Selecting All Cells

E

Earl Kiosterud

Deus

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

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
 
D

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
 
D

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
 
D

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
 
D

Dave Peterson

Glad it worked for you.


Deus said:
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
Click to expand...
 
Top