VBA code to import worksheets from other workbooks
 

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!

VBA code to import worksheets from other workbooks
 

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

VBA code to import worksheets from other workbooks
 

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

VBA code to import worksheets from other workbooks
 

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

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

VBA code to import worksheets from other workbooks
 

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

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

VBA code to import worksheets from other workbooks
 

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 futu Please write your Excel version in the question

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