How to copy columns from closed workbook to new workbook using VBA?

Question

Right now, I'm creating a new workbook with 12 column names all in row 1 and every time I run my macro to test it, the workbook has a different name (Book1, Book2,....)

I want to get specific columns from a CLOSED workbook and paste them to different columns in this new workbook. I do NOT want to use the GetValuesFromAClosedWorkbook because in my macro, the user is picking the file ("Y:...\Test.xls").

I have the following code which works, but is not as automated as I would like it to be because I will not know the name of the workbook...whether if it's Book1, Book2, and so on...AND because it's opening the file, which I don't want it to do.

Workbooks.Open Filename:="Y:\......\Test.xls"
Range("D3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Book1").Activate
ActiveWorkbook.ActiveSheet.Range("B2").PasteSpecial Paste:=xlValues

Is it possible that once the user picks the file, I can pick certain columns and paste them to this new workbook?

Any help will be appreciated! Thank you!

Does the workbook have to be closed? You cant just turn off screenupdating while you open the workbook and copy the columns? — Tim Williams
@TimWilliams I suppose it doesn't have to be off and I could do that...but how would I fix the other problem where I'm specifying the added workbook name and instead, perform the code on the added workbook, no matter what the name is? — Kristina
There is the workbook where I'm running the macro (AutomateFiles.xlsm) and the new workbook added (Book1, Book2,....) — Kristina

Tim Williams Tim Williams · Accepted Answer · 2012-09-10T16:14:03

When you open a workbook you can do this:

Dim wb as WorkBook
Set wb = Workbooks.Open("filepathhere")

Or when you add a new workbook:

Set wb = Workbooks.Add()

You can then use the reference wb:

Dim rngCopy as Range
Set rngCopy = wb.Sheets(1).Range(.Range("D3"), .Range("D3").End(xlDown))

wb2.Sheets("sheetname").Range("B2").Resize(rngCopy.Rows.Count).Value = rngCopy.Value

Where wb is the file you opened and wb2 is the newly-added workbook.

Using a "helper" sub to perform the copying:

Sub DoCopy(rngFrom as Range, rngTo as Range)
    Dim rngCopy as Range
    Set rngCopy = rngFrom.Parent.Range(rngFrom,rngFrom.End(xlDown))
    rngTo.Resize(rngCopy.Rows.Count).Value = rngCopy.Value
End Sub

If you want to copy both values and formats:

Sub DoCopy(rngFrom As Range, rngTo As Range)
    rngFrom.Parent.Range(rngFrom, rngFrom.End(xlDown)).Copy
    rngTo.PasteSpecial xlPasteFormats
    rngTo.PasteSpecial xlPasteValues
End Sub

Usage:

'updated to copy values to first empty row (NOTE: all copied columns
'   must be the same length)
Dim shtDest as Worksheet
Set shtDest= wb2.Sheets("sheetname")

DoCopy wb.Sheets(1).Range("D3"), shtDest.Cells(.Rows.Count, 2).end(xlUp).offset(1,0)
DoCopy wb.Sheets(1).Range("G3"), shtDest.Cells(.Rows.Count, 3).end(xlUp).offset(1,0)

How to copy columns from closed workbook to new workbook using VBA?

1 Answers