4

I have 2 workbooks and am trying to find a way to copy a column from wb1 into wb2. I am aware I could just copy/paste, but the idea is to make something so my boss can click the macro and everything populates.

I have been trying a code I came across in another question:

Sub Import()

Dim sourceColumn As Range
Dim destColumn As Range

Set sourceColumn = Workbooks("C:\Documents and Settings\********\My Documents\*********.xlsm").Worksheets(2).Columns("BL")
Set destColumn = Workbooks("C:\Documents and Settings\********\My Documents\*********.xlsm").Worksheets(2).Columns("A")

sourceColumn.Copy Destination = destColumn

End Sub

When I run this, I get a "Subscript Out Of Range" Error.

The source column contains a formula relying on other columns and the destination column is empty, but even when I ran this on dummy workbooks with small digits I got the same error.

Is there something super basic I am missing here?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Aubrey
  • 55
  • 1
  • 1
  • 5
  • You need to open the workbooks, and you don't need the path, so `Workbooks("blah.xlsm")` – Tim Williams Jan 10 '14 at 00:28
  • Is there a way to do this without opening the workbooks? I also tried the code in this post, [link](http://stackoverflow.com/questions/19351832/vba-copy-from-one-workbook-and-paste-into-another), but that gave me an "Object Required" error... – Aubrey Jan 10 '14 at 00:45
  • There may be a way to do it without opening the workbooks, but it's going to be a lot more difficult than just opening the files. You can open them via code and close them when you're done, so the end-user doesn't need to see it happening. – Tim Williams Jan 10 '14 at 00:49
  • I'll try the code in the link I posted above again and see if I have better results. Thanks for your help! – Aubrey Jan 10 '14 at 01:00

1 Answers1

2

EDIT : Just realized what's probably missing from that piece of code you have. Add a ":" in there! Change to destination:=Workbooks(".... and it should work fine.

Extra details : When working with function parameters, you have to add the ":" in order to specify to the computer you're not evaluating an equality, but doing a parameter assignment.

(Old, flashy answer) As I assume this is what you're trying to do; this script will probably do what you want. The style will NOT be preserved however. 

Sub yourSub()

Application.ScreenUpdating = False 'Disables "Screen flashing" between 2 workbooks

Dim colA As Integer, colB As Integer
Dim rowA As Integer, rowB As Integer
Dim wbA As Workbook, wbB As Workbook

Set wbA = ThisWorkbook
Set wbB = Workbooks.Open("C:/YourFilePath/YourFile.xls")

colA = 1 'Replace "1" with the number of the column FROM which you're copying
colB = 1 'Replace "1" with the number of the column TO which you're copying

rowA = 1 'Replace "1" with the number of the starting row of the column FROM which you're copying
rowB = 1 'Replace "1" with the number of the row of the column TO which you're copying

wbA.Activate
lastA = Cells(Rows.Count, colA).End(xlUp).Row 'This finds the last row of the data of the column FROM which you're copying
For x = rowA To lastA 'Loops through all the rows of A
    wbA.Activate
    yourData = Cells(x, colA)
    wbB.Activate
    Cells(rowB, colB) = yourData
    rowB = rowB + 1 'Increments the current line of destination workbook
Next x 'Skips to next row

Application.ScreenUpdating = True 'Re-enables Screen Updating

End Sub

I didn't have time to test this yet. Will do as soon as possible.

Bernard Saucier
  • 2,240
  • 1
  • 19
  • 28
  • I tried this, and at first I got a object error so I added code to activate the exact sheets in the workbooks. Now I am getting a "Subscript out of range" error... I know the code gets as far as `wbA.Activate` and `Worksheets("Placements").Activate`, but then the error pops up. In `lastA = Cells(Rows.Count, colA).End(xlUp).Row` "End" does not appear blue in my code, could I have something wrong there? – Aubrey Jan 14 '14 at 01:06