raylward102
03-05-2010, 09:57 PM
I was using :
Sub Macro1()
'
' Macro1 Macro
Folder = "c:\temp\"
Set SumSht = ActiveSheet
RowCount = 1
Rows(1).Font.Bold = True
SumSht.Cells(1, 1).Value = "FILE NAME"
SumSht.Cells(1, 2).Value = "PAYEE"
SumSht.Cells(1, 3).Value = "DATE"
SumSht.Cells(1, 4).Value = "INVOICE#"
SumSht.Cells(1, 5).Value = "GST"
SumSht.Cells(1, 6).Value = "PST"
SumSht.Cells(1, 7).Value = "TOTAL"
RowCount = RowCount + 1
Range("b5").Select
ActiveWindow.FreezePanes = True
FName = Dir(Folder & "*.xls")
Do While FName <> ""
Set OldBk = Workbooks.Open(Filename:=Folder & FName)
SumSht.Range("A" & RowCount) = FName
SumSht.Range("B" & RowCount) = OldBk.Sheets("Sheet1").Range("a10")
SumSht.Range("C" & RowCount) = OldBk.Sheets("Sheet1").Range("j4")
SumSht.Range("D" & RowCount) = OldBk.Sheets("Sheet1").Range("j5")
SumSht.Range("E" & RowCount) = OldBk.Sheets("Sheet1").Range("j35")
SumSht.Range("F" & RowCount) = OldBk.Sheets("Sheet1").Range("j36")
SumSht.Range("G" & RowCount) = OldBk.Sheets("Sheet1").Range("j37")
RowCount = RowCount + 1
OldBk.Close savechanges:=False
FName = Dir()
Loop
ActiveSheet.Range("A1:G1").Select
ActiveSheet.Cells.EntireColumn.AutoFit
'
End Sub
to get data from mutiple workbooks. It works great! Now I want to get a range of data from each of the workbooks instead of just a cell. I tried manipulatiing the code but was unabple to achieve this. how would i get range a17:j33 from many workbooks? Thanks in adavance!
Edit: VBA tags added to code.
Sub Macro1()
'
' Macro1 Macro
Folder = "c:\temp\"
Set SumSht = ActiveSheet
RowCount = 1
Rows(1).Font.Bold = True
SumSht.Cells(1, 1).Value = "FILE NAME"
SumSht.Cells(1, 2).Value = "PAYEE"
SumSht.Cells(1, 3).Value = "DATE"
SumSht.Cells(1, 4).Value = "INVOICE#"
SumSht.Cells(1, 5).Value = "GST"
SumSht.Cells(1, 6).Value = "PST"
SumSht.Cells(1, 7).Value = "TOTAL"
RowCount = RowCount + 1
Range("b5").Select
ActiveWindow.FreezePanes = True
FName = Dir(Folder & "*.xls")
Do While FName <> ""
Set OldBk = Workbooks.Open(Filename:=Folder & FName)
SumSht.Range("A" & RowCount) = FName
SumSht.Range("B" & RowCount) = OldBk.Sheets("Sheet1").Range("a10")
SumSht.Range("C" & RowCount) = OldBk.Sheets("Sheet1").Range("j4")
SumSht.Range("D" & RowCount) = OldBk.Sheets("Sheet1").Range("j5")
SumSht.Range("E" & RowCount) = OldBk.Sheets("Sheet1").Range("j35")
SumSht.Range("F" & RowCount) = OldBk.Sheets("Sheet1").Range("j36")
SumSht.Range("G" & RowCount) = OldBk.Sheets("Sheet1").Range("j37")
RowCount = RowCount + 1
OldBk.Close savechanges:=False
FName = Dir()
Loop
ActiveSheet.Range("A1:G1").Select
ActiveSheet.Cells.EntireColumn.AutoFit
'
End Sub
to get data from mutiple workbooks. It works great! Now I want to get a range of data from each of the workbooks instead of just a cell. I tried manipulatiing the code but was unabple to achieve this. how would i get range a17:j33 from many workbooks? Thanks in adavance!
Edit: VBA tags added to code.