Needing to select file path with Set command

  • Thread starter Thread starter mike.wilson8
  • Start date Start date
M

mike.wilson8

I'm currently using this piece of code to define my last column.

Set LastColOpen = Worksheets("Dec").Cells(3, "IV").End(xlToLeft)

I need to expand this code to show that the Worksheet ("Dec") lives on
R:\Something\Something [test.xls])

Something like

Set LastColOpen = Worksheets
(R:\Something\Something[test.xls]).("Dec").Cells(3, "IV").End(xlToLeft)

Any suggetions?
 
Okay, maybe you can help out...

As a test, this code works like a charm if I stay in one workbook

Range("A1:A10").Select

Set LastColOpen = Worksheets("Dec").Cells(3, "IV").End(xlToLeft)
Selection.Copy Destination:=LastColOpen.Offset(0, 1)


What I'm trying to do is copy a range from one workbook and paste the
data into another workbook, but I seem to be having
difficulties...here's what my code looks like

Windows("OfficialReportTemp.xls").Activate
Sheets("Report").Select
Range("A1:A10").Select

'trying to copy a range from this workbook which is open already

Windows("Master PT Daily.xls").Activate
Sheets("Dec").Select
Set LastColOpen = Worksheets("Dec").Cells(3, "IV").End(xlToLeft)
Selection.Copy Destination:=LastColOpen.Offset(0, 1)

'then I want to paste it into this workbook *Master PT Daily*

I'm sure it's something silly, but anyhelp would be appreciated.
 
To copy from OfficialReportTemp.xls to Maste PT Daily.xls:

Workbooks("OfficialReportTemp.xls").Sheets( _
"Report").Range("A1:A10").Copy Destination:= _
Workbooks("Master PT Daily.xls").Worksheets("Dec") _
.Cells(3,"IV").End(xlToLeft).Offset(0,1)


the "space underscore" is a line continuation character. This is one
command spread across 4 lines for ease of copying.
 
Tom, thank you so much it worked...one little question if you don't
mind...I need to add a Paste:=xlValues somewhere in the code because
the data in the cells are actually formulas and I need to paste over
the raw data only.

Thanks again
 
Then two commands

Workbooks("OfficialReportTemp.xls").Sheets( _
"Report").Range("A1:A10").Copy

Workbooks("Master PT Daily.xls").Worksheets("Dec") _
.Cells(3,"IV").End(xlToLeft).Offset(0,1).PasteSpecial xlValues
 
Back
Top