"stacking" data from multiple sheets

  • Thread starter Thread starter BigDave
  • Start date Start date
B

BigDave

New here. Not necessarily new to Excel, I've assembled a few buttons
combo boxes, vlookup's. Anyway...

I've created a set of five forms on one worksheet. I've added a secon
worksheet for all the data to copy to, basically creating a databas
table (still in Excel).

Is there a way to get these 5 rows of data to "stack" in a seperat
workbook when I have multiple worksheets/tables I want to combine? Th
sheets will be the same, except for the unique data and a unique fil
name.

I may not be explaining this clearly, so please let me know if that i
the case
 
BigDave,

Put all of your files into one folder, then modify the macro below where
indicated with comments to reflect your folder name and the sheet name of
the sheet you want to 'stack'.

Then run the macro, and give it a file name when it asks. The macro opens
each of your files in turn, so it may take a while, depending on your file
count.

HTH,
Bernie
MS Excel MVP

Sub Consolidate()
Dim SheetName As String

'Change this to your sheet name
SheetName = "Sheet1"

With Application
.DisplayAlerts = False
.EnableEvents = False
.ScreenUpdating = False
End With

With Application.FileSearch
.NewSearch
'Change this to your directory
.LookIn = "C:\Excel"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
Set Basebook = Workbooks.Open(.FoundFiles(1))
Basebook.Worksheets(SheetName).Activate
For i = 2 To .FoundFiles.Count
Set myBook = Workbooks.Open(.FoundFiles(i))
Worksheets(SheetName).Range("A1").CurrentRegion.Copy _
Basebook.Worksheets(SheetName).Range("A65536") _
.End(xlUp)(2)
myBook.Close
Next i
Basebook.SaveAs Application. _
GetSaveAsFilename("Consolidated file.xls")
End If
End With

With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With


End Sub
 
Private Sub CommandButton1_Click()
Dim SheetName As String

'Change this to your sheet name
SheetName = "Data"

With Application
.DisplayAlerts = False
.EnableEvents = False
.ScreenUpdating = False
End With

With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\hulw\My Documents\M
Documents\Wksht Project\db"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
Set Basebook = Workbooks.Open(.FoundFiles(1))
Basebook.Worksheets(SheetName).Activate
For i = 2 To .FoundFiles.Count
Set myBook = Workbooks.Open(.FoundFiles(i))
Worksheets(SheetName).Range("A1").CurrentRegion.Copy _
Basebook.Worksheets(SheetName).Range("A65536") _
.End(xlUp)(2)
myBook.Close
Next i
Basebook.SaveAs Application. _
GetSaveAsFilename("RI Data.xls")
End If
End With

With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With


End Sub

I've added this to command button. When I click it, it goes to an hou
glass for one second and then nothing. What have I don incorrectly
 
Dave,

See what's happening by changing

If .Execute() > 0 Then

to

If .Execute() > 0 Then
MsgBox .FoundFiles.Count

to make sure that it is finding files in your folder.

HTH,
Bernie
MS Excel MVP
 
Back
Top