MACRO: i have a master workbook and i want to copy from one...

[mbos015]

i have code to open multiple xls files shown below. as each file opens i
want to copy columns from the newly opened workbook and paste them in
the same place in the master. I have shown a sort of macro code shown
under the #'s below but i don't know how to reference the workbooks and
paste the columns in without using the actual file names...

could someone fill in the blanks for me...


Dim fn As Variant, f As Integer
fn = Application.GetOpenFilename("Excel-files,*.xls", _
1, "Select One Or More Files To Open", , True)
If TypeName(fn) = "Boolean" Then Exit Sub
For f = 1 To UBound(fn)
Debug.Print "Selected file #" & f & ": " & fn(f)
Workbooks.Open fn(f)
MsgBox ActiveWorkbook.Name, , "Active Workbook Name:"


Next f

########################################

'Windows("JUST OPENED WORKBOOK?????:").Activate
' Columns("I:IV").Select
' Range("I2").Activate
' Selection.Copy
' Windows("MASTER WORKBOOK?????").Activate
' Columns("I:IV").Select
' Range("I2").Activate
' ActiveSheet.Paste
then close

 

[Dave Peterson]

I'm not sure what you mean by pasting in the same place. Wouldn't the 2nd
overwrite the 1st and so forth?

But this may help you:

Option Explicit
Sub testme01()

Dim fn As Variant
Dim f As Long
Dim tempWkbk As Workbook
Dim mstrWks As Workbook
Dim DestCell As Range

fn = Application.GetOpenFilename("Excel-files,*.xls", _
1, "Select One Or More Files To Open", , True)
If TypeName(fn) = "Boolean" Then Exit Sub

Set DestCell _
= ThisWorkbook.Worksheets("sheet1").Range("I1")
'or
'Set DestCell _
' = Workbooks("masterworkbook.xls").Worksheets("sheet1").Range("I1")

For f = 1 To UBound(fn)
Set tempWkbk = Workbooks.Open(Filename:=fn(f))
' with activesheet 'the activesheet is in the just opened workbook
'or the left most worksheet in the workbook.
With tempWkbk.Worksheets(1)
.Range("I:I").Copy _
Destination:=DestCell
End With
tempWkbk.Close savechanges:=False
Set DestCell = DestCell.Offset(0, 1)
Next f

End Sub