Selecting All Cells

[Earl Kiosterud]

Deus

Range(Range("A2"), Range("A2").End(xlDown)).EntireRow.Copy
Destination:=Sheets("Total").Range("A2")

 

[Deus DNE]

Hi,

I have a workbook with x number of sheets in it, each sheet contains x rows.
The top row being the column titles. I need to write a macro/VB to select
all rows containing data except the title row, and paste them into a total
sheet.

What I really need is something more complex, but this will do for starters
!

Hope you can help.

TIA

Deus

 

[Deus DNE]

Thanks Earl,

But as i'm really dumb at this stuff, how does this fit in to make it all
just happen.

Workbook is

Total
5AW
5CF etc etc

Need all rows except title copying into row 2 downwards on Total sheet

Apologies for not having a clue

Deus

 

[Dave Peterson]

I used column A of each worksheet to determine the rows to copy (and copied the
headerrow one time):

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim newWks As Worksheet
Dim FirstWks As Boolean
Dim RngToCopy As Range
Dim DestCell As Range

'clean up old Total
Application.DisplayAlerts = False
On Error Resume Next
Worksheets("total").Delete
On Error GoTo 0
Application.DisplayAlerts = True

Set newWks = Worksheets.Add
newWks.Name = "total"

FirstWks = True

For Each wks In ActiveWorkbook.Worksheets
If wks.Name = newWks.Name Then
'do nothing
Else
If FirstWks Then
wks.Rows(1).Copy _
Destination:=newWks.Range("a1")
FirstWks = False
End If

With newWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With wks
.Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) _
.EntireRow.Copy _
Destination:=DestCell
End With
End If
Next wks

End Sub

 

[Deus DNE]

Dave,

Thank you very much

Excellent coding. I know it was probably a simple job for you, but to us
thickos you are a godsend



Deus

 

[Dave Peterson]

Glad it worked for you.

Dave,

Thank you very much

Excellent coding. I know it was probably a simple job for you, but to us
thickos you are a godsend

Deus