Hi Everyone,
I created a macro to copy a rage of data from multiple sheets into a table on one sheet. It has worked fine for me, except that now I want to use a call macro to grab only selected sheets' data into the table. The issue I have is that originally we were going to always pull data from the first sheet. I set up the macro to paste into the first cell table after the headers. As you can see in my code below that the subsequent destinations paste into the next open row of the table. The problem is if the user doesn't want data from sheet 1 then the data is inserted in the first open row after my table.
I need help amending the Copy_Paste_Sheet2 3,4,etc. t wok jus like Copy_Paste Sheet1. Users may copy only one sheet of data or multiple sheets of data into the table.
Sub Clear_Data()
With Range("CSV_DATA").ListObject
If Not .DataBodyRange Is Nothing Then
.DataBodyRange.Delete
End If
End With
End Sub
Sub Copy_Paste_Sheet1()
Worksheets(1).Range("J10:P109").Copy
Worksheets("CSV DATA").Activate
Worksheets("CSV DATA").Range("A2").PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlPasteSpecialOperationNone, _
SkipBlanks:=False, _
Transpose:=False
End Sub
Sub Copy_Paste_Sheet2()
Worksheets(2).Range("J10:P109").Copy
Worksheets("CSV DATA").Activate
Worksheets("CSV DATA").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlPasteSpecialOperationNone, _
SkipBlanks:=False, _
Transpose:=False
End Sub
Sub Copy_Paste_Sheet3()
Worksheets(3).Range("J10:P109").Copy
Worksheets("CSV DATA").Activate
Worksheets("CSV DATA").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlPasteSpecialOperationNone, _
SkipBlanks:=False, _
Transpose:=False
End Sub
I created a macro to copy a rage of data from multiple sheets into a table on one sheet. It has worked fine for me, except that now I want to use a call macro to grab only selected sheets' data into the table. The issue I have is that originally we were going to always pull data from the first sheet. I set up the macro to paste into the first cell table after the headers. As you can see in my code below that the subsequent destinations paste into the next open row of the table. The problem is if the user doesn't want data from sheet 1 then the data is inserted in the first open row after my table.
I need help amending the Copy_Paste_Sheet2 3,4,etc. t wok jus like Copy_Paste Sheet1. Users may copy only one sheet of data or multiple sheets of data into the table.
Sub Clear_Data()
With Range("CSV_DATA").ListObject
If Not .DataBodyRange Is Nothing Then
.DataBodyRange.Delete
End If
End With
End Sub
Sub Copy_Paste_Sheet1()
Worksheets(1).Range("J10:P109").Copy
Worksheets("CSV DATA").Activate
Worksheets("CSV DATA").Range("A2").PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlPasteSpecialOperationNone, _
SkipBlanks:=False, _
Transpose:=False
End Sub
Sub Copy_Paste_Sheet2()
Worksheets(2).Range("J10:P109").Copy
Worksheets("CSV DATA").Activate
Worksheets("CSV DATA").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlPasteSpecialOperationNone, _
SkipBlanks:=False, _
Transpose:=False
End Sub
Sub Copy_Paste_Sheet3()
Worksheets(3).Range("J10:P109").Copy
Worksheets("CSV DATA").Activate
Worksheets("CSV DATA").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlPasteSpecialOperationNone, _
SkipBlanks:=False, _
Transpose:=False
End Sub