Copying multiple workbooks into named worksheets in active workbook

[JimLes]

Hello, I am trying to copy worksheets, all named "Sheet 1" from multiple workbooks into my current workbook onto a named worksheet for each. All the files I would like to import from are under one folder R:\HC Data.


I found this code at this site:

http://www.dbforums.com/microsoft-e...eet1-multiple-workbook-into-one-workbook.html

This works great but it copies each sheet from workbooks under a folder into the the current workbook to Sheet 1, Sheet 2, etc... Instead of sheet 1, I would like it to copy to "HC Data", "Termination Data", etc...

Not sure if I could use the loop or if I had to call each separately in the code.

Sub ImportSheets()
Dim Path As String
Dim filename As String
Dim sht As Worksheet
Dim wkB As Workbook
Dim i As Integer

Path = "C:\LOGCALL"
filename = Dir(Path & "\*.xls")
Application.ScreenUpdating = False
i = 1
Do While filename <> ""
'CHECK SHEET NAME TO BE CREATED DOES NOT EXIST
Do While SheetExists("Sheet" & i)
i = i + 1
If i = 20 Then
MsgBox "Too many sheets !", vbExclamation, "ERROR !!"
Application.ScreenUpdating = True
Exit Sub
End If
Loop

Set sht = ActiveSheet
Workbooks.Open filename:=Path & "\" & filename
If SheetExists("Sheet1") Then
Set wkB = ActiveWorkbook
Sheets("Sheet1").Copy After:=sht
ActiveSheet.Name = "Sheet" & i
i = i + 1
wkB.Close savechanges:=False
Else
MsgBox "Sheets1 does not exist in file '" & filename & "'", vbExclamation, "ERROR !!"
ActiveWorkbook.Close savechanges:=False
End If

filename = Dir
Loop
Application.ScreenUpdating = True
End Sub

Function SheetExists(ByVal sName As String)
Dim sht As Worksheet

SheetExists = True
For Each sht In ActiveWorkbook.Sheets
If sht.Name = sName Then Exit Function
Next sht

SheetExists = False
End Function

Any help is greatly appreciated!!

 

[SkipVought]

Hi,

I would like it to copy to "HC Data", "Termination Data", etc...

based on WHAT criteria?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[JimLes]

Hey Skip,

Thanks for replying. I would like to copy the entire Sheet 1 in each work book to a target location.

Example: Sheet 1 in Workbook 1 to "Active HC Data" (sheet name)in HC Template Workbook

Does this help explain what I am trying to do?

Thanks,

 

[SkipVought]

No, it does not explain how you expect to name MULTIPLE sheets from MULTIPLE workbooks, each with a specific name. What is your CRITERIA?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[JimLes]

Here are the workbooks in the R:\HC Data folder:
1) Active HC - US Data.xls
2) Active HC - UK Data.xls
3) Active HC - France Data.xls
4) Active HC - Japan Data.xls
5) Termination Data - US Data.xls
6) Termination Data - UK Data.xls
7) Termination Data - France Data.xls
8) Termination Data - Japan Data.xls
9) Transfer In Division - US.xls
10) Transfer In Division - UK.xls
11) Transfer In Division - France.xls
12) Transfer In Division - Japan.xls
13) Transfer Out Division - US.xls
14) Transfer Out Division - UK.xls
15) Transfer Out Division - France.xls
16) Transfer Out Division - Japan.xls
17) Transfer In Job Code - US.xls
18) Transfer In Job Code - UK.xls
19) Transfer In Job Code - France.xls
20) Transfer In Job Code - Japan.xls
21) Transfer Out Job Code - US.xls
22) Transfer Out Job Code - UK.xls
23) Transfer Out Job Code - France.xls
24) Transfer Out Job Code - Japan.xls
25) Recruiting Open Report.xls
26) Recruiting Filled Report.xls
27) GIS Active HC Data.xls
28) GIS Termination HC Data.xls

Now, I would like to copy the entire contents (data) of "Sheet1" from each of the workbooks above to a master workbook named Headcount Rollup Template. Each sheet should be imported to a particular tab in the master:

Example: all data in "Sheet1" in Active HC - US Data.xls should be copied to tab "US HC" in master workbook

Here is the mapping corresponding to the number above:
1) US HC
1) UK HC
3) FRA HC
4) JPN HC
5) US Terms
6) UK Terms
7) FRA Terms
8) JPN Terms
9) US Tra In Div
10) UK Tra In Div
11) FRN Tra In Div
12) JPN Tra In Div
13) US Tra Out Div
14) UK Tra Out Div
15) FRN Tra Out Div
16) JPN Tra Out Div
17) US Tra In JC
18) UK Tra In JC
19) FRN Tra In JC
20) JPN Tra In JC
21) US Tra Out JC
22) UK Tra Out JC
23) FRN Tra Out JC
24) JPN Tra Out JC
25) Rec Open.xls
26) Rec Filled.xls
27) GIS HC.xls
28) GIS Terms.xls

Please let me know if this is enough information.

Thanks,

 

[SkipVought]

Put this data into a table, where you can reference the workbook and the corresponding sheet name.

This forum is not for VBA Code. Please repost your question in Forum707 for further help. When you post, explain that you have this table that cross references the workbook and new sheet name.




Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[JimLes]

Thanks Skip,

Thread has been moved to:

http://www.tek-tips.com/viewthread.cfm?qid=1546580&page=1