Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Visual basic (combining wrkbks)
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Visual basic (combining wrkbks)
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is Visual Basic the same as Visual Studio 2008? | Excel Worksheet Functions | |||
In visual basic | Excel Worksheet Functions | |||
visual basic | Excel Worksheet Functions | |||
changing the visual basic in office 2003 to visual studio net | Excel Discussion (Misc queries) | |||
Visual Basic 6.3 | Excel Programming |