VBA code to import worksheets from other workbooks

[Alice21]

Hi

Is there a VBA code that I can put into a Master workbook that wil
import sheets from four different workbooks?

I only want to import sheets if they contain "W93004" in the shee
name.

Many Thanks

 

[Claus Busch]

Hi Alice,

Am Mon, 22 Apr 2013 15:55:37 +0100 schrieb Alice21:

Is there a VBA code that I can put into a Master workbook that will
import sheets from four different workbooks?

I only want to import sheets if they contain "W93004" in the sheet
name.

open Master and the other 4 workbooks.
Code for workbook "Master":

Sub Import()
Dim wbk As Workbook
Dim wsh As Worksheet

ThisWorkbook.Activate
For Each wbk In Application.Workbooks
If wbk.Name <> "Master.xlsm" Then
For Each wsh In wbk.Worksheets
If InStr(wsh.Name, "W93004") > 0 Then
wsh.Copy _
after:=Workbooks("Master.xlsm").Sheets(Sheets.Count)
End If
Next wsh
End If
Next wbk
End Sub


Regards
Claus Busch

 

[Alice21]

Thanks. I have this code, however i cannot get it to work. I think i
may be because the sheet names are not W93004, they contain text afte
them too. Is there a 'wild card' i need to out in to find thes
sheets?

Sub Import()
Dim wbk As Workbook
Dim wsh As Worksheet

ThisWorkbook.Activate
For Each wbk In Application.Workbooks
If wbk.Name <> "W93004.xlsm" Then
For Each wsh In wbk.Worksheets
If InStr(wsh.Name, "W93004") > 0 Then
wsh.Copy _
after:=Workbooks("W93004.xlsm").Sheets(Sheets.Count)
End If
Next wsh
End If
Next wbk
End Su

 

[Claus Busch]

Hi Alice,

Am Tue, 23 Apr 2013 09:07:53 +0100 schrieb Alice21:

Thanks. I have this code, however i cannot get it to work. I think it
may be because the sheet names are not W93004, they contain text after
them too. Is there a 'wild card' i need to out in to find these
sheets?

instr searches for a substring in the sheet name. So you don't need a
wildcard.
Did you suit the workbook name and the sheet names correctly?
Is the code in a standard module of workbook "W93004.xlsm"?


Regards
Claus Busch

 

[Alice21]

I put the code in the Master workbook called W93004.
I have the other four workbooks open.
When i run the macro it gives me a run time error

 

[Claus Busch]

Hi Alice,

Am Tue, 23 Apr 2013 10:52:01 +0100 schrieb Alice21:

I put the code in the Master workbook called W93004.
I have the other four workbooks open.
When i run the macro it gives me a run time error 9

is the workbook W93004 saved as workbook with macros?
Or is it another format? xlsx or xlsb?
In Direct window write:
?Thisworkbook.name


Regards
Claus Busch

 

[Alice21]

It's a workbook with Macros. Is an excel 2003 file .xl

 

[Claus Busch]

Hi Alice,

Am Tue, 23 Apr 2013 14:02:16 +0100 schrieb Alice21:

It's a workbook with Macros. Is an excel 2003 file .xls

then you have to change the code:

Sub Import()
Dim wbk As Workbook
Dim wsh As Worksheet

ThisWorkbook.Activate
For Each wbk In Application.Workbooks
If wbk.Name <> "W93004.xls" Then
For Each wsh In wbk.Worksheets
If InStr(wsh.Name, "W93004") > 0 Then
wsh.Copy _
after:=Workbooks("W93004.xls").Sheets(Sheets.Count)
End If
Next wsh
End If
Next wbk
End Sub


Regards
Claus Busch

 

[Alice21]

Thank you ever so much! That works! I've been working on that fo
days

 

[Claus Busch]

Hi Alice,

Am Tue, 23 Apr 2013 16:01:49 +0100 schrieb Alice21:

Thank you ever so much! That works! I've been working on that for
days!

glad to help and thank you for the feedback.
For the future: Please write your Excel version in the question

Regards
Claus Busch