Macro copying data from one workbook containing several sheets to another

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,570
Office Version
  1. 2021
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,217,258
Messages
6,135,506
Members
449,945
Latest member
noone12344444444

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