MarcoPoloVB
02-23-2017, 08:15 AM
I have a problem with the VBA code.
I want to write a macro that will open the file named MonthNameConstant, where the name is taken from column A3 and xldown, and Month form B1.
With an open file will copy some data, and then paste to the table with the name.
Assuiming that, how to tell excel which exactly file should it choose to open and copy, and if there is no that file then go next.
I have right now something like this, Can this be dependent on variables?
Sub Data()
Dim wbSource As Workbook
Worksheets("Data").Activate
MsgBox ("Please choose files")
Filenames = Application.GetOpenFilename( _
Title:="Select a File to Import", _
MultiSelect:=True)
TotalWorkbooks = UBound(Filenames)
For I = 1 To TotalWorkbooks
Workbooks.Open FileName:=Filenames(I)
Set wbSource = ActiveWorkbook
wbSource.Sheets(1).Select
Range("B4:M4").Select
Selection.Copy
ThisWorkbook.Worksheets("Data").Cells(2 + I, 5).PasteSpecial _
Paste:=xlPasteValues
wbSource.Close SaveChanges:=False
Next I
End Sub
I want to write a macro that will open the file named MonthNameConstant, where the name is taken from column A3 and xldown, and Month form B1.
With an open file will copy some data, and then paste to the table with the name.
Assuiming that, how to tell excel which exactly file should it choose to open and copy, and if there is no that file then go next.
I have right now something like this, Can this be dependent on variables?
Sub Data()
Dim wbSource As Workbook
Worksheets("Data").Activate
MsgBox ("Please choose files")
Filenames = Application.GetOpenFilename( _
Title:="Select a File to Import", _
MultiSelect:=True)
TotalWorkbooks = UBound(Filenames)
For I = 1 To TotalWorkbooks
Workbooks.Open FileName:=Filenames(I)
Set wbSource = ActiveWorkbook
wbSource.Sheets(1).Select
Range("B4:M4").Select
Selection.Copy
ThisWorkbook.Worksheets("Data").Cells(2 + I, 5).PasteSpecial _
Paste:=xlPasteValues
wbSource.Close SaveChanges:=False
Next I
End Sub