Hi all,
I have a workbook with 5 different sheets that all contains Tables named tbl_1, tbl_2, tbl_3 etc. The sheet name that contains tbl_1 is named “1” and so on. All the tables start at A8 with column headings but can vary in nr of columns. I have one sheet named master, also a table.
What I want to do is to copy a specified range in each table (tbl_1 – tbl_5) and paste it to first blank row to the Master table column “A:D”.
I want to do the upload from each table 1 – 5 in different steps, one at a time. The range I want to upload to Master is different in every table. In my example the code check for duplicates in tbl_1 Column (“AA”) and then copy and paste unique values range “AA:AD” to Master table column “A:D”.
I have this code that is working when I do the upload from tbl_1. But when I copied this Macro and adjust the settings for tbl_2 it overwrites the uploaded data from tbl_1. I need the macro to find the next blank row in Master sheet.
Is anyone having an idea on how to adjust the code so it doesn’t overwrites the previous uploaded data? Can someone please help me?
Here is the code:
Sub Upload_tbl_1()
With Sheets("1")
.Range("AB9:AD9", .Cells(Rows.Count, "AA").End(xlUp)).Copy
End With
With Sheets("Master")
.Range("A8:A8").PasteSpecial xlPasteValues
.Range("A8", .Range("A" & Rows.Count).End(xlUp)).RemoveDuplicates 1, xlNo
End With
End Sub
Thanks in advance
I have a workbook with 5 different sheets that all contains Tables named tbl_1, tbl_2, tbl_3 etc. The sheet name that contains tbl_1 is named “1” and so on. All the tables start at A8 with column headings but can vary in nr of columns. I have one sheet named master, also a table.
What I want to do is to copy a specified range in each table (tbl_1 – tbl_5) and paste it to first blank row to the Master table column “A:D”.
I want to do the upload from each table 1 – 5 in different steps, one at a time. The range I want to upload to Master is different in every table. In my example the code check for duplicates in tbl_1 Column (“AA”) and then copy and paste unique values range “AA:AD” to Master table column “A:D”.
I have this code that is working when I do the upload from tbl_1. But when I copied this Macro and adjust the settings for tbl_2 it overwrites the uploaded data from tbl_1. I need the macro to find the next blank row in Master sheet.
Is anyone having an idea on how to adjust the code so it doesn’t overwrites the previous uploaded data? Can someone please help me?
Here is the code:
Sub Upload_tbl_1()
With Sheets("1")
.Range("AB9:AD9", .Cells(Rows.Count, "AA").End(xlUp)).Copy
End With
With Sheets("Master")
.Range("A8:A8").PasteSpecial xlPasteValues
.Range("A8", .Range("A" & Rows.Count).End(xlUp)).RemoveDuplicates 1, xlNo
End With
End Sub
Thanks in advance