MrExcel Publishing
Your One Stop for Excel Tips & Solutions

move each sheet to an open workbook based on name


Posted by Shanna on December 06, 2001 7:48 PM

I want to write a macro that will do the following:

If the sheet name contains "100" move it to the open workbook name that contains "100" and then rename the sheet as QTD.

The sheet should go behind any others already in the open workbook.

Any way do to this?


Posted by Colo on December 06, 2001 9:48 PM

Hi Shanna
:If the sheet name contains "100" move it to the open workbook
I use copy method because if the Sheet name contains "100" is the last one of thisbook then error message will be shown.
If you want Sheet to "move" then
Please replace the word "copy" "move".

Sub Sample()
Const strWrd As String = "100"
Dim WB As Workbook, WS As Worksheet
For Each WS In Worksheets
If InStr(WS.Name, strWrd) > 0 Then
For Each WB In Workbooks
If InStr(WB.Name, strWrd) > 0 Then
WS.Copy After:=WB.Sheets(WB.Sheets.Count)
ActiveSheet.Name = "QTD"
End If
Next
End If
Next
End Sub

Posted by Shanna on December 08, 2001 12:08 PM

Hi Colo

I was wondering how to change this so that the sheet name is not hardcoded. Say I want excel to look at the sheet name and then copy it to the open workbook that contains that string.

I have numerous sheets, all with different 3-digit code's as their names, so saying it will be "100" doesn't help. I tried writing it over and over again for all the different numbers, but am getting tripped up on how many End If's and Next's I need.

Perhaps there's any easier way???

Thanks :-)
Shanna

Posted by Colo on December 16, 2001 5:03 PM

Sub Sample2()
Dim strShName As String, WB As Workbook, WS As Worksheet
For Each WS In Worksheets
If IsNumeric(WS.Name) And Len(WS.Name) = 3 Then
For Each WB In Workbooks
If InStr(WB.Name, WS.Name) > 0 Then
WS.Copy After:=WB.Sheets(WB.Sheets.Count)
ActiveSheet.Name = "QTD"
End If
Next
End If
Next
End Sub