Visual basic (combining wrkbks)
 

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!!


---
Message posted from http://www.ExcelForum.com/

Visual basic (combining wrkbks)
 

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",
"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!!


---
Message posted from http://www.ExcelForum.com/

.

Visual basic (combining wrkbks)
 

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.


---
Message posted from http://www.ExcelForum.com/

Visual basic (combining wrkbks)
 

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



-----Original Message-----
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.


---
Message posted from http://www.ExcelForum.com/

.

Visual basic (combining wrkbks)
 

Thanks very much.


---
Message posted from http://www.ExcelForum.com/

Visual basic (combining wrkbks)
 

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

--
Regards,
Tom Ogilvy


"ojj00u " wrote in 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",
"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!!


---
Message posted from http://www.ExcelForum.com/

Visual basic (combining wrkbks)
 

Cheers Tom - worked great

--
Message posted from http://www.ExcelForum.com