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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RyanH - 20 May 2008 15:14 GMT

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

Reply to this Message

Chip Pearson - 20 May 2008 15:36 GMT

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.

Signature

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

>I am getting an Error on the line indicated below. FacePlastic_BOM is
> declared and calculated in another Sub in the UserForm. For now
[quoted text clipped - 14 lines]
> Thanks in Advance,
> Ryan

Reply to this Message

RyanH - 20 May 2008 16:10 GMT

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 & ":D" & LastRow) = _
Sheets("Parts List").Range("A" & FacePlastic_BOM & ":D" & FacePlastic_BOM)

Thanks,
Ryan

> The problem is that in the line
>
[quoted text clipped - 33 lines]
> > Thanks in Advance,
> > Ryan

Reply to this Message