Results 1 to 10 of 10

Thread: Modify macro copy and paste data from multiple rows
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2010
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Modify macro copy and paste data from multiple rows

    Hi Guys,

    I currently have a macro that copies a single row of raw data from the clipboard into PasteSpecial sheet, Email Extract sheet then reorders/formats the data which then copies the reordered data into the DataChecker sheet.

    the number of rows in the raw data has increased exponentially, is there any way the current macro could be modified so multiple rows can be done at once. please see the code below:

    Sub DATM()
    '
    ' DATM Macro
    '
    ' Keyboard Shortcut: Ctrl+d
    '
    Range("A2").Select
    ActiveSheet.PasteSpecial
    Sheets("Email Extract").Select
    Range("Table1").Select
    Selection.Copy
    Sheets("Data Checker").Select
    Cells(Range("A1000000").End(xlUp).Row + 1, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Sheets("Paste MailTable").Select
    Range("A4").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Record updated"
    End Sub

    Many thanks in advance.

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    250
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Modify macro copy and paste data from multiple rows

    Try yo change to
    Code:
    Range("table1").CurrentRegion.Select

  3. #3
    New Member
    Join Date
    Nov 2010
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Modify macro copy and paste data from multiple rows

    Quote Originally Posted by mohadin View Post
    Try yo change to
    Code:
    Range("table1").CurrentRegion.Select
    Hi Mate,

    That didn't work, all it did was copy the header and the first row only.

  4. #4
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    250
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Modify macro copy and paste data from multiple rows

    Check this
    you can hit F8
    Code:
    Sub Test()Range("table1").CurrentRegion.Select
    Selection.Copy
    Sheets("sheet2").Activate
    Range("a1").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False
    End Sub

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,019
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Modify macro copy and paste data from multiple rows

    Please supply the cross post link.
    Thanks
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    New Member
    Join Date
    Nov 2010
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Modify macro copy and paste data from multiple rows

    Quote Originally Posted by Fluff View Post
    Please supply the cross post link.
    Thanks
    https://www.excelforum.com/excel-pro...rder-data.html

    https://www.ozgrid.com/forum/forum/h...-re-order-data
    Last edited by aamarb; Aug 28th, 2019 at 03:24 PM.

  7. #7
    New Member
    Join Date
    Nov 2010
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Modify macro copy and paste data from multiple rows

    Quote Originally Posted by mohadin View Post
    Check this
    you can hit F8
    Code:
    Sub Test()Range("table1").CurrentRegion.Select
    Selection.Copy
    Sheets("sheet2").Activate
    Range("a1").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False
    End Sub
    Thanks for that but that does not work for what i need. I have attached a sample spreadsheet to clarify the issue

  8. #8
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    250
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Modify macro copy and paste data from multiple rows

    HI
    have you named named rang as table1?
    Other wise it is working here

  9. #9
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    250
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Modify macro copy and paste data from multiple rows

    Sub Test()
    Code:
    Range("table1").CurrentRegion.Select
    Selection.Copy
    Sheets("sheet2").Activate
    Range("a1").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False
    End Sub
    make sure the name table1 or what ever
    Last edited by mohadin; Aug 29th, 2019 at 07:32 AM.

  10. #10
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    250
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Modify macro copy and paste data from multiple rows

    Code:
    Sub Test()
    Range("table1").CurrentRegion.Select
    Selection.Copy
    Sheets("sheet2").Activate
    Range("a1").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False
    End Sub
    make sure the name table1 or what ever spelling

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
  •