Help with a macro

[Guest]

The macro as recorded.

Sub DAILY()
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Sheets("Running total").Select
Range("B133:AE133").Select
Selection.Copy
Range("B134").Select
ActiveSheet.Paste
Range("B133").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Pending running total").Select
Range("B105:O105").Select
Application.CutCopyMode = False
Selection.Copy
Range("B106").Select
ActiveSheet.Paste
Range("B105").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub

Each day the row portion of the cell address will change by one and I would
like to not make the reference absolute

Do I replace?

Range("B133:AE133").Select
with
Range(Range("A1"), Cells(Rows.Count, "A").End(xlUp)).Select

I don't think so because this just finds the last row correct? In addition,
I'm missing the multiple columns (to AE) (and I'm starting in "A" rather than
"B")

 

[Bob Phillips]

Sub DAILY()
Dim LastRow As Long

With Sheets("Running total")
.Cells(.Rows.Count, "B").End(xlUp).Row
.Cells(LastRow, "B").Resize(1, 28).Copy .Cells(LastRow + 1, "B")
.Cells(LastRow + 1, "B").Value = .Cells(LastRow + 1, "B").Value
End With

With Sheets("Pending running total")
.Cells(.Rows.Count, "B").End(xlUp).Row
.Cells(LastRow, "B").Resize(1, 14).Copy .Cells(LastRow + 1, "B")
.Cells(LastRow + 1, "B").Value = .Cells(LastRow + 1, "B").Value
End With

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

I am a little bit lost. Do you want to find the last row? Copy columns B
through AE of that row and then paste that somewhere or just what. Are
certain cell addresses absolute while other are relative? Give us a quick
rundown of exactly what you want to do...

 

[Guest]

I'm gathering information daily with equations starting in column B and going
through column AE and I have to keep a history of data. Before I allow the
current date's data to enter into the workbook - I'm coping the equations
down one row and range valuing yesterday's results.

I have two reports that I have to keep historical information (which are not
related - except I have to do the work). Hence the two paste special values
commands.

 

[Guest]

I ran the macro and got an "object doesn't support this property or method"
error message.

on this line
..Cells(.Rows.Count, "B").End(xlUp).Row

- I will be on Excel 2003 Friday - I'm still on 2000 until then.

 

[Guest]

If I understand this right - wouldn't I want

..Cells(LastRow + 1, "B").Value = .Cells(LastRow + 1, "B").Value

to read

..Cells(LastRow, "B").Value = .Cells(LastRow, "B").Value

Becuse the equations would now be LastRow +1

and the values in LastRow?

Thanks for your help - I really appreciate it.

 

[Bob Phillips]

Sorry Brad, something got lost in the transcription

Sub DAILY()
Dim LastRow As Long

With Sheets("Running total")
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Cells(LastRow, "B").Resize(1, 28).Copy .Cells(LastRow + 1, "B")
.Cells(LastRow , "B").Value = .Cells(LastRow, "B").Value
End With

With Sheets("Pending running total")
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Cells(LastRow, "B").Resize(1, 14).Copy .Cells(LastRow + 1, "B")
.Cells(LastRow , "B").Value = .Cells(LastRow, "B").Value
End With

End Sub

From your description I can see that you wan t the formulas to move down a
row, and the values to be frozen in the 'old' last row, so you are correct
about the +1. I have included that.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

That did it - thanks again

Need to get that book.