Macro to paste into different workbook (Excel 2003)

Topic

New Reply

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

Reply | Quote

Replies

(Edited by JohnBF on 10-Nov-04 18:36. Typos.)

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.

Reply | Quote

(Edited by JohnBF on 10-Nov-04 18:36. Typos.)

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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)
‘ DWB.Worksheets.Add
‘ DWB.Worksheets(Worksheets.Count).Name = “Inter-Branch Allocation”
CWB.Sheets(“Inter-Branch Allocation”).Range(“A1216:BI1251”).Copy _
DWB.Sheets(“Inter-Branch Allocation”).Range(“A1216”)
End If

Reply | Quote

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)
‘ DWB.Worksheets.Add
‘ DWB.Worksheets(Worksheets.Count).Name = “Inter-Branch Allocation”
CWB.Sheets(“Inter-Branch Allocation”).Range(“A1216:BI1251”).Copy _
DWB.Sheets(“Inter-Branch Allocation”).Range(“A1216”)
End If

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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”)

Reply | Quote