Copy rows from multiple workbook into a different workbook (sheet)
 

Please I am very new to Macro. Please Help me on this one. I will really
appreciate it. Will like to copy multiple rows from different workbook into
one sheet in a different workbook. Each row has the same headings.

How do i go about about to use macro. Again I haven't used it before. Please
pardon my ignorance.

Thanks for your help

Copy rows from multiple workbook into a different workbook (sheet)
 

Hi Yossy

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

--

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


"Yossy" wrote in message ...
Please I am very new to Macro. Please Help me on this one. I will really
appreciate it. Will like to copy multiple rows from different workbook into
one sheet in a different workbook. Each row has the same headings.

How do i go about about to use macro. Again I haven't used it before. Please
pardon my ignorance.

Thanks for your help

Copy rows from multiple workbook into a different workbook (sheet)
 

please where do I copy the code in. i opened the vb and to be honest I have
no clue what to do. Please help and also after copying the code in what do i
do next. am sorry for silly question but please help me.

Thanks

"Yossy" wrote:

Please I am very new to Macro. Please Help me on this one. I will really
appreciate it. Will like to copy multiple rows from different workbook into
one sheet in a different workbook. Each row has the same headings.

How do i go about about to use macro. Again I haven't used it before. Please
pardon my ignorance.

Thanks for your help

Copy rows from multiple workbook into a different workbook (sheet)
 

Yossy,

A good place to start would be he
http://www.mvps.org/dmcritchie/excel/getstarted.htm

John

"Yossy" wrote in message
...
please where do I copy the code in. i opened the vb and to be honest I
have
no clue what to do. Please help and also after copying the code in what do
i
do next. am sorry for silly question but please help me.

Thanks

"Yossy" wrote:

Please I am very new to Macro. Please Help me on this one. I will really
appreciate it. Will like to copy multiple rows from different workbook
into
one sheet in a different workbook. Each row has the same headings.

How do i go about about to use macro. Again I haven't used it before.
Please
pardon my ignorance.

Thanks for your help

Copy rows from multiple workbook into a different workbook (sh
 

Sorry Ron. I found a useful explanation in your link. Thanks a big bunch
"Ron de Bruin" wrote:

Hi Yossy

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

--

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


"Yossy" wrote in message ...
Please I am very new to Macro. Please Help me on this one. I will really
appreciate it. Will like to copy multiple rows from different workbook into
one sheet in a different workbook. Each row has the same headings.

How do i go about about to use macro. Again I haven't used it before. Please
pardon my ignorance.

Thanks for your help

Copy rows from multiple workbook into a different workbook (sheet)
 

See also
http://www.rondebruin.nl/code.htm

You find the link to David's page also on that page


--

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


"John Wilson" wrote in message ...
Yossy,

A good place to start would be he
http://www.mvps.org/dmcritchie/excel/getstarted.htm

John

"Yossy" wrote in message
...
please where do I copy the code in. i opened the vb and to be honest I
have
no clue what to do. Please help and also after copying the code in what do
i
do next. am sorry for silly question but please help me.

Thanks

"Yossy" wrote:

Please I am very new to Macro. Please Help me on this one. I will really
appreciate it. Will like to copy multiple rows from different workbook
into
one sheet in a different workbook. Each row has the same headings.

How do i go about about to use macro. Again I haven't used it before.
Please
pardon my ignorance.

Thanks for your help

Copy rows from multiple workbook into a different workbook (sh
 

please i tried using this code
Sub RDB_Copy_Sheet()
Dim myFiles As Variant
Dim myCountOfFiles As Long

myCountOfFiles = Get_File_Names( _
MyPath:="C:\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:="", _
SourceShIndex:=2, _
myReturnedFiles:=myFiles

End Sub

and I get this error, sub or function not defined

Thanks for your help.

Copy rows from multiple workbook into a different workbook (sh
 

Please read the information on the site

Download the example workbook to test the code


--

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


"Yossy" wrote in message ...
please i tried using this code
Sub RDB_Copy_Sheet()
Dim myFiles As Variant
Dim myCountOfFiles As Long

myCountOfFiles = Get_File_Names( _
MyPath:="C:\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:="", _
SourceShIndex:=2, _
myReturnedFiles:=myFiles

End Sub

and I get this error, sub or function not defined

Thanks for your help.

Copy rows from multiple workbook into a different workbook (sh
 

hey Ron,
I have read through the information and made necessary adjustment to the
Get_File_Names and Get _ Sheet function but I don't know what am missing as I
keep getting the same error "sub or function not defined" please help,
anybody help
and it also highlights the "Sub RDB_Copy_Sheet()" and the "Get_File_Names".
I want to believe this is where the error is coming from but have no clue as
to what to do next.

Thanks for helping out. I really appreciate it.

Copy rows from multiple workbook into a different workbook (sh
 

Do you use the example workbook ?

Show me the Sub RDB_Copy_Sheet macro

--

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


"Yossy" wrote in message ...
hey Ron,
I have read through the information and made necessary adjustment to the
Get_File_Names and Get _ Sheet function but I don't know what am missing as I
keep getting the same error "sub or function not defined" please help,
anybody help
and it also highlights the "Sub RDB_Copy_Sheet()" and the "Get_File_Names".
I want to believe this is where the error is coming from but have no clue as
to what to do next.

Thanks for helping out. I really appreciate it.

Copy rows from multiple workbook into a different workbook (sh
 

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

Copy rows from multiple workbook into a different workbook (sh
 

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

Copy rows from multiple workbook into a different workbook (sh
 

If you want it in another workbook then

Copy the Basic_Code_Module also in your workbook
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

Copy rows from multiple workbook into a different workbook (sh
 

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

Copy rows from multiple workbook into a different workbook (sh
 

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

Copy rows from multiple workbook into a different workbook (sh
 

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

Copy rows from multiple workbook into a different workbook (sh
 

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

Copy rows from multiple workbook into a different workbook (sh
 

Hi Ron,
I got it all working now including the copy below each other. Thanks so much
for your prompt responses. I really appreciate it.

"Yossy" wrote:

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

Copy rows from multiple workbook into a different workbook (sh
 

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

Copy rows from multiple workbook into a different workbook (sh
 

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