I am trying to get data from multiple worksheets and paste into sheet 1 but my code isnt working properly. It keeps copying from the sheet "table 1" over and over again. Does anyone know what is wrong with my code?
Thanks
Option Explicit
Sub test()
Dim ws As Worksheet
Dim i As Integer
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Table 1" Then
ActiveSheet.Range("A15:X35").Select
Selection.Copy
Worksheets("Table 1").Activate
For i = 1 To 5000
If IsEmpty(Cells(i, 1)) = True And IsEmpty(Cells(i + 1, 1)) = True Then
ActiveSheet.Cells(i, 1).Select
ActiveSheet.Paste
Exit For
End If
Next i
End If
Next ws
End Sub
Inside your loop, you are copying from the ActiveSheet, not from ws. if the activeSheet is "table 1" (in fact, you activate table 1 yourself in the statement Worksheets("Table 1").Activate), then of course if will keep copying from it.
Besides, it is always better to copy directly from ranges without using Activate and Select. I guess this is what you want to do:
With Worksheets("Table 1")
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Table 1" Then
For i = 1 To 5000
If IsEmpty(.Cells(i, 1)) And IsEmpty(.Cells(i + 1, 1)) Then
ws.Range("A15:X35").Copy .Cells(i, 1)
Exit For
End If
Next i
End If
Next ws
End With
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments