Hi

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.

Many Thanks!

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.Coun t)
End If
Next wsh
End If
Next wbk
End Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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?

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.Coun t)
End If
Next wsh
End If
Next wbk
End Sub

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2