PDA

View Full Version : macro help



projectmanag
03-21-2009, 10:24 AM
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
03-21-2009, 11:02 AM
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

projectmanag
03-21-2009, 11:05 AM
unless i'm not running it correctly it doesnt work... only picked up the titles.

Bob Phillips
03-21-2009, 11:14 AM
It only move the row of the active cell on the selected sheet.

projectmanag
03-21-2009, 11:32 AM
i need the macro to run so the whole data fields moved, of all the sheets which start with a number...

mdmackillop
03-21-2009, 01:15 PM
Why would you post a sample without data?

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

projectmanag
03-21-2009, 01:26 PM
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
03-21-2009, 01:30 PM
Check the consistency of your sample.

projectmanag
03-21-2009, 01:53 PM
yep done, clever little macro