combine worksheets in VBA 1

[dustyteter]

Hey all,

Below is some code that collects data from multiple workbooks and pastes the data into a master worksheet...it's not all of the code but just the part I'm interested in modifying. What need is each time I run it I want to append the master file..hence paste the new data set below the data set that was pasted there previously...

Any ideas? Thanks in advance for any help!!


Sub GetData()
Dim BaseBook As Workbook ' The current open book that files will be merged into
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim rnum As Long
Dim lrow As Long
Dim SourceRcount As Long
Dim FNames As String
Dim LastRow As Long


Dim MyFiles, F
Const MyPath = "C:\Expenses" ' Set the path.
Const FileName = "Exp" & "*.xls"

Set BaseBook = ThisWorkbook
MyFiles = ProcessFiles(MyPath, FileName, -1)
rnum = 1

For Each F In MyFiles
If F = "" Then Exit Sub
Set mybook = Workbooks.Open(F)
lrow = Range("A" & Cells.Rows.Count).End(xlUp).Row
Set sourceRange = mybook.Worksheets(1).Range("A8:IV" & lrow)
'Copy from A2:IV? (till the last row with data on your sheet)
SourceRcount = sourceRange.Rows.Count
Set destrange = BaseBook.Worksheets(1).Cells(rnum, "A")
sourceRange.Copy destrange
mybook.Close False
rnum = rnum + SourceRcount
Next

End Sub

 

[GlennUK]

You'd be better off posting this type of question in the VBA Visual Basic for Applications (Microsoft) forum, forum707 than here.

That said, to fix your code to do what you want, replace the line that currently says rnum=1 with

rnum= BaseBook.Worksheets(1).Range("A" & Cells.Rows.Count).End(xlUp).Row

This assumes that your "Master" sheet has entries in column A for every row pasted ... if not, choose a column that does have entries for every row.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[dustyteter]

Thank you very much Glenn, works like a charm.