pull data from multiple workbooks....cell vs range

[raylward102]

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.

[mbarron]

Break it into two lines.
OldBk.Sheets("Sheet1").Range("a17:j33").Copy
SumSht.Range("B" & RowCount).PasteSpecial xlValues

[lucas]

raylward102, welcome to the board. If you are posting code, select it while posting and hit the green VBA button to format it for the forum as I have done to your first post.

I would also suggest strongly that you use option explicit as the very top line of your module as you have several variables that are not defined.

[lucas]

This code basically does exactly the same thing even down to mbarron's code to get a range from the closed workbook. It has all the variables diminsioned though:

Option Explicit
Sub open_workbooks_same_folder()
Dim folder As String
Dim Wb As Workbook, sFile As String
Dim Cwb As Workbook
Dim lrow As Long
folder = ThisWorkbook.Path & "\"
' folder = "C:\Temp\"
Set Cwb = ThisWorkbook
sFile = Dir(folder & "*.xls")
Do While sFile <> ""
If sFile <> Cwb.Name Then
'If there are sheets without a data sheet
'continue with code to import
'the rest that has a sheet with the name data
On Error Resume Next
Set Wb = Workbooks.Open(folder & sFile)
lrow = Cwb.Worksheets("Data").Range("A" & Rows.Count).End(xlUp).Row
lrow = lrow + 1
Wb.Worksheets("Sheet1").Range("A1:J10").Copy
Cwb.Worksheets("Data").Range("A" & lrow + 1).PasteSpecial xlPasteValues
Wb.Close True
End If
sFile = Dir
Loop
Cwb.Worksheets("Data").Range("A1").Select
End Sub


You can add your header row to the active sheet using an array also:

Dim myarray As Variant
myarray = Array("Heading 1", "Heading 2", "Heading 3", "Heading 4", "Heading 5")
Range("a1:e1").Value = myarray