Macro copying data from one workbook containing several sheets to another

[howard]

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

 

[pbornemeier]

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]

Hi Phil

Thanks for the info

 

[AlphaFrog]

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]

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