Adding Copied Data into next row in Table
Results 1 to 5 of 5

Thread: Adding Copied Data into next row in Table
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Adding Copied Data into next row in Table

    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

  2. #2
    New Member
    Join Date
    Apr 2019
    Location
    Porto - Portugal
    Posts
    16
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding Copied Data into next row in Table

    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?

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding Copied Data into next row in Table

    Quote Originally Posted by tico_ocit View Post
    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 by ESPN8; Aug 16th, 2019 at 07:26 PM. Reason: typos

  4. #4
    New Member
    Join Date
    Apr 2019
    Location
    Porto - Portugal
    Posts
    16
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding Copied Data into next row in Table

    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".

  5. #5
    New Member
    Join Date
    Aug 2019
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding Copied Data into next row in Table

    Quote Originally Posted by tico_ocit View Post
    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •