Inserting copied Formated Rows with a Macro

[shmoes]

Hello!

I'm trying to create a Macro to do some row formatting, for clarity, it's formatting for design not numbers/formulas.

I started a thread here Link, but was directed to post here, I hope anyone can help.

Originally, I thought I could just use the macro recorder format my cells and save, next time I wanted to format the 4 rows it would be as simple as CTRL+Q..
of course that would've been to easy it didn't work I couldn't get it to skip the rows I wanted blank (original macro recording is on the link above sorry I didn't use the code tag).

Then I changed my way of thinking and thought, If I put the format style at the top of the page (then hide the rows), it should be easy to record a macro that copies and pastes those cells/rows to the current location. And sure with a minor modification and google searching I was right! I came up with a simple line as

Sub New_Safety_Item()
'
' New_Safety_Item Macro
' Add New Safety Item
'
' Keyboard Shortcut: Ctrl+q
'
Range("A1:C4").Copy Destination:=Cells(ActiveCell.Row + 1, 1)
End Sub

Worked great!, but if I soon realized I needed to put items between other previously pasted items, so following the same logic I recorded another with an insert function instead of paste.. Below is what was recorded, Rows("18:18").Select has no signifigant relivance other than it's the row I chose to insert my formatting, it could've been any row and any point in the document.

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+p
'
    Rows("1:4").Select
    Selection.Copy
    Rows("18:18").Select
    Selection.Insert Shift:=xlDown
End Sub

I hope that is enough detail. Thanks for any help!

~AZ

 

[SkipVought]

You appear to have a 4 row, 3 column format. Whatever it is select that rane and assign a Range Name. Let's call it rSource.

Now to the sheet/cell that defines the TOP LEFT CELL of the target rang to be formatted: your ActiveCell.

Put this code in a procedure that you can call from a control or a keyboard shortcut for instance.

'
   rSource.copy
   ActiveCell.PasteSpecial xlPasteFormats

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[shmoes]

I'll be honest, I had no idea how to create a named range.. sooo 3hrs of goggling.. trying to separate the code from the individual so I could understand what I needed is crazy when you don't know what you're doing! (My novice is showing!! haha)

So I finally made your solution work once I figured out how to create a named range, but it would only copy the background colors and not the text, also it wouldn't insert it would only copy and paste.
I used a combination of your suggestion and one from the poster on the previous link and created a workable solution!!

Sub Macro3()
'
' Macro3 Macro
' Keyboard Shortcut: Ctrl+p
'
   Dim rSource As Range
   Set rSource = Range("A1:C5")
   rSource.Copy
   Selection.Insert Shift:=xlDown
End Sub

Works like a charm, I was going CTRL+P Crazy and got a weird disconnection from client error?!?@ but I closed the program and reopened it and it was all good haven't been able to reproduce.

Thank you for helping me along the correct path.. please let me know if you think that code will self destruct LOL!

~AZ

 

[SkipVought]

Sorry, my syntax was wrong, but it seems you found a way.

Sub Macro3()
'
' Macro3 Macro
' Keyboard Shortcut: Ctrl+p
'
   'The selection must be at the proper place
   Range("A1:C5").Copy
   Selection.Insert Shift:=xlDown
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]