Hello Everyone,
I was trying to come up with a VBA code which copies data from the last two rows of multiple workbooks in a folder, without out opening them, and pastes it into a master workbook.
After googling for it a lot, I got a VBA code mentioned below:
'credit for this technique goes to John Walkenback
'17 MVP tips, tricks and shortcuts for Excel » The Spreadsheet Page
Sub GetDataDemo()
Dim FilePath$, Row&, Column&, Address$
'change constants & FilePath below to suit
'***************************************
Const FileName$ = "Book1.xls"
Const SheetName$ = "Sheet1"
Const NumRows& = 10
Const NumColumns& = 10
FilePath = ActiveWorkbook.Path & "\"
'***************************************
DoEvents
Application.ScreenUpdating = False
If Dir(FilePath & FileName) = Empty Then
MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist"
Exit Sub
End If
For Row = 1 To NumRows
For Column = 1 To NumColumns
Address = Cells(Row, Column).Address
Cells(Row, Column) = GetData(FilePath, FileName, SheetName, Address)
Columns.AutoFit
Next Column
Next Row
ActiveWindow.DisplayZeros = False
End Sub
Private Function GetData(Path, File, Sheet, Address)
Dim Data$
Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
Range(Address).Range("A1").Address(, , xlR1C1)
GetData = ExecuteExcel4Macro(Data)
End Function
However this code only copies the data from First 10 rows & first 10 columns of one workbook.
I tried to amend the above mentioned code. However, I've been unsuccessful in getting the result that I want.
I want to copy the data from last two rows of all the workbooks present in a folder, without opening those workbooks, and paste it in a master file. In the Master file the data which is supposed to be pasted, should be pasted at the top, after inserting a row, below the header(First Row is the Header).
The above code copies and pastes the data without opening the workbook. However, it does it only for one workbook and copies the data from first 10 rows & first 10 columns.
Any Help is appreciated.
Thanks in Advance
Thanks in advance
I was trying to come up with a VBA code which copies data from the last two rows of multiple workbooks in a folder, without out opening them, and pastes it into a master workbook.
After googling for it a lot, I got a VBA code mentioned below:
'credit for this technique goes to John Walkenback
'17 MVP tips, tricks and shortcuts for Excel » The Spreadsheet Page
Sub GetDataDemo()
Dim FilePath$, Row&, Column&, Address$
'change constants & FilePath below to suit
'***************************************
Const FileName$ = "Book1.xls"
Const SheetName$ = "Sheet1"
Const NumRows& = 10
Const NumColumns& = 10
FilePath = ActiveWorkbook.Path & "\"
'***************************************
DoEvents
Application.ScreenUpdating = False
If Dir(FilePath & FileName) = Empty Then
MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist"
Exit Sub
End If
For Row = 1 To NumRows
For Column = 1 To NumColumns
Address = Cells(Row, Column).Address
Cells(Row, Column) = GetData(FilePath, FileName, SheetName, Address)
Columns.AutoFit
Next Column
Next Row
ActiveWindow.DisplayZeros = False
End Sub
Private Function GetData(Path, File, Sheet, Address)
Dim Data$
Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
Range(Address).Range("A1").Address(, , xlR1C1)
GetData = ExecuteExcel4Macro(Data)
End Function
However this code only copies the data from First 10 rows & first 10 columns of one workbook.
I tried to amend the above mentioned code. However, I've been unsuccessful in getting the result that I want.
I want to copy the data from last two rows of all the workbooks present in a folder, without opening those workbooks, and paste it in a master file. In the Master file the data which is supposed to be pasted, should be pasted at the top, after inserting a row, below the header(First Row is the Header).
The above code copies and pastes the data without opening the workbook. However, it does it only for one workbook and copies the data from first 10 rows & first 10 columns.
Any Help is appreciated.
Thanks in Advance
Thanks in advance