The workbook merge code and the add-in have a option to merge all data from every worksheet

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Yossy" wrote in message ...
Hi Ron,
Please is it possible it merges more than one sheet from multiple workbook
below each other at the same time. Instead of using only 1 (one)index for
worksheet 1. How do we do multiple sheet from multiple workbook all having
the same corresponding sheet name.

Thanks for your help. I really appreciate it.

"Ron de Bruin" wrote:

Hi Yossy

I will upload a example file this weekend to that page
It is not so diffecult, copy the macro you want to use and the function and you can test.

Install the Merge add-in if you want it easy
http://www.rondebruin.nl/merge.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Yossy" wrote in message ...
Thanks ROn,
For some reason i don't know what i was doing wrong but its working now.
However it only copied the data in various sheets. How do i make it copy data
below each another. I have checked your link
http://www.rondebruin.nl/copy3.htm Merge a range from all workbooks in a
folder (below each other) but there is no downloadable format and I'm way
more confused on what to do to merge data below each other

"Ron de Bruin" wrote:

Have you try the example workbook ?
Is it working ?

If you copy the code in another workbook you must
copy/paste the code from the Basic_Code_Module and the code freom the sheet example module.

But use the example workbook, no need to copy the code in a new workbook

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Yossy" wrote in message ...
Good Morning Ron,
PLEASE what do u mean by "Copy the Basic_Code_Module also in your workbook
together with the module with the sheet example (name is not important)". I
have tried all possible option but still get error "Sub or function not
defined" and when i tried to use your own code my macro wouldn't run either.
Gave error as to security issue and certificate problem. Please help me on
this one.

thanks a big bunch


"Ron de Bruin" wrote:

If you want it in another workbook then

together with the module with the sheet example (name is not important)

Good night



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message ...
You not copy the Get_File_Names macro in the module

Download the example workbook
Make your changes there and run the code



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Yossy" wrote in message ...
Hey Ron there u go!! Please help. thanks. Also I was wondering do I need to
change the Module name to Get_Sheet_Macro, if so how do i do that. I tried to
but couldn't.
The code below I placed in Module1 that I created.

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

myCountOfFiles = Get_File_Names( _
MyPath:="C:\Documents and Settings\Desktop\Sample", _
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_Sheet _
PasteAsValues:=True, _
SourceShName:="Profile", _
SourceShIndex:=2, _
myReturnedFiles:=myFiles

End Sub

' Note: You not have to change the macro below, you only
' edit and run the RDB_Copy_Sheet above.

Sub Get_Sheet(PasteAsValues As Boolean, SourceShName As String, _
SourceShIndex As Integer, myReturnedFiles As Variant)
Dim mybook As Workbook, BaseWks As Worksheet
Dim CalcMode As Long
Dim SourceSh As Variant
Dim sh As Worksheet
Dim I As Long

'Change ScreenUpdating, Calculation and EnableEvents
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With

On Error GoTo ExitTheSub

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


'Check if we use a named sheet or the index
If SourceShName = "" Then
SourceSh = SourceShIndex
Else
SourceSh = SourceShName
End If

'Loop through all files in the array(myFiles)
For I = LBound(myReturnedFiles) To UBound(myReturnedFiles)
Set mybook = Nothing
On Error Resume Next
Set mybook = Workbooks.Open(myReturnedFiles(I))
On Error GoTo 0

If Not mybook Is Nothing Then

'Set sh and check if it is a valid
On Error Resume Next
Set sh = mybook.Sheets(SourceSh)

If Err.Number 0 Then
Err.Clear
Set sh = Nothing
End If
On Error GoTo 0

If Not sh Is Nothing Then
sh.Copy
after:=BaseWks.Parent.Sheets(BaseWks.Parent.Sheets .Count)

On Error Resume Next
ActiveSheet.Name = mybook.Name
On Error GoTo 0

If PasteAsValues = True Then
With ActiveSheet.UsedRange
.Value = .Value
End With
End If

End If
'Close the workbook without saving
mybook.Close savechanges:=False
End If

'Open the next workbook
Next I

' delete the first sheet in the workbook
Application.DisplayAlerts = False
On Error Resume Next
BaseWks.Delete
On Error GoTo 0
Application.DisplayAlerts = True

ExitTheSub:
'Restore ScreenUpdating, Calculation and EnableEvents
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
End Sub