Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
|
|||
|
|||
It's a workbook with Macros. Is an excel 2003 file .xls
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
|
|||
|
|||
Thank you ever so much! That works! I've been working on that for days!
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to split Worksheets into seperat workbooks | Excel Programming | |||
HELP = VBA Code To Import Worksheets Into A WorkBook | Excel Programming | |||
Simple Folder, Workbooks, Worksheets Import ? | Excel Programming | |||
how can i import worksheets from multiple workbooks | New Users to Excel | |||
Change code to make worksheets instead of workbooks | Excel Programming |