macro help

[projectmanag]

i'm try to make a macro in excel 2007, which takes the data entered in the numbered tabs and paste special vales it in the summary sheet but a new/next free row each time. see attachment to help. once it copies it it wipes the data from the slave sheets.

any help?

cheers...

[Bob Phillips]

[vba]

Public Sub ProcessIt()
Dim LastRow As Long

With Worksheets("Summary")

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
ActiveCell.EntireRow.Copy .Cells(LastRow + 1, "A")
ActiveCell.EntireRow.ClearContents
End With
End Sub
[/vba]

[projectmanag]

unless i'm not running it correctly it doesnt work... only picked up the titles.

[Bob Phillips]

It only move the row of the active cell on the selected sheet.

[projectmanag]

i need the macro to run so the whole data fields moved, of all the sheets which start with a number...

[mdmackillop]

Why would you post a sample without data?

[VBA]Option Explicit
Sub Summary()
Dim ws As Worksheet
Dim i As Long, j As Long, rw As Long

Set ws = Sheets("Summary")
For j = 1 To 7
rw = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
For i = 1 To 7
ws.Cells(rw, i).Value = Sheets(CStr(j)).Cells.Find(ws.Cells(1, i)).Offset(1).Value
Sheets(CStr(j)).Cells.Find(ws.Cells(1, i)).Offset(1).ClearContents
Next i
Next j
End Sub[/VBA]

[projectmanag]

there was some data in it but i lost bits, it doesn't copy all of it, it stops at hours increase and the vba error is object variable or with block variable not set...

[mdmackillop]

Check the consistency of your sample.

[projectmanag]

yep done, clever little macro