Visual basic (combining wrkbks)

[ojj00u]

Hi,

I am currently using this piece of code:

Sub GetSheets()
Dim sPath As String, i As Long
Dim varr As Variant
Dim wkbk As Workbook
sPath = "H:\Work\metSLA\December\ResolverGroup\"
varr = Array("metslareportWE0512.xls", "metslareportWE1212.xls",
"metslareportWE191203.xls", "metslareportWE020104.xls")
For i = LBound(varr) To UBound(varr)
Set wkbk = Workbooks.Open(sPath & varr(i))
wkbk.Worksheets(1).Copy After:=ThisWorkbook. _
Worksheets(ThisWorkbook.Worksheets.Count)
wkbk.Close SaveChanges:=False
Next
End Sub

to combine the named files in the array into one workbook. However I
know very little about VB so am unable to edit this piece of code so
that all the combined workbooks are pasted into one worksheet as
opposed to separate ones. It must be very simple to merely append the
information to the end of the last paste but have been trying for a
while and getting a little dispondent!

For example if the first file used rows 1-200, then I want the second
file to fill from row 201 - end etc etc

Please help!!

 

[Rog]

try this :

Sub GetSheets()
Dim sPath As String, i As Long
Dim varr As Variant
Dim wkbk As Workbook
Application.DisplayAlerts = False
sPath = "H:\Work\metSLA\December\ResolverGroup\"
varr = Array
("metslareportWE0512.xls", "metslareportWE1212.xls", "metsl
areportWE191203.xls", "metslareportWE020104.xls")

For i = LBound(varr) To UBound(varr)
Set wkbk = Workbooks.Open(sPath & varr(i))
wkbk.Worksheets(1).UsedRange.Copy
ThisWorkbook.ActiveSheet.Cells
(ThisWorkbook.ActiveSheet.UsedRange.Rows.Count + 2,
1).PasteSpecial
wkbk.Close SaveChanges:=False
Next
End Sub

Rgds

Rog

-----Original Message-----
Hi,

I am currently using this piece of code:

Sub GetSheets()
Dim sPath As String, i As Long
Dim varr As Variant
Dim wkbk As Workbook
sPath = "H:\Work\metSLA\December\ResolverGroup\"
varr = Array

("metslareportWE0512.xls", "metslareportWE1212.xls",

 

[ojj00u]

Fantastic - thanks very much! It leaves a gap of about 1400 blank rows
before pasting them in - not too much of a problem but if you know why
this is I would like to get rid of it but that code has done the trick
thanks for your time.

 

[Rog]

The blank rows are because it finds the first empty row -
i guess the rows are not totally blank. If you think they
are, then delete them all before running the macro, and I
bet it will start in row 3.

Rgds

Rog

 

[ojj00u]

Thanks very much.

 

[Tom Ogilvy]

Sub GetSheets()
Dim sPath As String, i As Long
Dim varr As Variant
Dim wkbk As Workbook
sPath = "H:\Work\metSLA\December\ResolverGroup\"
varr = Array("metslareportWE0512.xls", "metslareportWE1212.xls",
"metslareportWE191203.xls", "metslareportWE020104.xls")
For i = LBound(varr) To UBound(varr)
Set wkbk = Workbooks.Open(sPath & varr(i))
wkbk.Worksheets(1).Range("A1").CurrentRegion.Copy _
Destination:=ThisWorkbook.Worksheets(1). _
Cells(rows.count,1).End(xlup)(2)
wkbk.Close SaveChanges:=False
Next
End Sub

 

[ojj00u]

Cheers Tom - worked great