Move Sheets to a New WorkBook Based on Tab Name

[dsgsandman]

I've got a long list of worksheets that I need to copy into a new workbook. I only want to copy these specific tabs to a new workbook. For each row of data, the corresponding sheet name that needs to be moved, can be created by combining the 15th column, a space and then the 1st column. Basically the tab name would be Indirect(O3&" "&A3), all the way down like 500 rows. I've taken a crack at writing the code myself. but, I get object variable not set. and it points to the TabName row when you go to debug.

Any idea what's the problem. I'm fairly new to this.

Any help would be appreciated.


Sub ZZZZTysonMoveSpecificDivisonTabs()
Dim TabName As Worksheet, ss As Worksheet, TysonCopy As Workbook
LastRow = Range("A65536").End(xlUp).Row
For Row = 3 To LastRow Step 1
TabName = Cells(Row, 15).Value & " " & Cells(Row, 1).Value

If TysonCopy Is Nothing Then
Sheets(TabName).Copy
Set TysonCopy = ActiveWorkbook
Else: Sheets(TabName).Copy after:=ss
End If
Next Row
End Sub

 

[cstimart]

you'll want to Set the Worksheet....

Set TabName = Sheets(1)
TabName.Name = Cells(Row, 15) & " " & Cells(Row, 1)

 

[dsgsandman]

That didn't work. It gave me an error because that tab name already exists. I already have all the sheets in my workbook named on individual sheets. I just need to move them. I think your code is trying to set the first sheet's name in my work book to the name of the sheet that I'm trying to copying into a new workbook...

 

[Norie]

Do you mean you have this list on a worksheet?

If you do why not start by using a formula to create the sheet names?

Put this is row 3 of an empty column and copy down:

=A3 & " " & O3

That isn't essential but it will make things slightly easier.

Anyway, I think one problem with the code is that you don't set a value for ss.

Try this code, at uses ss to reference the last worksheet in the new workbook.

That means that all the worksheets being copied go at the 'end' of the workbook.

Sub ZZZZTysonMoveSpecificDivisonTabs()
Dim TabName As Worksheet
Dim ss As Worksheet
Dim TysonCopy As Workbook
Dim I As Long
Dim wsList As Worksheet

    ' change 'ListSheet' to the name of the worksheet with the list
    ' for the worksheets to be copied
    Set wsList = ThisWorkbook.Worksheets("ListSheet")
 
    LastRow = wsList.Range("A" & Rows.Count).End(xlUp).Row
 
    For I = 3 To LastRow Step 1
 
        TabName = wsList.Cells(Row, 15).Value & " " & wsList.Cells(Row, 1).Value
 
        If TysonCopy Is Nothing Then
        
            ThisWorkbook.Sheets(TabName).Copy
 
            Set TysonCopy = ActiveWorkbook
 
           
        Else
 
            Set ss = TysonCopy.Worksheets(TysonCopy.Worksheets.Copy)
 
            ThisWorkbook.Sheets(TabName).Copy After:=ss
 
        End If
 
    Next I
    
End Sub

 

[VoG]

It would be more like

Set TabName = Cells(Row, 15).Value & " " & Cells(Row, 1).Value
If TysonCopy Is Nothing Then
    TabName.Copy

 

[dsgsandman]

Sorry, still haven't gotten it to work.


Norie,

I tried yours but got an error on the code:
Set ss = TysonCopy.Worksheets(TysonCopy.Worksheets.Copy)

Do I need to remove the .copy part?

VoG, I got the same error when I changed it to:
Set TabName = Cells(Row, 15).Value & " " & Cells(Row, 1).Value

 

[VoG]

I don't understand what Tyson is doing.

This worked for me

Sub CopySheets()
Dim LR As Long, i As Long
Dim X()
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 3 To LR
    ReDim Preserve X(i - 3)
    X(i - 3) = Range("O" & i).Value & " " & Range("A" & i).Value
Next i
Sheets(X).Copy
End Sub

 

[Norie]

It shoud be Count not Copy.

Think Intellisense got the better of me on that one.