mac8795
05-05-2014, 05:26 AM
Hi Folks, First post so as usual please be gentle for any mistakes in my posting.... :)
I have gotten code to take worksheets from mutiple workbooks and insert these sheets into one worksheet - so in theory say i have twelve workbooks for each month of the year. I would like to merge the information from these 12 sheets into a separate workbook but on the same worksheet. As in Jan information, followed by Feb information etc etc.
The problem is that the code i have got does not seem to add the information each time a worksheet is opened in the sode so i only end up with what i think is the information in the last worksheet opened. I apologise for having to be vague but I cannot go into details of the data being used. Here is the code i have gotten so far. Hopefully someone can spot probably the obvious mistake I have done.
I would just like to stress I am no expert in VBA, been a few years since I used it in school....
Sub consolidate()
Dim sh As Worksheet, lr As Long, fPath As String, wb As Workbook, sh2 As Worksheet, fNm As String
Dim lstRw As Long, rng As Range
Set sh = Sheets(2)
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
fPath = "folder with the mutiple workbooks in it"
If Right(fPath, 1) <> "\" Then
fPath = fPath & "\"
End If
fNm = Dir(fPath & "*.xl*")
Do
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set wb = Workbooks.Open(fPath & fNm)
Set sh2 = wb.Sheets(1)
lstRw = sh2.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh2.Range("A2:S2")
rng.EntireRow.Copy sh.Range("A" & lr + 1)
wb.Close False
fNm = Dir
Loop While fNm <> ""
End Sub
Thank you all for even taking the time to read this. And also thanks in advance if you help me out.
:)
I have gotten code to take worksheets from mutiple workbooks and insert these sheets into one worksheet - so in theory say i have twelve workbooks for each month of the year. I would like to merge the information from these 12 sheets into a separate workbook but on the same worksheet. As in Jan information, followed by Feb information etc etc.
The problem is that the code i have got does not seem to add the information each time a worksheet is opened in the sode so i only end up with what i think is the information in the last worksheet opened. I apologise for having to be vague but I cannot go into details of the data being used. Here is the code i have gotten so far. Hopefully someone can spot probably the obvious mistake I have done.
I would just like to stress I am no expert in VBA, been a few years since I used it in school....
Sub consolidate()
Dim sh As Worksheet, lr As Long, fPath As String, wb As Workbook, sh2 As Worksheet, fNm As String
Dim lstRw As Long, rng As Range
Set sh = Sheets(2)
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
fPath = "folder with the mutiple workbooks in it"
If Right(fPath, 1) <> "\" Then
fPath = fPath & "\"
End If
fNm = Dir(fPath & "*.xl*")
Do
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set wb = Workbooks.Open(fPath & fNm)
Set sh2 = wb.Sheets(1)
lstRw = sh2.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh2.Range("A2:S2")
rng.EntireRow.Copy sh.Range("A" & lr + 1)
wb.Close False
fNm = Dir
Loop While fNm <> ""
End Sub
Thank you all for even taking the time to read this. And also thanks in advance if you help me out.
:)