Help with a macro
 

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")

Help with a macro
 

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)

"Brad" wrote in message
...
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")

Help with a macro
 

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...
--
HTH...

Jim Thomlinson


"Brad" wrote:

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")

Help with a macro
 

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.


"Jim Thomlinson" wrote:

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...
--
HTH...

Jim Thomlinson


"Brad" wrote:

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")

Help with a macro
 

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.


"Bob Phillips" wrote:

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)

"Brad" wrote in message
...
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")

Help with a macro
 

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" wrote:

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)

"Brad" wrote in message
...
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")

Help with a macro
 

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)

"Brad" wrote in message
...
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" wrote:

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)

"Brad" wrote in message
...
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")

Help with a macro
 

That did it - thanks again

Need to get that book.

"Bob Phillips" wrote:

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)

"Brad" wrote in message
...
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" wrote:

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)

"Brad" wrote in message
...
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")