Copying specific sheets from multiple workbooks into 1 "master" workbook.

I'm trying to work out how to copy some specific sheets from multiple files into 1 master workbook.

I have stumbled across this code https://excel.tips.net/T007425_Combi...Workbooks.html

Please Login or Register  to view this content.

But my knowledge of VBA is rudimentary at best

The problem I have is 2 fold:

1. The worksheets are named slightly differently in each file.
   
   Each book contains 4 sheets, the sheets themselves are almost identical in name except that the sheet position forms part of the name, and the position is random. i.e. in 1 book I could have A_1, B_2, and C_3 but in another it could be A_1, C_2, B_3
   
   Assuming I wanted to move every tab named A_ I am presuming I could simply use a wildcard like * to fill in the variable bit.
   
   I also have a couple of files working to a slightly different naming sttructure, i.e. a instead of A
   
   So I need to reference both filename partss in the code
   
2. I want to rename the tabs as I copy them
   
   As I said above, the sheets all have the same name (near as damn it) - I don't want to end up with a workbook ful of sheets A, A, A, A, a, A etc. However, the filenames of the workbooks these sheets are in are suitable for the new tab names.

So basically I would like it to search a specific folder (this can be hard coded or requested via an entry box - I prefer hard coding.
The folder will only contain target data, so searching for *.xls should be ok.
I then want to import *A* or *a* (depending on the file type) from these old files to the new files, changing the tab name from "A" or "a" to Fn1

You could modify the code above as shown below. This can adjust the string names to match exactly your situation. If changing the strings in the Left functions to get worksheet names don't forget to change the number of characters you are checking to get the length of the string.

Sub CombineSheets()
Dim sPath As String
Dim sFname As String
Dim wBk As Workbook
Dim wSht, wShtDest As Worksheet

Application.EnableEvents = False
Application.ScreenUpdating = False
sPath = "Input your directory path, leave off the final \"
ChDir sPath
sFname = Dir(sPath & "\" & "*.xl*", vbNormal)

Do Until sFname = ""
Set wBk = Workbooks.Open(sFname)
For Each wSht In wBk.Worksheets
If Left(wSht.Name, 1) = "A" Or Left(wSht.Name, 1) = "a" Then
wSht.Name = wBk.Name
wSht.Copy Before:=ThisWorkbook.Sheets(1)
End If
Next wSht
wBk.Close False
sFname = Dir()
Loop

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Great, I'll test that when I get home.

Thank you.

It's working - YAY!

So as I mentioned above, the tab names come in 2 formats. The # represents the number of the tab in the workbook, which is appended to the tab name, and varies dependant upon tab position. I have been a bit more explicit with the tab names this time (while still obscuring the ensitive data) - they are case sensitive, and follow the formats below.

I have marked the tabs I'm interested in with ***

1 format the tabs are

Please Login or Register  to view this content.

The other format the tabs are

Please Login or Register  to view this content.

So from the code suggested above (placed inside CODE tags to make more readable, and indented (personal preference)):

Please Login or Register  to view this content.

I changed

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

as wShtDest didn't appear anywhere else.

I changed

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

I changed

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

so get the relevant sheets that I want.

I then also changed

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

which strips the unecessary part of the filename, and leaves me with the tabs named how I want them :D

So my final code looks like this - for anyone else who is looking for something similar

Please Login or Register  to view this content.