+ Reply to Thread
Results 1 to 6 of 6

Needing to select file path with Set command

  1. #1

    Needing to select file path with Set command

    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?


  2. #2
    Tom Ogilvy
    Guest

    Re: Needing to select file path with Set command

    The workbook must be open to use that command, so You shouldn't need to
    expand it.

    --
    Regards,
    Tom Ogilvy


    <[email protected]> wrote in message
    news:[email protected]...
    > 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?
    >




  3. #3

    Re: Needing to select file path with Set command

    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.


  4. #4
    Tom Ogilvy
    Guest

    Re: Needing to select file path with Set command

    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.

    --
    Regards,
    Tom Ogilvy




    <[email protected]> wrote in message
    news:[email protected]...
    > 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.
    >




  5. #5

    Re: Needing to select file path with Set command

    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


  6. #6
    Tom Ogilvy
    Guest

    Re: Needing to select file path with Set command

    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

    --
    Regards,
    Tom Ogilvy



    <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1