Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Sheets.(Range 1) = Sheets.(Range 2)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Sheets.(Range 1) = Sheets.(Range 2)
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make Sheets.(Range 1) = Sheets.(Range 2)
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 "Chip Pearson" wrote: 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" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
same range name in different sheets | Excel Discussion (Misc queries) | |||
How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Worksheet Functions | |||
How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Programming | |||
range of data from different sheets | Charts and Charting in Excel | |||
sheets.range and adding cells that are out of the range | Excel Programming |