Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jun 2004
    Location
    Itasca, Illinois, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to paste into different workbook (Excel 2003)

    I would like to run a macro from one workbook that will paste formulas into another open workbook. This process will need to be repeated for multiple workbooks with different names. I am using the following macro but, for some reason, the paste portion is not executing properly (it seems to be hanging up on the Range().Select command). Also, I would like to change the Windows().Activate to allow either a prompt or a generic way to point to the other workbook.

    Any help would be greatly appreciated.

    Sheets("Inter-Branch Allocation").Select
    ActiveSheet.Range("A1216:BI1251").Select
    Selection.Copy
    Windows("3-yr plan prototype v2 TEST.xls").Activate
    Sheets("Inter-Branch Allocation").Select
    Range("A1216").Select
    ActiveSheet.Paste

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macro to paste into different workbook (Excel 2003)

    <P ID="edit" class=small>(Edited by JohnBF on 10-Nov-04 18:36. Typos.)</P>This code would run cleaner if you specify the Source workbook and use the Destination parameter of the Copy Method. Without testing, something like:

    Workbook("Source").Sheets("Inter-Branch Allocation").Range("A1216:BI1251").Copy _
    Workbook("3-yr plan prototype v2 TEST.xls").Sheets("Inter-Branch Allocation").Range("A1216")

    or instead of
    Workbook("Source")...
    use
    ThisWorkbook...

    On the part of your question on user selection of the target Workbook, look into the

    Application.GetOpenFilename

    Method. Seacrh in this Forum and you will find examples of its use.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro to paste into different workbook (Excel 2003)

    Hi,
    I've changed ranges etc, but to illustrate what John is saying, here is some sample code to work from:

    Sub MoveData()
    Dim CWB As Workbook
    Dim DWB As Workbook
    Set CWB = ActiveWorkbook
    DestinationBook = Application.GetOpenFilename("All Excel Files, *.xls")
    Workbooks.Open DestinationBook
    Set DWB = ActiveWorkbook
    CWB.Activate
    Sheets(1).Range("A1:B5").Select
    Selection.Copy Destination:=DWB.Sheets(1).Range("A1")
    End Sub

    Cheers
    Regards,
    Rudi

  4. #4
    New Lounger
    Join Date
    Jun 2004
    Location
    Itasca, Illinois, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to paste into different workbook (Excel 2003)

    Thanks Rudi.

    I have incorporated most of your code but I am hitting a snag. Specifically, the line of code:

    Sheets("Inter-Branch Allocation").Range("A1216:BI1251").Select

    is getting flagged with a runtime error 1004 (Select method of Range class failed).

    The entire code as I copied and modified from your post follows:

    Dim CWB As Workbook
    Dim DWB As Workbook
    Set CWB = ActiveWorkbook
    DestinationBook = Application.GetOpenFilename("All Excel Files, *.xls")
    Workbooks.Open DestinationBook
    Set DWB = ActiveWorkbook
    CWB.Activate
    Sheets("Inter-Branch Allocation").Range("A1216:BI1251").Select
    Selection.Copy Destination = DWB.Sheets("Inter-Branch Allocation").Range("A1216")

    Thanks for your help.

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macro to paste into different workbook (Excel 2003)

    The most common reason for a 1004 is that you used a name string that doesn't exist. Does the Destination workbook include a worksheet called "Inter-Branch Allocation", or does it need to be created? If it needs to be created you'll need lines such as the one in red below commented out. If it exists, check the spelling in the Destination WB. Also, here's a few improvements to consider (not tested):

    Dim CWB As Workbook
    Dim DWB As Workbook
    Dim DestinationBook As String

    Set CWB = ActiveWorkbook
    DestinationBook = CStr(Application.GetOpenFilename("All Excel Files, *.xls"))
    If DestinationBook <> "False" Then ' user didn't cancel
    Set DWB = Workbooks.Open(DestinationBook)
    ' <font color=red>DWB.Worksheets.Add
    ' DWB.Worksheets(Worksheets.Count).Name = "Inter-Branch Allocation"</font color=red>
    CWB.Sheets("Inter-Branch Allocation").Range("A1216:BI1251").Copy _
    DWB.Sheets("Inter-Branch Allocation").Range("A1216")
    End If
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    New Lounger
    Join Date
    Jun 2004
    Location
    Itasca, Illinois, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to paste into different workbook (Excel 2003)

    Thanks so much John and Rudi.

    I have code that does exactly what I needed as a result of your valuable input. I still need to tweak a few things (Unprotect the sheets in the destination workbook that I am modifying and add a prompt when I change the source of the links) but it works.

    I am including the code in this thread for the benefit of other users who seek a similar solution.

    Dim DWB As Workbook
    Dim DestinationBook As String
    Set CWB = ActiveWorkbook
    DestinationBook = Application.GetOpenFilename("All Excel Files,*.xls")
    Workbooks.Open DestinationBook
    Set DWB = ActiveWorkbook
    CWB.Activate
    Sheets("Inter-Branch Allocation").Range("A1216:BI1251").Copy DWB.Sheets("Inter-Branch Allocation").Range("A1216")
    Sheets("Detailed Financials").Range("AA82:BT82").Copy DWB.Sheets("Detailed Financials").Range("AA82")
    Sheets("Detailed Financials").Range("AA95:BT95").Copy DWB.Sheets("Detailed Financials").Range("AA95")
    Sheets("Monthly Plan Summary").Range("Y29:BR29").Copy DWB.Sheets("Monthly Plan Summary").Range("Y29")

Posting Permissions

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