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

wberg82

New Member
Joined
Feb 19, 2016
Messages
38
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,573
Office Version
365
Platform
Windows
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
 

wberg82

New Member
Joined
Feb 19, 2016
Messages
38
Worked! What is the significance of writing the macro on different lines?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,573
Office Version
365
Platform
Windows
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
 

wberg82

New Member
Joined
Feb 19, 2016
Messages
38
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,573
Office Version
365
Platform
Windows
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
 

Forum statistics

Threads
1,085,161
Messages
5,382,071
Members
401,768
Latest member
JAWHARRAH

Some videos you may like

This Week's Hot Topics

Top