Results 1 to 9 of 9

Thread: Maro to copy data and paste based on cell value
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2009
    Posts
    99
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Maro to copy data and paste based on cell value

    Hi Team ,

    I have 3 sheets Process, Master & Data
    Process sheet Cell “C1” has row number Example:”A2”

    My macro should copy data from Master sheet “A4:L200” and paste it in “Data”sheet based on cell value in Process sheet Cell “C1(in current case its Cell A2)

    My current code doesn't work-


    Worksheets("Master").Range("A4:L200").Copy
    Worksheets("Data").Range(WB.Sheets("Process").Range("C1").Value).PasteSpecial Paste:=xlPasteValues

    Thanks
    chan

  2. #2
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,057
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Maro to copy data and paste based on cell value

    Try:

    Code:
    Worksheets("Data").Range(WB.Sheets("Process").Range(Range("C1").Value)).PasteSpecial paste:=xlPasteValues

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

    Default Re: Maro to copy data and paste based on cell value

    What is WB?
    Also what does "doesn't work" mean?, do you get any errors, does nothing happen, does it paste the data to the wrong?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    Board Regular
    Join Date
    Jul 2009
    Posts
    99
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Maro to copy data and paste based on cell value

    it says runtime error 424

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

    Default Re: Maro to copy data and paste based on cell value

    Can you please answer my other question as well, namely "What is WB"? Have you declared it anywhere?, have you given it a value?

    Assuming all sheets are in the same workbook then just remove it.
    Last edited by Fluff; Sep 16th, 2019 at 09:14 AM.
    - 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
    Board Regular
    Join Date
    Jul 2009
    Posts
    99
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Maro to copy data and paste based on cell value

    Hi Below is the entire code which i am using were i have declared WB as woeksheet

    Sub CopyRND()
    '
    ' Chan
    '
    Dim wS As Workbook
    Dim wb As Worksheet
    Set wb = wb.Sheets("Process")
    '>>>> Copy to Data Sheets
    Worksheets("Master").Range("A4:L200").Copy
    Worksheets("Data").Range(wb.Sheets("Process").Range(Range("C1").Value)).PasteSpecial Paste:=xlPasteValues
    End Sub

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

    Default Re: Maro to copy data and paste based on cell value

    As you have declared WB as a worksheet, the code should be
    Code:
    Worksheets("Data").Range(wb.Range("C1").Value).PasteSpecial Paste:=xlPasteValues
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    Board Regular
    Join Date
    Jul 2009
    Posts
    99
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Maro to copy data and paste based on cell value

    Thanks for your help

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

    Default Re: Maro to copy data and paste based on cell value

    Glad we could help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •