VBA code to copy data in different sheets of a workbook

[Shweta]

Hi All,

I am using the following code to copy data from multiple workbooks in a folder to
one workbook.

Code:


Sub copy_files()

Dim wkb1, wkb2, wkb3 As Workbook
Dim i, lastrow As Integer
Dim mylastrow, mylastcol As Long
Dim myrange As Range
Dim mylastcell As Integer
Dim xrow As Long
Dim intsheets, z As Integer

Set wkb1 = ActiveWorkbook
lastrow = Range("a65356").End(xlUp).Row

Workbooks.Open Filename:=Range("E1").Value
Set wkb2 = ActiveWorkbook

For i = 1 To lastrow
Workbooks.Open Filename:=wkb1.Sheets(1).Range("A" & i).Value
Set wkb3 = ActiveWorkbook

Sheets(1).Select
Range("A1").Select
ActiveSheet.UsedRange.Copy
wkb2.Activate
Sheets(1).Select
xrow = Range("A65356").End(xlUp).Row + 1
Range("A" & xrow).Select
ActiveSheet.Paste
wkb3.Activate
wkb3.Close
wkb1.Activate

Next

End Sub


Please tell me what changes are required in the above code to paste the data in different sheets on one workbook.

Thanks & regards,
Shweta

 

[prajul89]

just add this code after Set wkb3 = ActiveWorkbook

sheets.Add

 

[prajul89]

just add this code after wkb2.Activate

sheets.Add