Hi--

I have a bunch of sheets with identical column headings but the number of
rows in each differ and are unpredictable. I'd like to have a code that would
collect all the data from all the worksheets into one.

Anyone know a way?

Hi Invoice

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Invoice" <(E-Mail Removed)> wrote in message news08968B3-1C0A-4921-A38B-(E-Mail Removed)...
> Hi--
>
> I have a bunch of sheets with identical column headings but the number of
> rows in each differ and are unpredictable. I'd like to have a code that would
> collect all the data from all the worksheets into one.
>
> Anyone know a way?

Thanks, Ron. Actually, I knew about your site and was kind of hoping you'd
reply. I'm a little lost as a newbie about which of your options is best for
me. Which one do you recommend for my case?

"Ron de Bruin" wrote:

> Hi Invoice
>
> See this page
> http://www.rondebruin.nl/copy2.htm
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Invoice" <(E-Mail Removed)> wrote in message news08968B3-1C0A-4921-A38B-(E-Mail Removed)...
> > Hi--
> >
> > I have a bunch of sheets with identical column headings but the number of
> > rows in each differ and are unpredictable. I'd like to have a code that would
> > collect all the data from all the worksheets into one.
> >
> > Anyone know a way?
>
>
>

Try this one that copy from row 3 till the last row with data from each sheet
If your data start in row 2 then change the 3 to 2

>>sh.Range(sh.Rows(3), sh.Rows(shLast)).Copy DestSh.Cells(Last + 1, "A")

See also this page
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Copy the macro and function in a normal module

Sub Test5()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim shLast As Long
Dim Last As Long

On Error Resume Next
If Len(ThisWorkbook.Worksheets.Item("Master").Name) = 0 Then
On Error GoTo 0
Application.ScreenUpdating = False
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "Master"
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> DestSh.Name Then
Last = LastRow(DestSh)
shLast = LastRow(sh)

sh.Range(sh.Rows(3), sh.Rows(shLast)).Copy DestSh.Cells(Last + 1, "A")
'Instead of this line you can use the code below to copy only the values
'or use the PasteSpecial option to paste the format also.


'With sh.Range(sh.Rows(3), sh.Rows(shLast))
'DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
'.Columns.Count).Value = .Value
'End With


'sh.Range(sh.Rows(3), sh.Rows(shLast)).Copy
'With DestSh.Cells(Last + 1, "A")
' .PasteSpecial xlPasteValues, , False, False
' .PasteSpecial xlPasteFormats, , False, False
' Application.CutCopyMode = False
'End With

End If
Next
DestSh.Cells(1).Select
Application.ScreenUpdating = True
Else
MsgBox "The sheet Master already exist"
End If
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



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Invoice" <(E-Mail Removed)> wrote in message news:253635A0-B96E-4E60-B52B-(E-Mail Removed)...
> Thanks, Ron. Actually, I knew about your site and was kind of hoping you'd
> reply. I'm a little lost as a newbie about which of your options is best for
> me. Which one do you recommend for my case?
>
> "Ron de Bruin" wrote:
>
>> Hi Invoice
>>
>> See this page
>> http://www.rondebruin.nl/copy2.htm
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>>
>> "Invoice" <(E-Mail Removed)> wrote in message news08968B3-1C0A-4921-A38B-(E-Mail Removed)...
>> > Hi--
>> >
>> > I have a bunch of sheets with identical column headings but the number of
>> > rows in each differ and are unpredictable. I'd like to have a code that would
>> > collect all the data from all the worksheets into one.
>> >
>> > Anyone know a way?
>>
>>
>>

Yes, that's working fine. Thanks!

"Ron de Bruin" wrote:

> Try this one that copy from row 3 till the last row with data from each sheet
> If your data start in row 2 then change the 3 to 2
>
> >>sh.Range(sh.Rows(3), sh.Rows(shLast)).Copy DestSh.Cells(Last + 1, "A")
>
> See also this page
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> Copy the macro and function in a normal module
>
> Sub Test5()
> Dim sh As Worksheet
> Dim DestSh As Worksheet
> Dim shLast As Long
> Dim Last As Long
>
> On Error Resume Next
> If Len(ThisWorkbook.Worksheets.Item("Master").Name) = 0 Then
> On Error GoTo 0
> Application.ScreenUpdating = False
> Set DestSh = ThisWorkbook.Worksheets.Add
> DestSh.Name = "Master"
> For Each sh In ThisWorkbook.Worksheets
> If sh.Name <> DestSh.Name Then
> Last = LastRow(DestSh)
> shLast = LastRow(sh)
>
> sh.Range(sh.Rows(3), sh.Rows(shLast)).Copy DestSh.Cells(Last + 1, "A")
> 'Instead of this line you can use the code below to copy only the values
> 'or use the PasteSpecial option to paste the format also.
>
>
> 'With sh.Range(sh.Rows(3), sh.Rows(shLast))
> 'DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
> '.Columns.Count).Value = .Value
> 'End With
>
>
> 'sh.Range(sh.Rows(3), sh.Rows(shLast)).Copy
> 'With DestSh.Cells(Last + 1, "A")
> ' .PasteSpecial xlPasteValues, , False, False
> ' .PasteSpecial xlPasteFormats, , False, False
> ' Application.CutCopyMode = False
> 'End With
>
> End If
> Next
> DestSh.Cells(1).Select
> Application.ScreenUpdating = True
> Else
> MsgBox "The sheet Master already exist"
> End If
> 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
>
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Invoice" <(E-Mail Removed)> wrote in message news:253635A0-B96E-4E60-B52B-(E-Mail Removed)...
> > Thanks, Ron. Actually, I knew about your site and was kind of hoping you'd
> > reply. I'm a little lost as a newbie about which of your options is best for
> > me. Which one do you recommend for my case?
> >
> > "Ron de Bruin" wrote:
> >
> >> Hi Invoice
> >>
> >> See this page
> >> http://www.rondebruin.nl/copy2.htm
> >>
> >> --
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl
> >>
> >>
> >>
> >> "Invoice" <(E-Mail Removed)> wrote in message news08968B3-1C0A-4921-A38B-(E-Mail Removed)...
> >> > Hi--
> >> >
> >> > I have a bunch of sheets with identical column headings but the number of
> >> > rows in each differ and are unpredictable. I'd like to have a code that would
> >> > collect all the data from all the worksheets into one.
> >> >
> >> > Anyone know a way?
> >>
> >>
> >>
>
>
>