Macro to copy all sheets from source workb

[howard]

I have a source workbook with several sheets. I would like to copy each sheet with the data into the destination workbook and to create sheets if these do not exist


I am looking for code to do this


Your assistance is most appreciated

I have also posted on https://www.excelforum.com/excel-pr...4223-import-workbook-with-muliple-sheets.html

 

[Yongle]

I would like to copy each sheet with the data into the destination workbook and to create sheets if these do not exist

If the sheets already exist ... do they contain data ? .... is data to be overwritten OR added to ?

 

[howard]

The Sheets if they exits will contain data, which is to be overwritten

 

[Yongle]

Will the destination file contain any other sheets ?
If not, simply save a copy of source workbook like this

Sub SaveAsNewName()
    Dim newName As String, Source As Workbook
    ChDir "C:\Folder\SubFolder"    [COLOR=#006400][I]'this line is to specify where to find source file [/I][/COLOR]
    Set Source = Workbooks.Open(Application.GetOpenFilename)
    newName = Left(Source.Name, InStr(Source.Name, ".") - 1)
    newName = InputBox("amend name", "??", newName)
    Source.SaveAs Filename:=Source.Path & "\" & newName
    Source.Close False
End Sub

Error handling should be added to the above code

 

[howard]

The Destination workbook will not contain any other sheets

Many thanks for your help