i have about 30 worksheets with 18 columns each and varying numbers of rows
that i need to consolidate into one worksheet in order to pivot off the data.
each worksheet has a total row that i do not need to consolidate.

i have attempted to pivot off multiple spreadsheets and couldnt get it to
function properly.

i have also attempted consolidating but the result is not what i
need...short of copying and pasting is there a simple way to consolidate the
data? thank you.

See
http://www.rondebruin.nl/copy2.htm


"se7098" schreef in bericht
...
i have about 30 worksheets with 18 columns each and varying numbers of rows
that i need to consolidate into one worksheet in order to pivot off the
data.
each worksheet has a total row that i do not need to consolidate.

i have attempted to pivot off multiple spreadsheets and couldnt get it to
function properly.

i have also attempted consolidating but the result is not what i
need...short of copying and pasting is there a simple way to consolidate
the
data? thank you.

__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com

Thanks Ron...i'm sure this is great information however my level of expertise
is not nearly high enough for me to understand what i need to do with this.
Thank you for trying to help me.

"Ron de Bruin" wrote:

See
http://www.rondebruin.nl/copy2.htm


"se7098" schreef in bericht
...
i have about 30 worksheets with 18 columns each and varying numbers of rows
that i need to consolidate into one worksheet in order to pivot off the
data.
each worksheet has a total row that i do not need to consolidate.

i have attempted to pivot off multiple spreadsheets and couldnt get it to
function properly.

i have also attempted consolidating but the result is not what i
need...short of copying and pasting is there a simple way to consolidate
the
data? thank you.

__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com

Have you download the example workbook ???

If you want I will help you to change the code for you workbook
But test the workbook first so you see what it do



"se7098" schreef in bericht
...
Thanks Ron...i'm sure this is great information however my level of
expertise
is not nearly high enough for me to understand what i need to do with
this.
Thank you for trying to help me.

"Ron de Bruin" wrote:

See
http://www.rondebruin.nl/copy2.htm


"se7098" schreef in bericht
...
i have about 30 worksheets with 18 columns each and varying numbers of
rows
that i need to consolidate into one worksheet in order to pivot off the
data.
each worksheet has a total row that i do not need to consolidate.

i have attempted to pivot off multiple spreadsheets and couldnt get it
to
function properly.

i have also attempted consolidating but the result is not what i
need...short of copying and pasting is there a simple way to
consolidate
the
data? thank you.

__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com

I attempted to run the macros in the example workbook but it would not allow
me to do so.

"Ron de Bruin" wrote:

Have you download the example workbook ???

If you want I will help you to change the code for you workbook
But test the workbook first so you see what it do



"se7098" schreef in bericht
...
Thanks Ron...i'm sure this is great information however my level of
expertise
is not nearly high enough for me to understand what i need to do with
this.
Thank you for trying to help me.

"Ron de Bruin" wrote:

See
http://www.rondebruin.nl/copy2.htm


"se7098" schreef in bericht
...
i have about 30 worksheets with 18 columns each and varying numbers of
rows
that i need to consolidate into one worksheet in order to pivot off the
data.
each worksheet has a total row that i do not need to consolidate.

i have attempted to pivot off multiple spreadsheets and couldnt get it
to
function properly.

i have also attempted consolidating but the result is not what i
need...short of copying and pasting is there a simple way to
consolidate
the
data? thank you.

__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com

Then your security is set to High so no code will run

ToolsMacroSecurity

Change it there





"se7098" schreef in bericht
...
I attempted to run the macros in the example workbook but it would not
allow
me to do so.

"Ron de Bruin" wrote:

Have you download the example workbook ???

If you want I will help you to change the code for you workbook
But test the workbook first so you see what it do



"se7098" schreef in bericht
...
Thanks Ron...i'm sure this is great information however my level of
expertise
is not nearly high enough for me to understand what i need to do with
this.
Thank you for trying to help me.

"Ron de Bruin" wrote:

See
http://www.rondebruin.nl/copy2.htm


"se7098" schreef in bericht
...
i have about 30 worksheets with 18 columns each and varying numbers
of
rows
that i need to consolidate into one worksheet in order to pivot off
the
data.
each worksheet has a total row that i do not need to consolidate.

i have attempted to pivot off multiple spreadsheets and couldnt get
it
to
function properly.

i have also attempted consolidating but the result is not what i
need...short of copying and pasting is there a simple way to
consolidate
the
data? thank you.

__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com

Thank you. I can see what it does now and Test 2 appears to be the closest to
what i need...what code do i need to replace in order to tailor it for my
specific worksheets?

"Ron de Bruin" wrote:

Then your security is set to High so no code will run

ToolsMacroSecurity

Change it there





"se7098" schreef in bericht
...
I attempted to run the macros in the example workbook but it would not
allow
me to do so.

"Ron de Bruin" wrote:

Have you download the example workbook ???

If you want I will help you to change the code for you workbook
But test the workbook first so you see what it do



"se7098" schreef in bericht
...
Thanks Ron...i'm sure this is great information however my level of
expertise
is not nearly high enough for me to understand what i need to do with
this.
Thank you for trying to help me.

"Ron de Bruin" wrote:

See
http://www.rondebruin.nl/copy2.htm


"se7098" schreef in bericht
...
i have about 30 worksheets with 18 columns each and varying numbers
of
rows
that i need to consolidate into one worksheet in order to pivot off
the
data.
each worksheet has a total row that i do not need to consolidate.

i have attempted to pivot off multiple spreadsheets and couldnt get
it
to
function properly.

i have also attempted consolidating but the result is not what i
need...short of copying and pasting is there a simple way to
consolidate
the
data? thank you.

__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com

each worksheet has a total row that i do not need to consolidate.

Where is the total row that you not want to copy ?


"se7098" schreef in bericht
...
Thank you. I can see what it does now and Test 2 appears to be the closest
to
what i need...what code do i need to replace in order to tailor it for my
specific worksheets?

"Ron de Bruin" wrote:

Then your security is set to High so no code will run

ToolsMacroSecurity

Change it there





"se7098" schreef in bericht
...
I attempted to run the macros in the example workbook but it would not
allow
me to do so.

"Ron de Bruin" wrote:

Have you download the example workbook ???

If you want I will help you to change the code for you workbook
But test the workbook first so you see what it do



"se7098" schreef in bericht
...
Thanks Ron...i'm sure this is great information however my level of
expertise
is not nearly high enough for me to understand what i need to do
with
this.
Thank you for trying to help me.

"Ron de Bruin" wrote:

See
http://www.rondebruin.nl/copy2.htm


"se7098" schreef in bericht
...
i have about 30 worksheets with 18 columns each and varying
numbers
of
rows
that i need to consolidate into one worksheet in order to pivot
off
the
data.
each worksheet has a total row that i do not need to consolidate.

i have attempted to pivot off multiple spreadsheets and couldnt
get
it
to
function properly.

i have also attempted consolidating but the result is not what i
need...short of copying and pasting is there a simple way to
consolidate
the
data? thank you.

__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com

it is the last row of each worksheet...below is what i amended so far...but
it isn't doing anything...thanks.

'Fill in the range that you want to copy
Set CopyRng = sh.UsedRange


Sub CopyRangeFromMultiWorksheets()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim CopyRng As Range

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"

'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
Last = LastRow(DestSh)

'Fill in the range that you want to copy
Set CopyRng = sh.UsedRange


'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 at the example below
this macro
With CopyRng
DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
.Columns.Count).Value = .Value
End With

'Optional: This will copy the sheet name in the H column
DestSh.Cells(Last + 1, "H").Resize(CopyRng.Rows.Count).Value =
sh.Name

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




"Ron de Bruin" wrote:

each worksheet has a total row that i do not need to consolidate.

Where is the total row that you not want to copy ?


"se7098" schreef in bericht
...
Thank you. I can see what it does now and Test 2 appears to be the closest
to
what i need...what code do i need to replace in order to tailor it for my
specific worksheets?

"Ron de Bruin" wrote:

Then your security is set to High so no code will run

ToolsMacroSecurity

Change it there





"se7098" schreef in bericht
...
I attempted to run the macros in the example workbook but it would not
allow
me to do so.

"Ron de Bruin" wrote:

Have you download the example workbook ???

If you want I will help you to change the code for you workbook
But test the workbook first so you see what it do



"se7098" schreef in bericht
...
Thanks Ron...i'm sure this is great information however my level of
expertise
is not nearly high enough for me to understand what i need to do
with
this.
Thank you for trying to help me.

"Ron de Bruin" wrote:

See
http://www.rondebruin.nl/copy2.htm


"se7098" schreef in bericht
...
i have about 30 worksheets with 18 columns each and varying
numbers
of
rows
that i need to consolidate into one worksheet in order to pivot
off
the
data.
each worksheet has a total row that i do not need to consolidate.

i have attempted to pivot off multiple spreadsheets and couldnt
get
it
to
function properly.

i have also attempted consolidating but the result is not what i
need...short of copying and pasting is there a simple way to
consolidate
the
data? thank you.

__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com

If you run Example 2 in my test workbook

Sub CopyDataWithoutHeaders()

Change this line
Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))

to

Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast - 1))



"se7098" schreef in bericht
...
it is the last row of each worksheet...below is what i amended so
far...but
it isn't doing anything...thanks.

'Fill in the range that you want to copy
Set CopyRng = sh.UsedRange


Sub CopyRangeFromMultiWorksheets()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim CopyRng As Range

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"

'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
Last = LastRow(DestSh)

'Fill in the range that you want to copy
Set CopyRng = sh.UsedRange


'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 at the example below
this macro
With CopyRng
DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
.Columns.Count).Value = .Value
End With

'Optional: This will copy the sheet name in the H column
DestSh.Cells(Last + 1, "H").Resize(CopyRng.Rows.Count).Value =
sh.Name

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




"Ron de Bruin" wrote:

each worksheet has a total row that i do not need to consolidate.

Where is the total row that you not want to copy ?


"se7098" schreef in bericht
...
Thank you. I can see what it does now and Test 2 appears to be the
closest
to
what i need...what code do i need to replace in order to tailor it for
my
specific worksheets?

"Ron de Bruin" wrote:

Then your security is set to High so no code will run

ToolsMacroSecurity

Change it there





"se7098" schreef in bericht
...
I attempted to run the macros in the example workbook but it would
not
allow
me to do so.

"Ron de Bruin" wrote:

Have you download the example workbook ???

If you want I will help you to change the code for you workbook
But test the workbook first so you see what it do



"se7098" schreef in bericht
...
Thanks Ron...i'm sure this is great information however my level
of
expertise
is not nearly high enough for me to understand what i need to do
with
this.
Thank you for trying to help me.

"Ron de Bruin" wrote:

See
http://www.rondebruin.nl/copy2.htm


"se7098" schreef in bericht
...
i have about 30 worksheets with 18 columns each and varying
numbers
of
rows
that i need to consolidate into one worksheet in order to
pivot
off
the
data.
each worksheet has a total row that i do not need to
consolidate.

i have attempted to pivot off multiple spreadsheets and
couldnt
get
it
to
function properly.

i have also attempted consolidating but the result is not what
i
need...short of copying and pasting is there a simple way to
consolidate
the
data? thank you.

__________ Information from ESET Smart Security, version of
virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of
virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4286 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus signature database 4286 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com

i'm getting an error...

compile error: sub or function not defined

it's highlighting the words LastRow

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

"Ron de Bruin" wrote:

If you run Example 2 in my test workbook

Sub CopyDataWithoutHeaders()

Change this line
Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))

to

Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast - 1))



"se7098" schreef in bericht
...
it is the last row of each worksheet...below is what i amended so
far...but
it isn't doing anything...thanks.

'Fill in the range that you want to copy
Set CopyRng = sh.UsedRange


Sub CopyRangeFromMultiWorksheets()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim CopyRng As Range

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"

'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
Last = LastRow(DestSh)

'Fill in the range that you want to copy
Set CopyRng = sh.UsedRange


'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 at the example below
this macro
With CopyRng
DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
.Columns.Count).Value = .Value
End With

'Optional: This will copy the sheet name in the H column
DestSh.Cells(Last + 1, "H").Resize(CopyRng.Rows.Count).Value =
sh.Name

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




"Ron de Bruin" wrote:

each worksheet has a total row that i do not need to consolidate.

Where is the total row that you not want to copy ?


"se7098" schreef in bericht
...
Thank you. I can see what it does now and Test 2 appears to be the
closest
to
what i need...what code do i need to replace in order to tailor it for
my
specific worksheets?

"Ron de Bruin" wrote:

Then your security is set to High so no code will run

ToolsMacroSecurity

Change it there





"se7098" schreef in bericht
...
I attempted to run the macros in the example workbook but it would
not
allow
me to do so.

"Ron de Bruin" wrote:

Have you download the example workbook ???

If you want I will help you to change the code for you workbook
But test the workbook first so you see what it do



"se7098" schreef in bericht
...
Thanks Ron...i'm sure this is great information however my level
of
expertise
is not nearly high enough for me to understand what i need to do
with
this.
Thank you for trying to help me.

"Ron de Bruin" wrote:

See
http://www.rondebruin.nl/copy2.htm


"se7098" schreef in bericht
...
i have about 30 worksheets with 18 columns each and varying
numbers
of
rows
that i need to consolidate into one worksheet in order to
pivot
off
the
data.
each worksheet has a total row that i do not need to
consolidate.

i have attempted to pivot off multiple spreadsheets and
couldnt
get
it
to
function properly.

i have also attempted consolidating but the result is not what
i
need...short of copying and pasting is there a simple way to
consolidate
the
data? thank you.

__________ Information from ESET Smart Security, version of
virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of
virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4286 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus signature database 4286 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com

Then you not test in my example workbook but in yours and you forgot to copy
the lastrow function in your workbook.

Copy this function also in your module

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


--

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


"se7098" schreef in bericht
...
i'm getting an error...

compile error: sub or function not defined

it's highlighting the words LastRow

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

"Ron de Bruin" wrote:

If you run Example 2 in my test workbook

Sub CopyDataWithoutHeaders()

Change this line
Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))

to

Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast - 1))



"se7098" schreef in bericht
...
it is the last row of each worksheet...below is what i amended so
far...but
it isn't doing anything...thanks.

'Fill in the range that you want to copy
Set CopyRng = sh.UsedRange


Sub CopyRangeFromMultiWorksheets()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim CopyRng As Range

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"

'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
Last = LastRow(DestSh)

'Fill in the range that you want to copy
Set CopyRng = sh.UsedRange


'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 at the example below
this macro
With CopyRng
DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
.Columns.Count).Value = .Value
End With

'Optional: This will copy the sheet name in the H column
DestSh.Cells(Last + 1, "H").Resize(CopyRng.Rows.Count).Value
=
sh.Name

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




"Ron de Bruin" wrote:

each worksheet has a total row that i do not need to consolidate.

Where is the total row that you not want to copy ?


"se7098" schreef in bericht
...
Thank you. I can see what it does now and Test 2 appears to be the
closest
to
what i need...what code do i need to replace in order to tailor it
for
my
specific worksheets?

"Ron de Bruin" wrote:

Then your security is set to High so no code will run

ToolsMacroSecurity

Change it there





"se7098" schreef in bericht
...
I attempted to run the macros in the example workbook but it would
not
allow
me to do so.

"Ron de Bruin" wrote:

Have you download the example workbook ???

If you want I will help you to change the code for you workbook
But test the workbook first so you see what it do



"se7098" schreef in bericht
...
Thanks Ron...i'm sure this is great information however my
level
of
expertise
is not nearly high enough for me to understand what i need to
do
with
this.
Thank you for trying to help me.

"Ron de Bruin" wrote:

See
http://www.rondebruin.nl/copy2.htm


"se7098" schreef in
bericht
...
i have about 30 worksheets with 18 columns each and varying
numbers
of
rows
that i need to consolidate into one worksheet in order to
pivot
off
the
data.
each worksheet has a total row that i do not need to
consolidate.

i have attempted to pivot off multiple spreadsheets and
couldnt
get
it
to
function properly.

i have also attempted consolidating but the result is not
what
i
need...short of copying and pasting is there a simple way
to
consolidate
the
data? thank you.

__________ Information from ESET Smart Security, version of
virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of
virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of
virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of
virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4286 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4286 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4286 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus signature database 4286 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com

you are a genius! thank you so much...i appreciate your patience with me.

and you are correct...i was using on my workbook not the practice one.

it works perfectly now in mine and will make my job much easier each month
when compiling this data...thanks again!

"Ron de Bruin" wrote:

Then you not test in my example workbook but in yours and you forgot to copy
the lastrow function in your workbook.

Copy this function also in your module

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


--

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


"se7098" schreef in bericht
...
i'm getting an error...

compile error: sub or function not defined

it's highlighting the words LastRow

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

"Ron de Bruin" wrote:

If you run Example 2 in my test workbook

Sub CopyDataWithoutHeaders()

Change this line
Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))

to

Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast - 1))



"se7098" schreef in bericht
...
it is the last row of each worksheet...below is what i amended so
far...but
it isn't doing anything...thanks.

'Fill in the range that you want to copy
Set CopyRng = sh.UsedRange


Sub CopyRangeFromMultiWorksheets()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim CopyRng As Range

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"

'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
Last = LastRow(DestSh)

'Fill in the range that you want to copy
Set CopyRng = sh.UsedRange


'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 at the example below
this macro
With CopyRng
DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
.Columns.Count).Value = .Value
End With

'Optional: This will copy the sheet name in the H column
DestSh.Cells(Last + 1, "H").Resize(CopyRng.Rows.Count).Value
=
sh.Name

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




"Ron de Bruin" wrote:

each worksheet has a total row that i do not need to consolidate.

Where is the total row that you not want to copy ?


"se7098" schreef in bericht
...
Thank you. I can see what it does now and Test 2 appears to be the
closest
to
what i need...what code do i need to replace in order to tailor it
for
my
specific worksheets?

"Ron de Bruin" wrote:

Then your security is set to High so no code will run

ToolsMacroSecurity

Change it there





"se7098" schreef in bericht
...
I attempted to run the macros in the example workbook but it would
not
allow
me to do so.

"Ron de Bruin" wrote:

Have you download the example workbook ???

If you want I will help you to change the code for you workbook
But test the workbook first so you see what it do



"se7098" schreef in bericht
...
Thanks Ron...i'm sure this is great information however my
level
of
expertise
is not nearly high enough for me to understand what i need to
do
with
this.
Thank you for trying to help me.

"Ron de Bruin" wrote:

See
http://www.rondebruin.nl/copy2.htm


"se7098" schreef in
bericht
...
i have about 30 worksheets with 18 columns each and varying
numbers
of
rows
that i need to consolidate into one worksheet in order to
pivot
off
the
data.
each worksheet has a total row that i do not need to
consolidate.

i have attempted to pivot off multiple spreadsheets and
couldnt
get
it
to
function properly.

i have also attempted consolidating but the result is not
what
i
need...short of copying and pasting is there a simple way
to
consolidate
the
data? thank you.

__________ Information from ESET Smart Security, version of
virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of
virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of
virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of
virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus

You are welcome



--

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


"se7098" schreef in bericht
...
you are a genius! thank you so much...i appreciate your patience with me.

and you are correct...i was using on my workbook not the practice one.

it works perfectly now in mine and will make my job much easier each month
when compiling this data...thanks again!

"Ron de Bruin" wrote:

Then you not test in my example workbook but in yours and you forgot to
copy
the lastrow function in your workbook.

Copy this function also in your module

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


--

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


"se7098" schreef in bericht
...
i'm getting an error...

compile error: sub or function not defined

it's highlighting the words LastRow

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

"Ron de Bruin" wrote:

If you run Example 2 in my test workbook

Sub CopyDataWithoutHeaders()

Change this line
Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))

to

Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast - 1))



"se7098" schreef in bericht
...
it is the last row of each worksheet...below is what i amended so
far...but
it isn't doing anything...thanks.

'Fill in the range that you want to copy
Set CopyRng = sh.UsedRange


Sub CopyRangeFromMultiWorksheets()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim CopyRng As Range

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"

'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
Last = LastRow(DestSh)

'Fill in the range that you want to copy
Set CopyRng = sh.UsedRange


'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 at the example
below
this macro
With CopyRng
DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
.Columns.Count).Value = .Value
End With

'Optional: This will copy the sheet name in the H column
DestSh.Cells(Last + 1,
"H").Resize(CopyRng.Rows.Count).Value
=
sh.Name

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




"Ron de Bruin" wrote:

each worksheet has a total row that i do not need to consolidate.

Where is the total row that you not want to copy ?


"se7098" schreef in bericht
...
Thank you. I can see what it does now and Test 2 appears to be
the
closest
to
what i need...what code do i need to replace in order to tailor
it
for
my
specific worksheets?

"Ron de Bruin" wrote:

Then your security is set to High so no code will run

ToolsMacroSecurity

Change it there





"se7098" schreef in bericht
...
I attempted to run the macros in the example workbook but it
would
not
allow
me to do so.

"Ron de Bruin" wrote:

Have you download the example workbook ???

If you want I will help you to change the code for you
workbook
But test the workbook first so you see what it do



"se7098" schreef in
bericht
...
Thanks Ron...i'm sure this is great information however my
level
of
expertise
is not nearly high enough for me to understand what i need
to
do
with
this.
Thank you for trying to help me.

"Ron de Bruin" wrote:

See
http://www.rondebruin.nl/copy2.htm


"se7098" schreef in
bericht
...
i have about 30 worksheets with 18 columns each and
varying
numbers
of
rows
that i need to consolidate into one worksheet in order
to
pivot
off
the
data.
each worksheet has a total row that i do not need to
consolidate.

i have attempted to pivot off multiple spreadsheets and
couldnt
get
it
to
function properly.

i have also attempted consolidating but the result is
not
what
i
need...short of copying and pasting is there a simple
way
to
consolidate
the
data? thank you.

__________ Information from ESET Smart Security, version
of
virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version
of
virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of
virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of
virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of
virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of
virus
signature database 4285 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus

__________ Information from ESET Smart Security, version of virus
signature database 4286 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus signature database 4286 (20090728) __________

The message was checked by ESET Smart Security.

http://www.eset.com