Copy Paste Merged Cell Format

[Jim May]

My Code:
It Bombs after running Line 6;

Public Sub CellFormat()
Application.ScreenUpdating = False
r = ActiveCell.Row
Worksheets("CellFormat") _
.Range("C1:N1").EntireRow.Copy
Worksheets("Sheet1").Activate
Rows("r:r").Select <<< Bombs Here with R/T error 1004.. Why?
ActiveSheet.Paste
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[Guest]

Hi
what row should R:R be??
This has either be a column or change the reference to a row reference.

Note: In most cases no need for select(activate statements at all

 

[Jim May]

Let's say I'm in Cell C4 on Sheet1 when I run the code.
r = Activecell.row ' r = 4

where later I'm trying to select
rows("4:4").select ' using rows("r:r")


Jim May

 

[Tom Ogilvy]

r = 4
Rows(r).Select

 

[DMoney]

Hope this helps.

<<< you were trying to put a variable in a sring when u
put it in quotes


Application.ScreenUpdating = False
r = ActiveCell.Row
Worksheets("CellFormat") _
.Range("C1:N1").EntireRow.Copy
Worksheets("Sheet1").Activate
Rows(r).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Application.ScreenUpdating = True

 

[Jim May]

Thanks Tom;
Incidentally, last week I was on Google searching for something and easily
enough came across a thread you were involved with in 2003 - where out of
the blue you made the comment "i'm your huckleberry" <<< which immediately
brought a smile to my face being a fan "of a sort" of the legend - Doc
Holliday. I've seen 4 or 5 versions of "Gunfight at the OK Corral" - and
one of the best lines in Tombstone is Val Kilmer saying it to Johnny Ringo;
Only wish in Wyatt Earp they had written it in the script for Dennis Quaid
to have said it.

You probably know "that there is only one thing better than a western,," and
that is "another western!!" - Of course to me Lonesome Dove is the "highest
calling" of all westerns...

Thank you for your contribution to the various Excel NG's - you are much
appreciated.
Jim May
Roanoke

http://www.dochollidaysociety.org/

 

[Tom Ogilvy]

That was Val Kilmer's line for sure.
True, nothing like a good western. Too bad they are so few and far in
between these days <g>