I put this as a reply on a post, but didn't hear back, so thought it best to
post as its own thread.. Thank you in advance for your assistance.
(I am using the website http://www.rondebruin.nl/copy2.htm)

Hello! Thank you so much for your time and assistance! I have copied the very
helpful macro listed at the website (follows is the example I used) and I
have done something wrong because when I go to run the macro, I get an error
message, INVALID OUTSIDE PROCEDURE. I looked that up and really do not know
how to correct it..
I very much appreciate your assistance.. Follows is what I've inserted in
the module:
The DestSh. in this 2nd line is what's highlighted as part of the error
message..

'Copy header row, change the range if you use more columns
If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then
sh.Range("A1:Z1").Copy DestSh.Range("A1")
End If

Sub CopyDataWithoutHeaders()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim shLast As Long
Dim CopyRng As Range
Dim StartRow As Long

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

'Delete the sheet "RDBMergeSheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("RDBMergeSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "RDBMergeSheet"
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "RDBMergeSheet"

'Fill in the start row
StartRow = 2

'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> DestSh.Name Then

'Find the last row with data on the DestSh and sh
Last = LastRow(DestSh)
shLast = LastRow(sh)

'If sh is not empty and if the last row >= StartRow copy the
CopyRng
If shLast > 0 And shLast >= StartRow Then

'Set the range that you want to copy
Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))

'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
GoTo ExitTheSub
End If

'This example copies values/formats, if you only want to
copy the
'values or want to copy everything look below example 1 on
this page
CopyRng.Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

End If

End If
Next

ExitTheSub:

Application.Goto DestSh.Cells(1)

'AutoFit the column width in the DestSh sheet
DestSh.Columns.AutoFit

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

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


Function LastCol(sh As Worksheet)
On Error Resume Next
LastCol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function

Hi Three

You copy the code to add the header row outside the subroutine

Read this from my page

If you want to copy the header row in the first row of the RDBMergeSheet
then copy the code below if each worksheet have the same headers after
this line : If sh.Name <> DestSh.Name Then

'Copy header row, change the range if you use more columns
If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then
sh.Range("A1:Z1").Copy DestSh.Range("A1")
End If

--

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




"Tree" <(E-Mail Removed)> wrote in message news:73746986-0748-4BD5-B1A3-(E-Mail Removed)...
>I put this as a reply on a post, but didn't hear back, so thought it best to
> post as its own thread.. Thank you in advance for your assistance.
> (I am using the website http://www.rondebruin.nl/copy2.htm)
>
> Hello! Thank you so much for your time and assistance! I have copied the very
> helpful macro listed at the website (follows is the example I used) and I
> have done something wrong because when I go to run the macro, I get an error
> message, INVALID OUTSIDE PROCEDURE. I looked that up and really do not know
> how to correct it..
> I very much appreciate your assistance.. Follows is what I've inserted in
> the module:
> The DestSh. in this 2nd line is what's highlighted as part of the error
> message..
>
> 'Copy header row, change the range if you use more columns
> If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then
> sh.Range("A1:Z1").Copy DestSh.Range("A1")
> End If
>
> Sub CopyDataWithoutHeaders()
> Dim sh As Worksheet
> Dim DestSh As Worksheet
> Dim Last As Long
> Dim shLast As Long
> Dim CopyRng As Range
> Dim StartRow As Long
>
> With Application
> .ScreenUpdating = False
> .EnableEvents = False
> End With
>
> 'Delete the sheet "RDBMergeSheet" if it exist
> Application.DisplayAlerts = False
> On Error Resume Next
> ActiveWorkbook.Worksheets("RDBMergeSheet").Delete
> On Error GoTo 0
> Application.DisplayAlerts = True
>
> 'Add a worksheet with the name "RDBMergeSheet"
> Set DestSh = ActiveWorkbook.Worksheets.Add
> DestSh.Name = "RDBMergeSheet"
>
> 'Fill in the start row
> StartRow = 2
>
> 'loop through all worksheets and copy the data to the DestSh
> For Each sh In ActiveWorkbook.Worksheets
> If sh.Name <> DestSh.Name Then
>
> 'Find the last row with data on the DestSh and sh
> Last = LastRow(DestSh)
> shLast = LastRow(sh)
>
> 'If sh is not empty and if the last row >= StartRow copy the
> CopyRng
> If shLast > 0 And shLast >= StartRow Then
>
> 'Set the range that you want to copy
> Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))
>
> 'Test if there enough rows in the DestSh to copy all the data
> If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
> MsgBox "There are not enough rows in the Destsh"
> GoTo ExitTheSub
> End If
>
> 'This example copies values/formats, if you only want to
> copy the
> 'values or want to copy everything look below example 1 on
> this page
> CopyRng.Copy
> With DestSh.Cells(Last + 1, "A")
> .PasteSpecial xlPasteValues
> .PasteSpecial xlPasteFormats
> Application.CutCopyMode = False
> End With
>
> End If
>
> End If
> Next
>
> ExitTheSub:
>
> Application.Goto DestSh.Cells(1)
>
> 'AutoFit the column width in the DestSh sheet
> DestSh.Columns.AutoFit
>
> With Application
> .ScreenUpdating = True
> .EnableEvents = True
> End With
> End Sub
>
> Function LastRow(sh As Worksheet)
> On Error Resume Next
> LastRow = sh.Cells.Find(What:="*", _
> After:=sh.Range("A1"), _
> Lookat:=xlPart, _
> LookIn:=xlFormulas, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlPrevious, _
> MatchCase:=False).Row
> On Error GoTo 0
> End Function
>
>
> Function LastCol(sh As Worksheet)
> On Error Resume Next
> LastCol = sh.Cells.Find(What:="*", _
> After:=sh.Range("A1"), _
> Lookat:=xlPart, _
> LookIn:=xlFormulas, _
> SearchOrder:=xlByColumns, _
> SearchDirection:=xlPrevious, _
> MatchCase:=False).Column
> On Error GoTo 0
> End Function
>

I do not understand how to "add the header row outside the subroutine" - I
have copied the text verbatim and put it as the first code in the module but
I still get the error message.

please advise.
Thank you.


"Ron de Bruin" wrote:

> Hi Three
>
> You copy the code to add the header row outside the subroutine
>
> Read this from my page
>
> If you want to copy the header row in the first row of the RDBMergeSheet
> then copy the code below if each worksheet have the same headers after
> this line : If sh.Name <> DestSh.Name Then
>
> 'Copy header row, change the range if you use more columns
> If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then
> sh.Range("A1:Z1").Copy DestSh.Range("A1")
> End If
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
>
>
> "Tree" <(E-Mail Removed)> wrote in message news:73746986-0748-4BD5-B1A3-(E-Mail Removed)...
> >I put this as a reply on a post, but didn't hear back, so thought it best to
> > post as its own thread.. Thank you in advance for your assistance.
> > (I am using the website http://www.rondebruin.nl/copy2.htm)
> >
> > Hello! Thank you so much for your time and assistance! I have copied the very
> > helpful macro listed at the website (follows is the example I used) and I
> > have done something wrong because when I go to run the macro, I get an error
> > message, INVALID OUTSIDE PROCEDURE. I looked that up and really do not know
> > how to correct it..
> > I very much appreciate your assistance.. Follows is what I've inserted in
> > the module:
> > The DestSh. in this 2nd line is what's highlighted as part of the error
> > message..
> >
> > 'Copy header row, change the range if you use more columns
> > If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then
> > sh.Range("A1:Z1").Copy DestSh.Range("A1")
> > End If
> >
> > Sub CopyDataWithoutHeaders()
> > Dim sh As Worksheet
> > Dim DestSh As Worksheet
> > Dim Last As Long
> > Dim shLast As Long
> > Dim CopyRng As Range
> > Dim StartRow As Long
> >
> > With Application
> > .ScreenUpdating = False
> > .EnableEvents = False
> > End With
> >
> > 'Delete the sheet "RDBMergeSheet" if it exist
> > Application.DisplayAlerts = False
> > On Error Resume Next
> > ActiveWorkbook.Worksheets("RDBMergeSheet").Delete
> > On Error GoTo 0
> > Application.DisplayAlerts = True
> >
> > 'Add a worksheet with the name "RDBMergeSheet"
> > Set DestSh = ActiveWorkbook.Worksheets.Add
> > DestSh.Name = "RDBMergeSheet"
> >
> > 'Fill in the start row
> > StartRow = 2
> >
> > 'loop through all worksheets and copy the data to the DestSh
> > For Each sh In ActiveWorkbook.Worksheets
> > If sh.Name <> DestSh.Name Then
> >
> > 'Find the last row with data on the DestSh and sh
> > Last = LastRow(DestSh)
> > shLast = LastRow(sh)
> >
> > 'If sh is not empty and if the last row >= StartRow copy the
> > CopyRng
> > If shLast > 0 And shLast >= StartRow Then
> >
> > 'Set the range that you want to copy
> > Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))
> >
> > 'Test if there enough rows in the DestSh to copy all the data
> > If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
> > MsgBox "There are not enough rows in the Destsh"
> > GoTo ExitTheSub
> > End If
> >
> > 'This example copies values/formats, if you only want to
> > copy the
> > 'values or want to copy everything look below example 1 on
> > this page
> > CopyRng.Copy
> > With DestSh.Cells(Last + 1, "A")
> > .PasteSpecial xlPasteValues
> > .PasteSpecial xlPasteFormats
> > Application.CutCopyMode = False
> > End With
> >
> > End If
> >
> > End If
> > Next
> >
> > ExitTheSub:
> >
> > Application.Goto DestSh.Cells(1)
> >
> > 'AutoFit the column width in the DestSh sheet
> > DestSh.Columns.AutoFit
> >
> > With Application
> > .ScreenUpdating = True
> > .EnableEvents = True
> > End With
> > End Sub
> >
> > Function LastRow(sh As Worksheet)
> > On Error Resume Next
> > LastRow = sh.Cells.Find(What:="*", _
> > After:=sh.Range("A1"), _
> > Lookat:=xlPart, _
> > LookIn:=xlFormulas, _
> > SearchOrder:=xlByRows, _
> > SearchDirection:=xlPrevious, _
> > MatchCase:=False).Row
> > On Error GoTo 0
> > End Function
> >
> >
> > Function LastCol(sh As Worksheet)
> > On Error Resume Next
> > LastCol = sh.Cells.Find(What:="*", _
> > After:=sh.Range("A1"), _
> > Lookat:=xlPart, _
> > LookIn:=xlFormulas, _
> > SearchOrder:=xlByColumns, _
> > SearchDirection:=xlPrevious, _
> > MatchCase:=False).Column
> > On Error GoTo 0
> > End Function
> >
>

>> If you want to copy the header row in the first row of the RDBMergeSheet
>> then copy the code below if each worksheet have the same headers after
>> this line : If sh.Name <> DestSh.Name Then

If you read the info above it tell you to copy this part

'Copy header row, change the range if you use more columns
If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then
sh.Range("A1:Z1").Copy DestSh.Range("A1")
End If

After this code line in the macro
If sh.Name <> DestSh.Name Then

The macro looks like this if you do that

Sub CopyDataWithoutHeaders2()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim shLast As Long
Dim CopyRng As Range
Dim StartRow As Long

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

'Delete the sheet "RDBMergeSheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("RDBMergeSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "RDBMergeSheet"
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "RDBMergeSheet"

'Fill in the start row
StartRow = 2

'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> DestSh.Name Then

'Copy header row, change the range if you use more columns
If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then
sh.Range("A1:Z1").Copy DestSh.Range("A1")
End If

'Find the last row with data on the DestSh and sh
Last = LastRow(DestSh)
shLast = LastRow(sh)

'If sh is not empty and if the last row >= StartRow copy the CopyRng
If shLast > 0 And shLast >= StartRow Then

'Set the range that you want to copy
Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))

'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
GoTo ExitTheSub
End If

'This example copies values/formats, if you only want to copy the
'values or want to copy everything look below example 1 on this page
CopyRng.Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

End If

End If
Next

ExitTheSub:

Application.Goto DestSh.Cells(1)

'AutoFit the column width in the DestSh sheet
DestSh.Columns.AutoFit

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




--

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




"Tree" <(E-Mail Removed)> wrote in message news:A2B23554-D6E4-4E55-91BF-(E-Mail Removed)...
>I do not understand how to "add the header row outside the subroutine" - I
> have copied the text verbatim and put it as the first code in the module but
> I still get the error message.
>
> please advise.
> Thank you.
>
>
> "Ron de Bruin" wrote:
>
>> Hi Three
>>
>> You copy the code to add the header row outside the subroutine
>>
>> Read this from my page
>>
>> If you want to copy the header row in the first row of the RDBMergeSheet
>> then copy the code below if each worksheet have the same headers after
>> this line : If sh.Name <> DestSh.Name Then
>>
>> 'Copy header row, change the range if you use more columns
>> If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then
>> sh.Range("A1:Z1").Copy DestSh.Range("A1")
>> End If
>>
>> --
>>
>> Regards Ron de Bruin
>> http://www.rondebruin.nl/tips.htm
>>
>>
>>
>>
>> "Tree" <(E-Mail Removed)> wrote in message news:73746986-0748-4BD5-B1A3-(E-Mail Removed)...
>> >I put this as a reply on a post, but didn't hear back, so thought it best to
>> > post as its own thread.. Thank you in advance for your assistance.
>> > (I am using the website http://www.rondebruin.nl/copy2.htm)
>> >
>> > Hello! Thank you so much for your time and assistance! I have copied the very
>> > helpful macro listed at the website (follows is the example I used) and I
>> > have done something wrong because when I go to run the macro, I get an error
>> > message, INVALID OUTSIDE PROCEDURE. I looked that up and really do not know
>> > how to correct it..
>> > I very much appreciate your assistance.. Follows is what I've inserted in
>> > the module:
>> > The DestSh. in this 2nd line is what's highlighted as part of the error
>> > message..
>> >
>> > 'Copy header row, change the range if you use more columns
>> > If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then
>> > sh.Range("A1:Z1").Copy DestSh.Range("A1")
>> > End If
>> >
>> > Sub CopyDataWithoutHeaders()
>> > Dim sh As Worksheet
>> > Dim DestSh As Worksheet
>> > Dim Last As Long
>> > Dim shLast As Long
>> > Dim CopyRng As Range
>> > Dim StartRow As Long
>> >
>> > With Application
>> > .ScreenUpdating = False
>> > .EnableEvents = False
>> > End With
>> >
>> > 'Delete the sheet "RDBMergeSheet" if it exist
>> > Application.DisplayAlerts = False
>> > On Error Resume Next
>> > ActiveWorkbook.Worksheets("RDBMergeSheet").Delete
>> > On Error GoTo 0
>> > Application.DisplayAlerts = True
>> >
>> > 'Add a worksheet with the name "RDBMergeSheet"
>> > Set DestSh = ActiveWorkbook.Worksheets.Add
>> > DestSh.Name = "RDBMergeSheet"
>> >
>> > 'Fill in the start row
>> > StartRow = 2
>> >
>> > 'loop through all worksheets and copy the data to the DestSh
>> > For Each sh In ActiveWorkbook.Worksheets
>> > If sh.Name <> DestSh.Name Then
>> >
>> > 'Find the last row with data on the DestSh and sh
>> > Last = LastRow(DestSh)
>> > shLast = LastRow(sh)
>> >
>> > 'If sh is not empty and if the last row >= StartRow copy the
>> > CopyRng
>> > If shLast > 0 And shLast >= StartRow Then
>> >
>> > 'Set the range that you want to copy
>> > Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))
>> >
>> > 'Test if there enough rows in the DestSh to copy all the data
>> > If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
>> > MsgBox "There are not enough rows in the Destsh"
>> > GoTo ExitTheSub
>> > End If
>> >
>> > 'This example copies values/formats, if you only want to
>> > copy the
>> > 'values or want to copy everything look below example 1 on
>> > this page
>> > CopyRng.Copy
>> > With DestSh.Cells(Last + 1, "A")
>> > .PasteSpecial xlPasteValues
>> > .PasteSpecial xlPasteFormats
>> > Application.CutCopyMode = False
>> > End With
>> >
>> > End If
>> >
>> > End If
>> > Next
>> >
>> > ExitTheSub:
>> >
>> > Application.Goto DestSh.Cells(1)
>> >
>> > 'AutoFit the column width in the DestSh sheet
>> > DestSh.Columns.AutoFit
>> >
>> > With Application
>> > .ScreenUpdating = True
>> > .EnableEvents = True
>> > End With
>> > End Sub
>> >
>> > Function LastRow(sh As Worksheet)
>> > On Error Resume Next
>> > LastRow = sh.Cells.Find(What:="*", _
>> > After:=sh.Range("A1"), _
>> > Lookat:=xlPart, _
>> > LookIn:=xlFormulas, _
>> > SearchOrder:=xlByRows, _
>> > SearchDirection:=xlPrevious, _
>> > MatchCase:=False).Row
>> > On Error GoTo 0
>> > End Function
>> >
>> >
>> > Function LastCol(sh As Worksheet)
>> > On Error Resume Next
>> > LastCol = sh.Cells.Find(What:="*", _
>> > After:=sh.Range("A1"), _
>> > Lookat:=xlPart, _
>> > LookIn:=xlFormulas, _
>> > SearchOrder:=xlByColumns, _
>> > SearchDirection:=xlPrevious, _
>> > MatchCase:=False).Column
>> > On Error GoTo 0
>> > End Function
>> >
>>

It is a truly beautiful thing!! THANK YOU!!!!!!!!!!!!!!!!!!!!

"Ron de Bruin" wrote:

> >> If you want to copy the header row in the first row of the RDBMergeSheet
> >> then copy the code below if each worksheet have the same headers after
> >> this line : If sh.Name <> DestSh.Name Then
>
> If you read the info above it tell you to copy this part
>
> 'Copy header row, change the range if you use more columns
> If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then
> sh.Range("A1:Z1").Copy DestSh.Range("A1")
> End If
>
> After this code line in the macro
> If sh.Name <> DestSh.Name Then
>
> The macro looks like this if you do that
>
> Sub CopyDataWithoutHeaders2()
> Dim sh As Worksheet
> Dim DestSh As Worksheet
> Dim Last As Long
> Dim shLast As Long
> Dim CopyRng As Range
> Dim StartRow As Long
>
> With Application
> .ScreenUpdating = False
> .EnableEvents = False
> End With
>
> 'Delete the sheet "RDBMergeSheet" if it exist
> Application.DisplayAlerts = False
> On Error Resume Next
> ActiveWorkbook.Worksheets("RDBMergeSheet").Delete
> On Error GoTo 0
> Application.DisplayAlerts = True
>
> 'Add a worksheet with the name "RDBMergeSheet"
> Set DestSh = ActiveWorkbook.Worksheets.Add
> DestSh.Name = "RDBMergeSheet"
>
> 'Fill in the start row
> StartRow = 2
>
> 'loop through all worksheets and copy the data to the DestSh
> For Each sh In ActiveWorkbook.Worksheets
> If sh.Name <> DestSh.Name Then
>
> 'Copy header row, change the range if you use more columns
> If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then
> sh.Range("A1:Z1").Copy DestSh.Range("A1")
> End If
>
> 'Find the last row with data on the DestSh and sh
> Last = LastRow(DestSh)
> shLast = LastRow(sh)
>
> 'If sh is not empty and if the last row >= StartRow copy the CopyRng
> If shLast > 0 And shLast >= StartRow Then
>
> 'Set the range that you want to copy
> Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))
>
> 'Test if there enough rows in the DestSh to copy all the data
> If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
> MsgBox "There are not enough rows in the Destsh"
> GoTo ExitTheSub
> End If
>
> 'This example copies values/formats, if you only want to copy the
> 'values or want to copy everything look below example 1 on this page
> CopyRng.Copy
> With DestSh.Cells(Last + 1, "A")
> .PasteSpecial xlPasteValues
> .PasteSpecial xlPasteFormats
> Application.CutCopyMode = False
> End With
>
> End If
>
> End If
> Next
>
> ExitTheSub:
>
> Application.Goto DestSh.Cells(1)
>
> 'AutoFit the column width in the DestSh sheet
> DestSh.Columns.AutoFit
>
> With Application
> .ScreenUpdating = True
> .EnableEvents = True
> End With
> End Sub
>
>
>
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
>
>
> "Tree" <(E-Mail Removed)> wrote in message news:A2B23554-D6E4-4E55-91BF-(E-Mail Removed)...
> >I do not understand how to "add the header row outside the subroutine" - I
> > have copied the text verbatim and put it as the first code in the module but
> > I still get the error message.
> >
> > please advise.
> > Thank you.
> >
> >
> > "Ron de Bruin" wrote:
> >
> >> Hi Three
> >>
> >> You copy the code to add the header row outside the subroutine
> >>
> >> Read this from my page
> >>
> >> If you want to copy the header row in the first row of the RDBMergeSheet
> >> then copy the code below if each worksheet have the same headers after
> >> this line : If sh.Name <> DestSh.Name Then
> >>
> >> 'Copy header row, change the range if you use more columns
> >> If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then
> >> sh.Range("A1:Z1").Copy DestSh.Range("A1")
> >> End If
> >>
> >> --
> >>
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl/tips.htm
> >>
> >>
> >>
> >>
> >> "Tree" <(E-Mail Removed)> wrote in message news:73746986-0748-4BD5-B1A3-(E-Mail Removed)...
> >> >I put this as a reply on a post, but didn't hear back, so thought it best to
> >> > post as its own thread.. Thank you in advance for your assistance.
> >> > (I am using the website http://www.rondebruin.nl/copy2.htm)
> >> >
> >> > Hello! Thank you so much for your time and assistance! I have copied the very
> >> > helpful macro listed at the website (follows is the example I used) and I
> >> > have done something wrong because when I go to run the macro, I get an error
> >> > message, INVALID OUTSIDE PROCEDURE. I looked that up and really do not know
> >> > how to correct it..
> >> > I very much appreciate your assistance.. Follows is what I've inserted in
> >> > the module:
> >> > The DestSh. in this 2nd line is what's highlighted as part of the error
> >> > message..
> >> >
> >> > 'Copy header row, change the range if you use more columns
> >> > If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then
> >> > sh.Range("A1:Z1").Copy DestSh.Range("A1")
> >> > End If
> >> >
> >> > Sub CopyDataWithoutHeaders()
> >> > Dim sh As Worksheet
> >> > Dim DestSh As Worksheet
> >> > Dim Last As Long
> >> > Dim shLast As Long
> >> > Dim CopyRng As Range
> >> > Dim StartRow As Long
> >> >
> >> > With Application
> >> > .ScreenUpdating = False
> >> > .EnableEvents = False
> >> > End With
> >> >
> >> > 'Delete the sheet "RDBMergeSheet" if it exist
> >> > Application.DisplayAlerts = False
> >> > On Error Resume Next
> >> > ActiveWorkbook.Worksheets("RDBMergeSheet").Delete
> >> > On Error GoTo 0
> >> > Application.DisplayAlerts = True
> >> >
> >> > 'Add a worksheet with the name "RDBMergeSheet"
> >> > Set DestSh = ActiveWorkbook.Worksheets.Add
> >> > DestSh.Name = "RDBMergeSheet"
> >> >
> >> > 'Fill in the start row
> >> > StartRow = 2
> >> >
> >> > 'loop through all worksheets and copy the data to the DestSh
> >> > For Each sh In ActiveWorkbook.Worksheets
> >> > If sh.Name <> DestSh.Name Then
> >> >
> >> > 'Find the last row with data on the DestSh and sh
> >> > Last = LastRow(DestSh)
> >> > shLast = LastRow(sh)
> >> >
> >> > 'If sh is not empty and if the last row >= StartRow copy the
> >> > CopyRng
> >> > If shLast > 0 And shLast >= StartRow Then
> >> >
> >> > 'Set the range that you want to copy
> >> > Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))
> >> >
> >> > 'Test if there enough rows in the DestSh to copy all the data
> >> > If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
> >> > MsgBox "There are not enough rows in the Destsh"
> >> > GoTo ExitTheSub
> >> > End If
> >> >
> >> > 'This example copies values/formats, if you only want to
> >> > copy the
> >> > 'values or want to copy everything look below example 1 on
> >> > this page
> >> > CopyRng.Copy
> >> > With DestSh.Cells(Last + 1, "A")
> >> > .PasteSpecial xlPasteValues
> >> > .PasteSpecial xlPasteFormats
> >> > Application.CutCopyMode = False
> >> > End With
> >> >
> >> > End If
> >> >
> >> > End If
> >> > Next
> >> >
> >> > ExitTheSub:
> >> >
> >> > Application.Goto DestSh.Cells(1)
> >> >
> >> > 'AutoFit the column width in the DestSh sheet
> >> > DestSh.Columns.AutoFit
> >> >
> >> > With Application
> >> > .ScreenUpdating = True
> >> > .EnableEvents = True
> >> > End With
> >> > End Sub
> >> >
> >> > Function LastRow(sh As Worksheet)
> >> > On Error Resume Next
> >> > LastRow = sh.Cells.Find(What:="*", _
> >> > After:=sh.Range("A1"), _
> >> > Lookat:=xlPart, _
> >> > LookIn:=xlFormulas, _
> >> > SearchOrder:=xlByRows, _
> >> > SearchDirection:=xlPrevious, _
> >> > MatchCase:=False).Row
> >> > On Error GoTo 0
> >> > End Function
> >> >
> >> >
> >> > Function LastCol(sh As Worksheet)
> >> > On Error Resume Next
> >> > LastCol = sh.Cells.Find(What:="*", _
> >> > After:=sh.Range("A1"), _
> >> > Lookat:=xlPart, _
> >> > LookIn:=xlFormulas, _
> >> > SearchOrder:=xlByColumns, _
> >> > SearchDirection:=xlPrevious, _
> >> > MatchCase:=False).Column
> >> > On Error GoTo 0
> >> > End Function
> >> >
> >>
>

You are welcome

--

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




"Tree" <(E-Mail Removed)> wrote in message news:B938539F-7BDB-42D4-83BC-(E-Mail Removed)...
> It is a truly beautiful thing!! THANK YOU!!!!!!!!!!!!!!!!!!!!
>
> "Ron de Bruin" wrote:
>
>> >> If you want to copy the header row in the first row of the RDBMergeSheet
>> >> then copy the code below if each worksheet have the same headers after
>> >> this line : If sh.Name <> DestSh.Name Then
>>
>> If you read the info above it tell you to copy this part
>>
>> 'Copy header row, change the range if you use more columns
>> If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then
>> sh.Range("A1:Z1").Copy DestSh.Range("A1")
>> End If
>>
>> After this code line in the macro
>> If sh.Name <> DestSh.Name Then
>>
>> The macro looks like this if you do that
>>
>> Sub CopyDataWithoutHeaders2()
>> Dim sh As Worksheet
>> Dim DestSh As Worksheet
>> Dim Last As Long
>> Dim shLast As Long
>> Dim CopyRng As Range
>> Dim StartRow As Long
>>
>> With Application
>> .ScreenUpdating = False
>> .EnableEvents = False
>> End With
>>
>> 'Delete the sheet "RDBMergeSheet" if it exist
>> Application.DisplayAlerts = False
>> On Error Resume Next
>> ActiveWorkbook.Worksheets("RDBMergeSheet").Delete
>> On Error GoTo 0
>> Application.DisplayAlerts = True
>>
>> 'Add a worksheet with the name "RDBMergeSheet"
>> Set DestSh = ActiveWorkbook.Worksheets.Add
>> DestSh.Name = "RDBMergeSheet"
>>
>> 'Fill in the start row
>> StartRow = 2
>>
>> 'loop through all worksheets and copy the data to the DestSh
>> For Each sh In ActiveWorkbook.Worksheets
>> If sh.Name <> DestSh.Name Then
>>
>> 'Copy header row, change the range if you use more columns
>> If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then
>> sh.Range("A1:Z1").Copy DestSh.Range("A1")
>> End If
>>
>> 'Find the last row with data on the DestSh and sh
>> Last = LastRow(DestSh)
>> shLast = LastRow(sh)
>>
>> 'If sh is not empty and if the last row >= StartRow copy the CopyRng
>> If shLast > 0 And shLast >= StartRow Then
>>
>> 'Set the range that you want to copy
>> Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))
>>
>> 'Test if there enough rows in the DestSh to copy all the data
>> If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
>> MsgBox "There are not enough rows in the Destsh"
>> GoTo ExitTheSub
>> End If
>>
>> 'This example copies values/formats, if you only want to copy the
>> 'values or want to copy everything look below example 1 on this page
>> CopyRng.Copy
>> With DestSh.Cells(Last + 1, "A")
>> .PasteSpecial xlPasteValues
>> .PasteSpecial xlPasteFormats
>> Application.CutCopyMode = False
>> End With
>>
>> End If
>>
>> End If
>> Next
>>
>> ExitTheSub:
>>
>> Application.Goto DestSh.Cells(1)
>>
>> 'AutoFit the column width in the DestSh sheet
>> DestSh.Columns.AutoFit
>>
>> With Application
>> .ScreenUpdating = True
>> .EnableEvents = True
>> End With
>> End Sub
>>
>>
>>
>>
>> --
>>
>> Regards Ron de Bruin
>> http://www.rondebruin.nl/tips.htm
>>
>>
>>
>>
>> "Tree" <(E-Mail Removed)> wrote in message news:A2B23554-D6E4-4E55-91BF-(E-Mail Removed)...
>> >I do not understand how to "add the header row outside the subroutine" - I
>> > have copied the text verbatim and put it as the first code in the module but
>> > I still get the error message.
>> >
>> > please advise.
>> > Thank you.
>> >
>> >
>> > "Ron de Bruin" wrote:
>> >
>> >> Hi Three
>> >>
>> >> You copy the code to add the header row outside the subroutine
>> >>
>> >> Read this from my page
>> >>
>> >> If you want to copy the header row in the first row of the RDBMergeSheet
>> >> then copy the code below if each worksheet have the same headers after
>> >> this line : If sh.Name <> DestSh.Name Then
>> >>
>> >> 'Copy header row, change the range if you use more columns
>> >> If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then
>> >> sh.Range("A1:Z1").Copy DestSh.Range("A1")
>> >> End If
>> >>
>> >> --
>> >>
>> >> Regards Ron de Bruin
>> >> http://www.rondebruin.nl/tips.htm
>> >>
>> >>
>> >>
>> >>
>> >> "Tree" <(E-Mail Removed)> wrote in message news:73746986-0748-4BD5-B1A3-(E-Mail Removed)...
>> >> >I put this as a reply on a post, but didn't hear back, so thought it best to
>> >> > post as its own thread.. Thank you in advance for your assistance.
>> >> > (I am using the website http://www.rondebruin.nl/copy2.htm)
>> >> >
>> >> > Hello! Thank you so much for your time and assistance! I have copied the very
>> >> > helpful macro listed at the website (follows is the example I used) and I
>> >> > have done something wrong because when I go to run the macro, I get an error
>> >> > message, INVALID OUTSIDE PROCEDURE. I looked that up and really do not know
>> >> > how to correct it..
>> >> > I very much appreciate your assistance.. Follows is what I've inserted in
>> >> > the module:
>> >> > The DestSh. in this 2nd line is what's highlighted as part of the error
>> >> > message..
>> >> >
>> >> > 'Copy header row, change the range if you use more columns
>> >> > If WorksheetFunction.CountA(DestSh.UsedRange) = 0 Then
>> >> > sh.Range("A1:Z1").Copy DestSh.Range("A1")
>> >> > End If
>> >> >
>> >> > Sub CopyDataWithoutHeaders()
>> >> > Dim sh As Worksheet
>> >> > Dim DestSh As Worksheet
>> >> > Dim Last As Long
>> >> > Dim shLast As Long
>> >> > Dim CopyRng As Range
>> >> > Dim StartRow As Long
>> >> >
>> >> > With Application
>> >> > .ScreenUpdating = False
>> >> > .EnableEvents = False
>> >> > End With
>> >> >
>> >> > 'Delete the sheet "RDBMergeSheet" if it exist
>> >> > Application.DisplayAlerts = False
>> >> > On Error Resume Next
>> >> > ActiveWorkbook.Worksheets("RDBMergeSheet").Delete
>> >> > On Error GoTo 0
>> >> > Application.DisplayAlerts = True
>> >> >
>> >> > 'Add a worksheet with the name "RDBMergeSheet"
>> >> > Set DestSh = ActiveWorkbook.Worksheets.Add
>> >> > DestSh.Name = "RDBMergeSheet"
>> >> >
>> >> > 'Fill in the start row
>> >> > StartRow = 2
>> >> >
>> >> > 'loop through all worksheets and copy the data to the DestSh
>> >> > For Each sh In ActiveWorkbook.Worksheets
>> >> > If sh.Name <> DestSh.Name Then
>> >> >
>> >> > 'Find the last row with data on the DestSh and sh
>> >> > Last = LastRow(DestSh)
>> >> > shLast = LastRow(sh)
>> >> >
>> >> > 'If sh is not empty and if the last row >= StartRow copy the
>> >> > CopyRng
>> >> > If shLast > 0 And shLast >= StartRow Then
>> >> >
>> >> > 'Set the range that you want to copy
>> >> > Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))
>> >> >
>> >> > 'Test if there enough rows in the DestSh to copy all the data
>> >> > If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
>> >> > MsgBox "There are not enough rows in the Destsh"
>> >> > GoTo ExitTheSub
>> >> > End If
>> >> >
>> >> > 'This example copies values/formats, if you only want to
>> >> > copy the
>> >> > 'values or want to copy everything look below example 1 on
>> >> > this page
>> >> > CopyRng.Copy
>> >> > With DestSh.Cells(Last + 1, "A")
>> >> > .PasteSpecial xlPasteValues
>> >> > .PasteSpecial xlPasteFormats
>> >> > Application.CutCopyMode = False
>> >> > End With
>> >> >
>> >> > End If
>> >> >
>> >> > End If
>> >> > Next
>> >> >
>> >> > ExitTheSub:
>> >> >
>> >> > Application.Goto DestSh.Cells(1)
>> >> >
>> >> > 'AutoFit the column width in the DestSh sheet
>> >> > DestSh.Columns.AutoFit
>> >> >
>> >> > With Application
>> >> > .ScreenUpdating = True
>> >> > .EnableEvents = True
>> >> > End With
>> >> > End Sub
>> >> >
>> >> > Function LastRow(sh As Worksheet)
>> >> > On Error Resume Next
>> >> > LastRow = sh.Cells.Find(What:="*", _
>> >> > After:=sh.Range("A1"), _
>> >> > Lookat:=xlPart, _
>> >> > LookIn:=xlFormulas, _
>> >> > SearchOrder:=xlByRows, _
>> >> > SearchDirection:=xlPrevious, _
>> >> > MatchCase:=False).Row
>> >> > On Error GoTo 0
>> >> > End Function
>> >> >
>> >> >
>> >> > Function LastCol(sh As Worksheet)
>> >> > On Error Resume Next
>> >> > LastCol = sh.Cells.Find(What:="*", _
>> >> > After:=sh.Range("A1"), _
>> >> > Lookat:=xlPart, _
>> >> > LookIn:=xlFormulas, _
>> >> > SearchOrder:=xlByColumns, _
>> >> > SearchDirection:=xlPrevious, _
>> >> > MatchCase:=False).Column
>> >> > On Error GoTo 0
>> >> > End Function
>> >> >
>> >>
>>