Macro copying data from one workbook containing several sheets to another

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,751
Office Version
  1. 2019
Platform
  1. Windows
I have a workbook with several sheets containing data that was imported.

I have written code to open the file containing the imported data and to copy the data from each of the sheets and paste this into the output workbook for eg if the source workbook has data in sheet1, sheet2, shhet3 etc then the data must be copied and paste into sheet1, shhet2, shhet3 etc in the source workbook i.e the workbook from where the macro is activated.

When running the macro an error occurred and the following code was highlighted


sht.Move after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

The code I wrote is as follows:

Sub copySheets()
Dim inputFile
Dim inputWB As Workbook

inputFile = Application.GetOpenFilename

If inputFile = "" Or inputFile = False Then Exit Sub

Set inputWB = Workbooks.Open(inputFile)
Application.ScreenUpdating = False
For Each sht In inputWB.Sheets
sht.Move after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Next
Application.ScreenUpdating = True
Set inputWB = Nothing
End Sub


It would be appreciated if you could assist


Thanks

Howard
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,889
What error are you getting? When I attempt to .Move the last worksheet from a workbook (and display alerts is set to false) I get error 1004, "Move method of worksheet class failed. If that is what you are getting, use the .Copy method instead, then clear the sheets in the source workbook after they are copied.
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,751
Office Version
  1. 2019
Platform
  1. Windows
Hi Phil

Thanks for the info
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
I think the problem is that you can't move ALL the sheets from inputWB because that would leave it with no sheets. You can't move (or delete) the last sheet from a workbook. You have to leave it with a least one sheet.
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,751
Office Version
  1. 2019
Platform
  1. Windows
Thanks for the reply. After much frustration, I managed to determine that one neded to have at least one sheet, so I inserted a blank sheet and amended the code to ignore the last sheet
 

Watch MrExcel Video

Forum statistics

Threads
1,127,938
Messages
5,627,722
Members
416,271
Latest member
Bolweavil

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
Top