Solved

Copy data from every tab in a workbook to a different workbook

Posted on 2013-12-17
18
325 Views
Last Modified: 2013-12-18
Is there a quick way [using VBA] to copy the data from every tab I have in my existing workbook [except those named Sheet1, Sheet2, Sheet3 and Sheet4] to another workbook and include the Tab names. I have 40 tabs in the workbook.

Here's hoping!

Thanks
0
Comment
Question by:Jagwarman
  • 11
  • 7
18 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39723636
Hi,

Pls try

Sub Macro()

Set DestWbk = Workbooks("YourFile.xlsx")
For Each ws In ActiveWorkbook.Sheets
    If Not (ws.Name Like "Sheet[1-4]") Then
        ws.Copy After:=DestWbk.Sheets(DestWbk.Sheets.Count)
    End If
Next
End Sub

Open in new window

Regards
0
 

Author Comment

by:Jagwarman
ID: 39723669
Hi Rgonzo1971

it tells me Variable not defined.

Also, I was hoping for sheets 1-4 it would be written so that I can amend if for instance I want to include the sheet named [i.e. Dashboard or menu etc]

Will I be able to change "Sheet[1-4]" to include the above if I need to?

Thanks for your help with this

Kind regards
0
 

Author Comment

by:Jagwarman
ID: 39723672
sorry it tells me Variable not defined here

Set DestWbk = Workbooks
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39723689
Hi,

Have you changed YourFile

at line 2 insert

Dim DestWbk as Workbook

Regards
0
 

Author Comment

by:Jagwarman
ID: 39723699
I have added. [Is this correct]

Dim DestWbk As Workbook
Dim ws As Worksheet

But now I have another question: is ("YourFile.xlsx") the file with all the tabs on or the new workbook where they will be copied to.

I am not sure I can see how the code goes from existing workbook to new workbook.

Sorry to be a dumb head.
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39723703
Hi,

pls try

YourFile is the destination File

Sub Macro()
Dim DestWbk As Workbook
Set DestWbk = Workbooks("YourFile.xlsm")
For Each ws In ActiveWorkbook.Sheets
    If Not ((ws.Name Like "Sheet[1-4]") Or _
            (ws.Name = "Dashboard") Or _
            (ws.Name = "Menu")) Then
        ws.Copy After:=DestWbk.Sheets(DestWbk.Sheets.Count)
    End If
Next
End Sub

Open in new window

Regards
0
 

Author Comment

by:Jagwarman
ID: 39723755
really sorry but if ("YourFile.xlsx") is the destination file I presume I have to open it. So I did that.

The code is in my file with the 40 tabs so I guess it should then copy to "yourfile.xlsx"

but nothing is being copied. It appears to miss out the code

ws.Copy After:=DestWbk.Sheets(DestWbk.Sheets.Count)

it is going from

If Not ((ws.Name Like "Sheet[1-4]") Or _
            (ws.Name = "Dashboard") Or _
            (ws.Name = "Menu")) Then

to End IF

hope this makes sense.

Thanks
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39723774
Hi

the code should be in the source workbook
Sub Macro()
Dim DestWbk As Workbook
Set DestWbk = Workbooks("YourFile.xlsm")
For Each ws In ThisWorkbook.Sheets
    If Not ((ws.Name Like "Sheet[1-4]") Or _
            (ws.Name = "Dashboard") Or _
            (ws.Name = "Menu")) Then
        ws.Copy After:=DestWbk.Sheets(DestWbk.Sheets.Count)
    End If
Next
End Sub 

Open in new window


Regards
0
 

Author Comment

by:Jagwarman
ID: 39724116
unfortuantely I cannot get this to work

but thanks for trying
0
 

Author Comment

by:Jagwarman
ID: 39724938
I have tried this on my personal PC and it works so I will try again tomorrow.

Regards
0
 

Author Comment

by:Jagwarman
ID: 39726007
Rgonzo1971

Hope you are still here, and thank you for your patience.

As I mentioned previously, it worked fine on my personal PC but at work ........

it falls over at .... ws.Copy After:=DestWbk.Sheets(DestWbk.Sheets.Count)

with

method copy of object _worksheet failed

When I hover over the code I can see it is finding the Workbook and identifying how many tabs are in the Wbk.

Any Ideas?

Thanks
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39726043
Hi,

In which File is the code?

Regards
0
 

Author Comment

by:Jagwarman
ID: 39726046
the file with the 40 Tabs
0
 

Author Comment

by:Jagwarman
ID: 39726053
I found this in a Google search and it also falls over

at WkSht.Copy After:=NewBook.Sheets(NewBook.Sheets.Count)


Application.ScreenUpdating = False
    Dim ThisBook As Workbook
    Dim WkSht As Worksheet, NewBook As Workbook
    Set ThisBook = ThisWorkbook
    Set NewBook = Workbooks.Add(xlWBATWorksheet)
    For Each WkSht In ThisBook.Worksheets
        Select Case WkSht.Name
        Case "DataCapture", "INFORMATION", "A_ISPACEMONTH", "A_ISPACEYEAR"
             'these are the sheets names which shouldn't be copied
        Case Else
            WkSht.Copy After:=NewBook.Sheets(NewBook.Sheets.Count)
        End Select
        Application.CutCopyMode = False
    Next WkSht
    Application.DisplayAlerts = False
    'Worksheets("Sheet1").Delete
    'Worksheets("Sheet1 (2)").Name = "Sheet1"
    NewBook.SaveAs Filename:="TestItOut.xls"


But if I skip over that row it works fine. It creates a new workbook and saves it.

does this give you any clues?
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39726379
Hi,

Are you trying to copy a sheet which is with Visible = xlSheetVeryHidden?


Regards
0
 

Author Comment

by:Jagwarman
ID: 39726392
I do have hidden sheets but I don't want those copied

Regards
0
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39726406
So
let's try

Sub Macro1()
Application.ScreenUpdating = False
    Dim ThisBook As Workbook
    Dim WkSht As Worksheet, NewBook As Workbook
    Set ThisBook = ThisWorkbook
    Set NewBook = Workbooks.Add(xlWBATWorksheet)
    For Each WkSht In ThisBook.Sheets
        If WkSht.Visible <> xlSheetVeryHidden Then
            Select Case WkSht.Name
            Case "DataCapture", "INFORMATION", "A_ISPACEMONTH", "A_ISPACEYEAR"
                 'these are the sheets names which shouldn't be copied
            Case Else
                WkSht.Copy After:=NewBook.Sheets(NewBook.Sheets.Count)
            End Select
            Application.CutCopyMode = False
        End If
    Next WkSht
    Application.DisplayAlerts = False
    'Worksheets("Sheet1").Delete
    'Worksheets("Sheet1 (2)").Name = "Sheet1"
    NewBook.SaveAs Filename:="TestItOut.xls"

 End Sub

Open in new window

0
 

Author Comment

by:Jagwarman
ID: 39726593
Rgonzo1971

thank you for helping me with this, brilliant, it now does exactly what I need it to do.

Have a good Xmas

Regards
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question