My excel file, has multiple tabs. One of the Sheets is "Employee Access Arranged" and another is "IERWAA Lookup Arranged". Both these sheets have the same data ( Columns A to J ) but having different sources they are pulling from ( which is why they are in two separate sheets). Headers are in Row 1 and number of rows are not fixed.
I have a third tab "Master" in which I want the data from both these tabs to show up appended to each other. Row 1 will have headers, which are the same for all 3 sheets.
I wrote a Macro but it is just copying the header instead of the data.
Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name = "Employee Access Arranged" Or Sht.Name = "IERWAA Lookup
Arranged" Then
Sht.Select
LastRow = Range("A6553").End(xlUp).Row
Range("A2", Cells(LastRow, "J")).Copy
Sheets("Master").Select
Range("A6553").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Else
End If
Next Sh
Could someone please assist as to how I can make it work to copy the data from both the sheets instead of the header. Also, since the number of rows changes, i want it to be dynamic to pick up the last filled row automatically.
Please Note : This is not a duplicate of another question. I do not want just matching values from both sheets, but all the values from both sheets. Furthermore, the problem is unique as the last column "J" might not have values in most cases, but the whole column would still need to be copied
Thank you in advance.
LastRow
value from empty?"A" & Rows.Count
instead. All you're doing is getting theRange
object for one cell, and then invoking.End(xlUp)
on it. The number makes no difference, other than whether orLastRow
is correct or not given any number of rows.sht.Select
. Instead of using implicitActiveSheet
references (e.g.Range
,Cells
), qualify them with thatsht
variable and dosht.Range
,sht.Cells
. And instead ofRange(...).Select
followed byActiveSheet.Paste
, dosht.Range(...).Paste
. AvoidSelect
andActivate
, and qualify all worksheet member calls with an explicitWorksheet
object.