ExcelBanter - Populating one sheet with data from other sheets

Sub ABCD()
Dim sh As Worksheet
Dim sh1 As Worksheet
Dim rng1 As Range, rng As Range
Set sh = ThisWorkbook.Worksheets("L_Summary")
if isempty(sh.Range("A3")) then
set rng = sh.Range("A3")
else
Set rng = sh.Cells(Rows.Count, 1).End(xlUp)(2)
end if

For each sh1 in ThisWorkbook.Worksheets
if Ucase(left(sh1.Name,1)) = "L" and _
sh1.name < sh.name then
sh1.range("A8:D28").Copy rng
sh1.Range("O8:O28).copy rng.offset(0,4)
set rng = rng.offset(21,0)
End if
Next sh1
' if you mean there may be blank rows int he rows 8 to 28, and you can tell
' this by looking at column 1, then this would remove blank rows
' otherwise, take it out
on Error Resume Next
set rng = sh.Range(sh.Range("A3"),sh.Cells(row.count,1).End( xlup))
set rng1 = rng.specialCells(xlblanks)
if not rng1 is nothing then rng1.EntireRow.Delete
On Error goto 0
End Sub

Not sure what you mean by blanks should be ignored.
--
Regards,
Tom Ogilvy

"Giselle" wrote in message
...
I'm having a bit of trouble (OK, a lot of trouble) writing some code.
Here's my situation.

My Workbook has many Worksheets with names like L1-3, F2-6, L2-3, R4-10,
etc. I need to write code that will do the following: (I'll use a
button
to execute this code)

For each sheet whose name starts with 'L', copy the contents of Range
A8:D28
and O8:O28 to a worksheet called L_Summary, starting in row 3 and working
downwards. Blanks should be ignored, and each new set of data should be
appended to the data already existing.

I would be sooooo greatful for any help with this.
much thanks, Giselle