Copy sheets from one workbook to another workbook

[macro_learning]

Hi All,

Can anyone please help me with the below code,

it should merge all the sheets in all the workbook in any given folder to a workbook "copying1.xlsm". it is opening the file however not copying the sheets to the workbook "copying1.xlsm".

Sub copyingsheets()
Application.ScreenUpdating = False

mysourcepath = InputBox("path")

Set myobject = New Scripting.FileSystemObject
Set mysource = myobject.GetFolder(mysourcepath)
On Error Resume Next

For Each myfile In mysource.Files

  Workbooks.Open (myfile)

    For Each Sheet In Workbooks(myfile).Worksheets
      Total = Workbooks("copying1.xlsm").Worksheets.Count
      Workbooks(myfile).Worksheets(Sheet.Name).Copy _
      after:=Workbooks("copying1.xlsm").Worksheets(Total)
      MsgBox myfile.Name    ' msgbox is populating name of the workbook myfile correctly
      MsgBox Sheet.Name    ' msgbox is not populating name of the sheet
      MsgBox Total               'msgbox is populating the value of variable "Total" correctly
    Next Sheet
 
  Next myfile
Application.ScreenUpdating = True

End Sub

 

[Marc L]

Hi !

First remove codeline On Error Resume Next to see where logic fails …
​

 

[macro_learning]

its showing error "object does not support method/property" while executing the below highlighted code,


Sub merging_sheet()
Application.ScreenUpdating = False

mysourcepath = "D:\Users\name\Desktop\New folder"

Dim myobject As FileSystemObject
Set myobject = CreateObject("Scripting.FileSystemObject")
Set mysource = myobject.GetFolder(mysourcepath)

For Each myfile In mysource.files

Workbooks.Open (myfile)
For Each Sheet In myfile
Workbooks(myfile).Sheets(Sheet).Copy , After:=Workbooks("merging_sheet.xlsm").Sheets(1)
Next Sheet

Next myfile


End Sub

Hi !

First remove codeline On Error Resume Next to see where logic fails …
​

 

[Marc L]

It is really weird to see it is not the same code as initial post ‼

So check what is Sheet in this codeline :
Workbooks(myfile).Sheets(Sheet).Copy ? …
​

 

[macro_learning]

Sheet is the name of the current worksheet in workbook myfile , and the current object in a for each-next loop.

and i checked that it is reading the required sheet in a workbook properly by making it populate its name by msgbox sheet.name

 

[Marc L]

No ‼ As written in your code it is not a name but a variable !

See previous codeline and just check in Locals window :
so you may understand you just need Sheet to refer a worksheet
within the For Each loop !

If the name of worksheet is Sheet so in the code : Sheets("Sheet") …
​