CODEWITHSUNDEEP
vbaexcel
William
William

Reputation: 115

Open workbooks and copy sheets

I need help with a macro that will open workbooks in the same folder and copy the Whole sheet in each workbook to a workbook where my macro is at.

There are 4 different workbooks and they all have a different number of sheets and I need to have all the sheet from each workbook in one.

I just started programming and get and error object required 424 on the line that says: Set wsSheet = wbRegularesBruto.Sheets("Movimentacao").Cells.Copy

Can someone please guide me to resolve this issue. Does anyone know of an easier way to get all these sheets copied into the TH Macro Workbook?

Option Explicit
Sub TrainingHoursMacro()

Dim wbTHMacro As Workbook, wsRegulares As Worksheet, wsRegularesDemitidos As Worksheet, wsTempActivos As Worksheet, wsTempJA As Worksheet, wsTempFit As Worksheet, _
wsTempDemitidos As Worksheet, wsPresenceSystem As Worksheet, wsResultados As Worksheet, wsDLList As Worksheet, wsSheet As Worksheet
Dim wbRegularesBruto As Workbook, wsMovimentacao As Worksheet, wsDemitidos As Worksheet
Dim wbTemporariosBruto As Workbook, wsTemporariosAtivos As Worksheet, wsJAAtivos As Worksheet, wsAprendizesFit As Worksheet
Dim wbPresenceSystem As Workbook, wsTPCC As Worksheet

Set wbTHMacro = Workbooks("Training Hours Macro.xlsm")
Set wsRegulares = wbTHMacro.Sheets("Regulares")
wsRegulares.Cells.ClearContents

Set wbRegularesBruto = Workbooks.Open(Filename:="H:\BX-HR\BX-INDUSTRIAL RELATIONS\HR REPRESENTATIVES\PRIVATE\HRSSC\Brazil\Training Hours Macro\Regulares Bruto.xls")
If Not wbRegularesBruto Is Nothing Then
  Set wsSheet = wbRegularesBruto.Sheets("Movimentacao").Cells.Copy

  wsSheet.Cells.Copy wsRegulares.Range("A1")
Else
  Exit Sub
End If


End Sub

Upvotes: 0

Views: 274

Answers (1)

Andy G
Andy G

Reputation: 19367

Set wsSheet = wbRegularesBruto.Sheets("Movimentacao").Cells.Copy

wsSheet is a Worksheet object. I assume you want to assign it to this sheet, so you would use:

Set wsSheet = wbRegularesBruto.Sheets("Movimentacao")

You could then use Cells.Copy after this. You can't do both in one statement.

Upvotes: 1

Related Questions