Move Sheets to a New WorkBook Based on Tab Name

D

dsgsandman

New Member
Joined
Jan 29, 2009
Messages
47
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
you'll want to Set the Worksheet....

Code: 
Set TabName = Sheets(1)
TabName.Name = Cells(Row, 15) & " " & Cells(Row, 1)
 
Upvote 0
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...
 
Upvote 0
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.

Code: 
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
 
Upvote 0
It would be more like

Code: 
Set TabName = Cells(Row, 15).Value & " " & Cells(Row, 1).Value
If TysonCopy Is Nothing Then
    TabName.Copy
 
Upvote 0
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
 
Upvote 0
I don't understand what Tyson is doing.

This worked for me

Code: 
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
 
Upvote 0
It shoud be Count not Copy.

Think Intellisense got the better of me on that one.:oops:
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Export Multiple Worksheets from a Workbook based on criteria
Replies
1
Views
88
Trebor76
T
A
  • Question Question
Make a new worksheet at every page break in a worksheet & name each sheet after a value from a cell.
Replies
6
Views
509
Arcomvi
A
H
  • Question Question
Copy Data where Source Workbook contains Name that is in destination sheet
Replies
4
Views
152
howard
H
B
  • Solved
Move ws to new wb with values and formatting
Replies
1
Views
64
mumps
mumps
A
  • Question Question
Open, Copy data and Close a workbook based on the names of the tabs in my active workbook
Replies
2
Views
100
Aurore87
A

Forum statistics

Threads
1,224,579
Messages
6,179,656
Members
452,934
Latest member
mm1t1

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top