Relative Range Selection & Paste
 

This is probably a really simple question but I haven't been able to find the
right VBA language to make it happen yet. I have a report that I need to add
a formula to in column "C". I want this formula to be copied into each cell
in column "C" until there are no more active rows. Then I want to highlight
the cells that I just filled with the formula, copy them and paste special
(values). Here's what I have so far. It looks like it will only handle the
absolute range of "C3:C93". Any ideas?

Range("C3").Select
Selection.AutoFill Destination:=Range("C3:C93")
Range("C3:C93").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Thanks,

Have_Data_Will_Travel

Relative Range Selection & Paste
 

hi
you didn't say what the formula was so i made one up to test. try this..
Dim c As Long
c = Cells(Rows.Count, "b").End(xlUp).Row
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C" & c)
Range("C3:C"& c).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Post back if questions
regards
FSt1

"Have_Data_Will_Travel" wrote:

This is probably a really simple question but I haven't been able to find the
right VBA language to make it happen yet. I have a report that I need to add
a formula to in column "C". I want this formula to be copied into each cell
in column "C" until there are no more active rows. Then I want to highlight
the cells that I just filled with the formula, copy them and paste special
(values). Here's what I have so far. It looks like it will only handle the
absolute range of "C3:C93". Any ideas?

Range("C3").Select
Selection.AutoFill Destination:=Range("C3:C93")
Range("C3:C93").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Thanks,

Have_Data_Will_Travel

Relative Range Selection & Paste
 

That actually took care of it. I was working with a concatenation formula.
I just took the lines from your example and dropped them into place. Thanks
so much!!

"FSt1" wrote:

hi
you didn't say what the formula was so i made one up to test. try this..
Dim c As Long
c = Cells(Rows.Count, "b").End(xlUp).Row
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C" & c)
Range("C3:C"& c).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Post back if questions
regards
FSt1

"Have_Data_Will_Travel" wrote:

This is probably a really simple question but I haven't been able to find the
right VBA language to make it happen yet. I have a report that I need to add
a formula to in column "C". I want this formula to be copied into each cell
in column "C" until there are no more active rows. Then I want to highlight
the cells that I just filled with the formula, copy them and paste special
(values). Here's what I have so far. It looks like it will only handle the
absolute range of "C3:C93". Any ideas?

Range("C3").Select
Selection.AutoFill Destination:=Range("C3:C93")
Range("C3:C93").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Thanks,

Have_Data_Will_Travel