Copy from last row of everysheets and paste into another sheet

[kh40sth30ry]

Hello,

I'm new to excel vba, currently using Excel 2010. Recently, i need to basically Copy from last row of everysheets from workbook A and paste into sheet1 of workbook B. I tried to write the code for it as followed:

Sub CopyData1()
Workbooks.Open Filename:="C:\ExcelLearning\Simple.xlsx"
InactiveWorkbook = "Simple.xlsx"
CurrentWorkbook = "DataSet1.xlsm"
Dim RowCounter As Integer
For RowCounter = 1 To 3
    For i = 1 To 3
    Windows(CurrentWorkbook).Activate
    Sheets(i).Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range(Cells(FinalRow, 1), Cells(FinalRow, 10)).Copy
    Windows(InactiveWorkbook).Activate
    Cells(RowCounter, 1).Select
    ActiveSheet.Paste
    Windows(CurrentWorkbook).Activate
    Next i
   Next RowCounter
End Sub

The problem i have is that it keeps pasting into Row 1 of workbook B and doesn't move to the next row. I don't know how to fix my loop. Anyhelp would be really appreciated.

Thank you.

 

[VoG]

Perhaps instead of

Cells(RowCounter, 1).Select

Cells(Rows.Count, 1).End(xlUp).Offset(1).Select

 

[kh40sth30ry]

Hello,
Thank you for the help. However, the loop got executed 3 times, not just one? Like i have 3 sheets in workbook A, but 9 rows in workbook B...

 

[VoG]

Try removing the RowCounter loop.

 

[kh40sth30ry]

Oh wow, it works! Thank you! The only minor thing is that now the copied data got moved 1 row down. But i will think about it more. This is my first macro ever :D

 

[VoG]

This is far from ideal but maybe

Cells(Rows.Count, 1).End(xlUp).Offset(1).Select
If ActiveCell.Offset(-1).Value = "" Then ActiveCell.Offset(-1).Select

Basically all that Activating and Selecting is unnecessary but as it's your first macro I'm not going to get fussy about that

 

[VoG]

Maybe try like this

Sub CopyData1()
Dim InactiveWorkbook As String, CurrentWorkbook As String
Workbooks.Open Filename:="C:\ExcelLearning\Simple.xlsx"
InactiveWorkbook = "Simple.xlsx"
CurrentWorkbook = "DataSet1.xlsm"
For i = 1 To 3
    Workbooks(CurrentWorkbook).Sheets(i).Cells(Rows.Count, 1).End(xlUp).Resize(, 10).Copy Destination:=Workbooks(InactiveWorkbook).Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1)
Next i
End Sub

 

[kh40sth30ry]

Wow,

Thank you so much again. Your code was something that was what i initially wanted to do, but after many "Object doesn't support this property or method." error, i decided to stick with what i know so at least the code works :D.