PDA

View Full Version : Tabs to different work books.



mwarner
09-12-2011, 09:42 AM
Hello,

I am looking for an efficient way to solve a tedious exercise.

Once a week I am given a master workbook with 40 tabs and then I have to send each tab to a different 40 workbooks, so 1 tab from the master workbook per 1 secondary workbook. I hope there is a way to quickly automate this process and save me some time.

Thank you for help.

Bob Phillips
09-12-2011, 10:12 AM
Of course there is, and you know there is, otherwise you wouldn't be here :)

Are the 40 workbooks already open? If not, is there some way that code can identify the books to open? How do we know which tab goes to which workbook?

mwarner
09-12-2011, 10:26 AM
Haha, you got me.

I have to open the 40 workbooks in order to manually copy them as i do now. My ideal process would not involve opening all 40 in order for this to work, but that is a relatively smart portion of the time this takes.

I am not sure if there is a way that code can identify these books. I hoped we could use the name of the workbook, W1 - W40 for example, and send Sheet 1-40 to the respective books.
I might be able to rename the 40 workbooks the same as each tab if that would make things easier.


Of course there is, and you know there is, otherwise you wouldn't be here :)

Are the 40 workbooks already open? If not, is there some way that code can identify the books to open? How do we know which tab goes to which workbook?

Bob Phillips
09-12-2011, 10:52 AM
Thi asks for the new data workbook, and then assumes that all its sheets are name Sheet1, Sheet2, ..., the workbooks are all in the same directory and are name WB_1, WB_2, ...



Public Sub Test()
Dim newData As Workbook
Dim target As Workbook
Dim sh As Worksheet
Dim wbPath As String

With Application.FileDialog(msoFileDialogFilePicker)

.AllowMultiSelect = False
If .Show = -1 Then

Set newData = Workbooks.Open(.SelectedItems(1))
wbPath = newData.Path

For Each sh In newData.Worksheets

Set target = Workbooks.Open(wbPath & Application.PathSeparator & "WB_" & Mid$(sh.Name, 6, Len(sh.Name) - 5))
sh.Copy After:=target.Worksheets(target.Worksheets.Count)
target.ActiveSheet.Name = Format(Date, "yyyymmdd")
target.Save
target.Close
Next sh

newData.Close SaveChanges:=False
End If
End With
End Sub

mwarner
09-12-2011, 11:17 AM
Thank you so much for helping me with this.
If I could please poke your brain one more time..

The directory of WB_1's works great for my purposes, however I need to leave the sheets named as they are given to me.
The sheets are all different unique well identifiers (ie. 3-5-10-16w4). The Master workbook is constantly increasing in size, I am wondering if your code would be able to account for the different sheet names and introducing a new sheet once in a while.

Unfortunately i am not well versed in VBA and will find it nearly impossible to modify your code myself:(

Thank you again for all your effort.




Thi asks for the new data workbook, and then assumes that all its sheets are name Sheet1, Sheet2, ..., the workbooks are all in the same directory and are name WB_1, WB_2, ...



Public Sub Test()
Dim newData As Workbook
Dim target As Workbook
Dim sh As Worksheet
Dim wbPath As String

With Application.FileDialog(msoFileDialogFilePicker)

.AllowMultiSelect = False
If .Show = -1 Then

Set newData = Workbooks.Open(.SelectedItems(1))
wbPath = newData.Path

For Each sh In newData.Worksheets

Set target = Workbooks.Open(wbPath & Application.PathSeparator & "WB_" & Mid$(sh.Name, 6, Len(sh.Name) - 5))
sh.Copy After:=target.Worksheets(target.Worksheets.Count)
target.ActiveSheet.Name = Format(Date, "yyyymmdd")
target.Save
target.Close
Next sh

newData.Close SaveChanges:=False
End If
End With
End Sub

Bob Phillips
09-12-2011, 11:20 AM
T
The directory of WB_1's works great for my purposes, however I need to leave the sheets named as they are given to me.
The sheets are all different unique well identifiers (ie. 3-5-10-16w4).

Just remove the line


target.ActiveSheet.Name = Format(Date, "yyyymmdd")


The Master workbook is constantly increasing in size, I am wondering if your code would be able to account for the different sheet names and introducing a new sheet once in a while.

Not sure what you mean by that.

mwarner
09-12-2011, 12:08 PM
Ok, i removed that line. It is not running as expected.

In the same directory i have: 1 workbook - the master spreadsheet "Test.xlsm" and 6 workbooks WB_1 through WB_6.

When i run the Test macro, i am prompted to open a file, i choose the master spreadsheet "Test.xlsm".
This ends with an error showing the directory i am in and saying WB_'s cannot be found, and "check your spelling or try a different path".
Finally, after hitting OK i am taken to the macro editor and i get a Run-time error '1004':
Method 'Open' of object 'Workbooks' failed.

The 6 WB_# are not macro enabled workbooks.


Just remove the line


target.ActiveSheet.Name = Format(Date, "yyyymmdd")



Not sure what you mean by that.