Jagwarman
asked on
Copy data from every tab in a workbook to a different workbook
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
Here's hoping!
Thanks
ASKER
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
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
ASKER
sorry it tells me Variable not defined here
Set DestWbk = Workbooks
Set DestWbk = Workbooks
Hi,
Have you changed YourFile
at line 2 insert
Dim DestWbk as Workbook
Regards
Have you changed YourFile
at line 2 insert
Dim DestWbk as Workbook
Regards
ASKER
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.
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.
Hi,
pls try
YourFile is the destination File
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
Regards
ASKER
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(Dest Wbk.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
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(Dest
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
Hi
the code should be in the source workbook
Regards
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
Regards
ASKER
unfortuantely I cannot get this to work
but thanks for trying
but thanks for trying
ASKER
I have tried this on my personal PC and it works so I will try again tomorrow.
Regards
Regards
ASKER
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(Dest Wbk.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
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(Dest
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
Hi,
In which File is the code?
Regards
In which File is the code?
Regards
ASKER
the file with the 40 Tabs
ASKER
I found this in a Google search and it also falls over
at WkSht.Copy After:=NewBook.Sheets(NewB ook.Sheets .Count)
Application.ScreenUpdating = False
Dim ThisBook As Workbook
Dim WkSht As Worksheet, NewBook As Workbook
Set ThisBook = ThisWorkbook
Set NewBook = Workbooks.Add(xlWBATWorksh eet)
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(NewB ook.Sheets .Count)
End Select
Application.CutCopyMode = False
Next WkSht
Application.DisplayAlerts = False
'Worksheets("Sheet1").Dele te
'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?
at WkSht.Copy After:=NewBook.Sheets(NewB
Application.ScreenUpdating
Dim ThisBook As Workbook
Dim WkSht As Worksheet, NewBook As Workbook
Set ThisBook = ThisWorkbook
Set NewBook = Workbooks.Add(xlWBATWorksh
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(NewB
End Select
Application.CutCopyMode = False
Next WkSht
Application.DisplayAlerts = False
'Worksheets("Sheet1").Dele
'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?
Hi,
Are you trying to copy a sheet which is with Visible = xlSheetVeryHidden?
Regards
Are you trying to copy a sheet which is with Visible = xlSheetVeryHidden?
Regards
ASKER
I do have hidden sheets but I don't want those copied
Regards
Regards
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Rgonzo1971
thank you for helping me with this, brilliant, it now does exactly what I need it to do.
Have a good Xmas
Regards
thank you for helping me with this, brilliant, it now does exactly what I need it to do.
Have a good Xmas
Regards
Pls try
Open in new window
Regards