Combining Data from Multiple Worksheets into 1

[Mike Lewis]

Hello, I need some help. I have a survey that has one spreadsheet for each
customer. I also have a master excel spreadsheet that these need to roll up
to. Does anyone have suggestions of how I can roll up just the data from
these spreadsheets to the master spreadsheet. I am not sure that I want all
the spreadsheets to actually roll into the master spreadsheet or just the
data. I would love to be able to do this with a macro as this will be a
frequent survey.

Thanks in advance for your help.

 

[Ron de Bruin]

Start here
http://www.rondebruin.nl/copy3.htm

Try the add-in

 

[Mike Lewis]

Hello,

I tried cutting and pasting, and I get an error that takes me back to the
top with yellow. Here is what I cut and paste. Sub RDB_Merge_Data() turned
yellow and Get_File_Names highlighted blue.

Thoughts?

Sub RDB_Merge_Data()
Dim myFiles As Variant
Dim myCountOfFiles As Long

myCountOfFiles = Get_File_Names( _
MyPath:="C:\Documents and
Settings\lewism11\Desktop\Survey Subfile", _
Subfolders:=False, _
ExtStr:="*.xls", _
myReturnedFiles:=myFiles)

If myCountOfFiles = 0 Then
MsgBox "No files that match the ExtStr in this folder"
Exit Sub
End If

Get_Data _
FileNameInA:=True, _
PasteAsValues:=True, _
SourceShName:="", _
SourceShIndex:=1, _
SourceRng:="A1:G1", _
StartCell:="", _
myReturnedFiles:=myFiles

End Sub

 

[Ron de Bruin]

Download the example workbook

 

[Mike Lewis]

If I use the add-in and want to sell the template then what? Does the add in
follow the template if someone else uses it?

 

[Mike Lewis]

Got it.....Now, one last question. The section below....

'Add a new workbook with one sheet
Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
Cnum = 1

Can you add the sheet to an existing workbook instead of a new workbook?

 

[Ron de Bruin]

Can you add the sheet to an existing workbook instead of a new workbook?

Yes

Set BaseWks = Thisworkbook.Worksheets("Yourworksheet")

 

[Mike Lewis]

Ron de Bruin,

Ok, since you seem to be a wealth of knowledge, I would like to find out
something else from you. If I am giving this spreadsheet to another person,
how can I make it where they will not need to go into the macro and change
the "my Path" section. Can this be done from the spreadsheet itself,
meaning, can there be a place put on the spreadsheet that would ask them to
put in their path or something easier so that macro access is not needed?

Thanks so much for your help...You have helped me tremendously.

 

[Ron de Bruin]

You can add code to browse to the folder


Try

Sub RDB_Merge_Data()
Dim myFiles As Variant
Dim myCountOfFiles As Long
Dim oApp As Object
Dim oFolder

Set oApp = CreateObject("Shell.Application")

Set oFolder = oApp.BrowseForFolder(0, "Select folder", 512)
If Not oFolder Is Nothing Then
'run the other code
Else
Exit Sub
End If

myCountOfFiles = Get_File_Names( _
MyPath:=oFolder.Self.Path, _
Subfolders:=False, _
ExtStr:="*.xl*", _
myReturnedFiles:=myFiles)

If myCountOfFiles = 0 Then
MsgBox "No files that match the ExtStr in this folder"
Exit Sub
End If

Get_Data _
FileNameInA:=True, _
PasteAsValues:=True, _
SourceShName:="", _
SourceShIndex:=1, _
SourceRng:="A1:G1", _
StartCell:="", _
myReturnedFiles:=myFiles

End Sub