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

[wberg82]

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.

 

[Fluff]

You cannot copy non-contiguous values like that, try

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

 

[wberg82]

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

 

[Fluff]

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

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

 

[wberg82]

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?

 

[Fluff]

How about

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