how can I code logic to copy worksheet data from 1 workbook to another?

I currently have a workbook (c:\Jan.xls) with 3 worksheets ( average, income, expense). I need to create a sub routine which opens the Jan.xls and copies all contents from the Jan file's average worksheet and special pastes the values into another workbook's (c:\Feb.xls) worksheet (Feb_average). I also need to repeat the copy for the remaining worksheets (copy income into Feb_income -and - expense to Feb_expense).

I am not sure how to go about coding this. Any suggestions are greatly appreciated?

Thank you for your time in advance.

Welcome to the forum. :)

The best way to get started is to turn on the macro recorder, perform the actions manually, and then review the code. If you're not sure about parts of the code or how to make it more robust then we can certainly help you with that.

Hi Colin,
I have recorded the macro and it worked well. Now I am trying to consolidate the logic to call a subroutine and pass it the parameters. This way the logic is not repeated so many times. In doing so, I keep getting an Run-time error '9' - Subscript out of range. Can't figure out what I am doing wrong? My macro is currently running within the coa_compare excel file.

Any thoughts as to why I am getting this error?

Call Copyx(Sheets("Pool"), Sheets("Cur Pool"))
Call Copyx(Sheets("Maturity"), Sheets("Cur Maturity"))
Call Copyx(Sheets("Volume"), Sheets("Cur Volume"))


Private Sub Copyx(ws1 As Worksheet, ws2 As Worksheet)
Workbooks.Open Filename:="f:\COA_current.xls"
Windows("coa_current.xls").Activate
Sheets("ws1").Select
Cells.Select
Selection.Copy
Windows("COA_compare.xls").Activate
Sheets("ws2").Select
Range("a1").Select
Selection.PasteSpecial Paste:=xlPasteValues
Windows("coa_current.xls").Activate
ActiveWorkbook.Close
End Sub

Good work so far.

You can only reference a sheet in an open workbook. When you try to reference the sheets in your sub calls:

Call Copyx(Sheets("Pool"), Sheets("Cur Pool"))
Call Copyx(Sheets("Maturity"), Sheets("Cur Maturity"))
Call Copyx(Sheets("Volume"), Sheets("Cur Volume"))

it thinks both these sheets belong to the active workbook. At least one of them does not, so you get a "Subscript out of range" error. Rather than passing these arguments as sheet references, I think your intention was to pass them as literal strings.

Let's try to come up with a generic procedure for what you want to do. Does this look about right?


Private Sub CopySheetsTest()
Dim wkbSource As Workbook, wkbTarget As Workbook

Set wkbTarget = Workbooks("COA_compare")
Set wkbSource = Workbooks.Open(Filename:="f:\COA_current.xls")

wkbSource.Worksheets("Pool").Cells.Copy
wkbTarget.Worksheets("Cur Pool").Cells.PasteSpecial Paste:=xlPasteValues

wkbSource.Worksheets("Maturity").Cells.Copy
wkbTarget.Worksheets("Cur Maturity").Cells.PasteSpecial Paste:=xlPasteValues

wkbSource.Worksheets("Volume").Cells.Copy
wkbTarget.Worksheets("Cur Volume").Cells.PasteSpecial Paste:=xlPasteValues

wkbSource.Close savechanges:=False
End Sub


Once we're happy that it does what it should, we can have a look at how to deal with any annoying message boxes or other possible interferences.

For reference, this is x-posted at MrExcel:
http://www.mrexcel.com/forum/showthread.php?t=464463

This worked perfectly. Thank you.

Its amazing on how much of code this logic cut out.
What I did notice, is that the first time I incorporated this logic a few pop up messages came up. The 1st pop up message asked "There is a large amount of information on the clip boad. Do you want to be able to paste this information into antoher program later?" The 2nd pop up indicated there were changes to the source file and do I want to save these changes?" My answers would be NO to each. Is there a way to switch these messages off?

Hi Colin,

Promise this is the last question. I was wondering is there a shorter way to shorten the logic below? Its just copying row 2 from 1 worksheet and pasting it different cell range A2 in another worksheet within the same workbook. Thanks again for all your help.

Windows("COA_compare.xls").Activate
Sheets("Cur Pool").Select
Rows("2:2").Select
Application.CutCopyMode = False
Selection.Copy

Sheets("Changes Pool").Select
Range("A2").Select
ActiveSheet.Paste

Sheets("Not In Cur Pool").Select
Range("A2").Select
ActiveSheet.Paste

This worked perfectly. Thank you.

Its amazing on how much of code this logic cut out.


Yes, the macro recorder is a great way to get started because it (usually) shows you the objects and methods required to do something. The problem with it is it creates unclean code: for example, it records actions such as selecting cells. However, when we come to automating a process via VBA, more often than not we don't need to select cells. This all means that macro recorder VBA code can be tidied up and improved upon. In the example I posted you can see that there are no Select/Activate methods or Selection/Activesheet properties.


Is there a way to switch these messages off?

Yes, do some research on these properties and see if you can come up with something:

Application.CutCopyMode
Application.DisplayAlerts


Hope that helps...

Perfect. Thanks again. :) I am almost there now. I was able to find some helpful hints. I am now just researching how I can possibly mention 2 designations on the same line, opposed to having it on two lines.


Windows("COA_compare.xls").Activate
Sheets("Cur Pool").Rows("2:2").Copy Destination:=Sheets("Changes Pool").Range("a2")
Sheets("Cur Pool").Rows("2:2").Copy Destination:=Sheets("Not In Cur Pool").Range("a2")

Hi,

Hi Colin,

Promise this is the last question. I was wondering is there a shorter way to shorten the logic below? Its just copying row 2 from 1 worksheet and pasting it different cell range A2 in another worksheet within the same workbook. Thanks again for all your help.

Windows("COA_compare.xls").Activate
Sheets("Cur Pool").Select
Rows("2:2").Select
Application.CutCopyMode = False
Selection.Copy

Sheets("Changes Pool").Select
Range("A2").Select
ActiveSheet.Paste

Sheets("Not In Cur Pool").Select
Range("A2").Select
ActiveSheet.Paste


Start by removing all the unnecessary Activate/Select methods. For example:

Application.CutCopyMode = False

Windows("COA_compare.xls").Activate
Sheets("Cur Pool").Select
Rows("2:2").Select
Selection.Copy


becomes:

Application.CutCopyMode = False

Workbooks("COA_compare").Worksheets("Cur Pool").Rows("2:2").Copy


The Workbooks collection property returns a reference to the COA_compare workbook. A workbook object has a Worksheets collection which allows us to reference a worksheet in that workbook. You can't do that in the same way via the Windows collection because a Window object does not have a Sheets or Worksheets collection (it does have an ActiveSheet property which could be used). So to get rid of those Select methods/Selection properties we revert to the Workbooks collection.

The "Cur Pool" worksheet is a member of both the Sheets and Worksheets collections so you use either. The Sheets collection includes all types of sheets such as worksheets and chart sheets, so its a little bit less specific than using Worksheets which only contains Worksheet type sheets. Since we know that "Cur Pool" is a Worksheet I use the Worksheets collection.


The next bit is a little trickier because the Range class does not have a paste method. However, the Range.Copy() method we're using above does have a Destination parameter where we can tell it where to paste to. So this code:

Windows("COA_compare.xls").Activate
Sheets("Cur Pool").Select
Rows("2:2").Select
Application.CutCopyMode = False
Selection.Copy

Sheets("Changes Pool").Select
Range("A2").Select
ActiveSheet.Paste

Sheets("Not In Cur Pool").Select
Range("A2").Select
ActiveSheet.Paste

Becomes:

Application.CutCopyMode = False

Workbooks("COA_compare").Worksheets("Cur Pool").Rows("2:2").Copy Destination:=Workbooks("COA_compare").Worksheets("Changes Pool").Range("A2")
Workbooks("COA_compare").Worksheets("Cur Pool").Rows("2:2").Copy Destination:=Workbooks("COA_compare").Worksheets("Not In Cur Pool").Range("A2")


Those lines of code are quite long so you could make them easier to read by using variables, or by adding line breaks, or perhaps by using a With....End With block.

Hope that helps...

Perfect. Thanks again. :) I am almost there now. I was able to find some helpful hints. I am now just researching how I can possibly mention 2 designations on the same line, opposed to having it on two lines.


Windows("COA_compare.xls").Activate
Sheets("Cur Pool").Rows("2:2").Copy Destination:=Sheets("Changes Pool").Range("a2")
Sheets("Cur Pool").Rows("2:2").Copy Destination:=Sheets("Not In Cur Pool").Range("a2")

Good work. You posted while I was constructing my previous reply, so there's some overlap and some points to take from my previous post. Don't worry about trying to compress these into one line. You can simplify using a With...End With block like this:

With Workbooks("COA_compare").Worksheets("Cur Pool").Rows("2:2")
.Copy Destination:=Sheets("Changes Pool").Range("a2")
.Copy Destination:=Sheets("Not In Cur Pool").Range("a2")
End With


Or you could use a Range variable like this:

Dim rngToCopy As Range

Set rngToCopy = Workbooks("COA_compare").Worksheets("Cur Pool").Rows("2:2")
rngToCopy.Copy Destination:=Sheets("Changes Pool").Range("a2")
rngToCopy.Copy Destination:=Sheets("Not In Cur Pool").Range("a2")

You have been a tremendous help. Thanks again for taking the time to explain. I've learned quite a bit today.
Hope to be able to get your assistance in the future. :)