Results 1 to 6 of 6

Thread: VBA macro for copying cells from one sheet and pasting to another
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2016
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA macro for copying cells from one sheet and pasting to another

    Sub Tapcon_134()
    '
    ' Add_Fastener Macro
    '
    Sheets("Hardware Load List").Range("C" & Rows.Count).End(xlUp).Offset(1).Resize(, 6).Value = Range("B9,D9,E9:F9,G9:H9").Value
    End Sub

    The above macro is only taking the value is B9 and copying it across all cells in "Hardware Load list". I want to copy the entire selected Range and paste whatever is in those cells to the "Hardware Load List" sheet. The Range that is to be copied in sheet called "Fasteners". I want the range to be pasted to the next available row in the "Hardware Load List" which I believe this macro is already setup to do.

    Need help copying all data in selected Range.

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

    Default Re: VBA macro for copying cells from one sheet and pasting to another

    You cannot copy non-contiguous values like that, try
    Code:
    With Sheets("Hardware Load List").Range("C" & Rows.Count).End(xlUp).Offset(1)
       .Offset(, 1).Resize(, 5).Value = Range("D9:H9").Value
       .Value = Range("B9").Value
    End With
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Feb 2016
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA macro for copying cells from one sheet and pasting to another

    Worked! What is the significance of writing the macro on different lines?

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

    Default Re: VBA macro for copying cells from one sheet and pasting to another

    You cannot copy values of non-contiguous cells, in one go, so you need to do it for each range.
    The With statement is just a short cut to save doing
    Code:
    Sheets("Hardware Load List").Range("C" & Rows.Count).End(xlUp).Offset(1, 1).Resize(, 5).Value = Range("D9:H9").Value
    Sheets("Hardware Load List").Range("C" & Rows.Count).End(xlUp).Offset(1).Value = Range("B9").Value
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Feb 2016
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA macro for copying cells from one sheet and pasting to another

    I would like to add a 3rd set of Values. This range is taken from J11. However, When adding this Range to the above formula, the result on sheet "Hardware Load List" is #N/A. How do I get this to return the value in Range J11 and not #N/A?

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

    Default Re: VBA macro for copying cells from one sheet and pasting to another

    How about
    Code:
    With Sheets("Hardware Load List").Range("C" & Rows.Count).End(xlUp).Offset(1)
       .Offset(, 1).Resize(, 5).Value = Range("D9:H9").Value
       .Offset(, 6).Value = Range("J11").Value
       .Value = Range("B9").Value
    End With
    - 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
  •