Make Sheets.(Range 1) = Sheets.(Range 2)

[RyanH]

I am getting an Error on the line indicated below. FacePlastic_BOM is
declared and calculated in another Sub in the UserForm. For now
FacePlastic_BOM = 160.

Dim LastRow As Single

Sheets("BOM & Labor").Unprotect "AdTech"

LastRow = Sheets("BOM & Labor").Cells(Rows.Count, "A").End(xlUp).Row + 1

'plastic material
'ERROR ==> Sheets("BOM & Labor").Range(Cells(LastRow, "A"),
Cells(LastRow, "D")) = _
Sheets("Parts List").Range(Cells(FacePlastic_BOM, "A"),
Cells(FacePlastic_BOM, "D"))


Thanks in Advance,
Ryan

 

[Chip Pearson]

The problem is that in the line

Sheets("BOM & Labor").Range(Cells(LastRow, "A"), Cells(LastRow, "D")) = _
Sheets("Parts List").Range(Cells(FacePlastic_BOM, "A"),
Cells(FacePlastic_BOM, "D"))

the Cells reference refers to the ActiveSheet (since no sheet is specified),
not the Sheets("BOM & Labor") worksheet. Use a With statement as follows:

With Sheets("BOM & Labor")
..Range(Cells(LastRow, "A"), .Cells(LastRow, "D")) = _
Sheets("Parts List").Range(Cells(.FacePlastic_BOM, "A"),
Cells(.FacePlastic_BOM, "D"))
End With

Pay close attention to the '.' characters before Cells. If there is a period
before the word 'Cells', it will refer to the sheet specified in the With
statement. Absent a period, it will refer to the ActiveSheet.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[RyanH]

Thanks for the reply. I copied your code and I am getting an Application
Error. The FacePlastic_BOM variable references a row number in Sheets("Parts
List"). Should it have a "." in front of it?

I tried to rewrite it this way. Now I am not getting an error, but I do not
see the text in Sheets("BOM & Labor") Range. Any ideas?

Sheets("BOM & Labor").Range("A" & LastRow & "" & LastRow) = _
Sheets("Parts List").Range("A" & FacePlastic_BOM & "" & FacePlastic_BOM)

Thanks,
Ryan