Adding Copied Data into next row in Table

ESPN8

New Member
Joined
Aug 13, 2019
Messages
19
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
I think the easiest solution is to allow the user to write the pages like:
A B
Initial : 2
Final : 3

So the sheets 2 and 3 would be the ones that was copied.
Then I would put every code in the same sub, using a :
i=B2
do while B1<=B2
Worksheets(i).Range("J10:P109").Copy
..
..
i=i+1
Loop

That the way I would do. Work for you?
 
Upvote 0
I think the easiest solution is to allow the user to write the pages like:
A B
Initial : 2
Final : 3

So the sheets 2 and 3 would be the ones that was copied.
Then I would put every code in the same sub, using a :
i=B2
do while B1<=B2
Worksheets(i).Range("J10:P109").Copy
..
..
i=i+1
Loop

That the way I would do. Work for you?

I think left some information out. The range is not the same for each sheet. That is why I have a separate sub for each sheet.
 
Last edited:
Upvote 0
How different?
The data that you want to copy isn't always on the columns J to P?
Because on the 3 subs that you presented, the range was always the same "J10:P109". :)
 
Upvote 0
How different?
The data that you want to copy isn't always on the columns J to P?
Because on the 3 subs that you presented, the range was always the same "J10:P109". :)

The Data is always in the same columns it just might go to P50 or P500.

Thank you,
Davd
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top