VBA?Macros : Re-create Row & Contents within on next open line?

[AliceCujo]

I need a method to automatically re-create a specific template row (A2:K2) when I click on the next empty row below(A3).

The template row is already perfect in all formatting, validations and check boxes. I can easily do a drag & drop and copy 100's of the same row, but it would make the page way too busy and confusing looking. I just want the macros to re-create everything from the template row when I click on the next open (A#) cell available. or recreate it when I click ctrl+N. Is this possible? I have been looking in my Excel book & can't figure it out. Please help.

 

[foverman]

It looks like you're wanting to copy the formatting, validation, and check boxes from one row to the next, but not the contents (data, constants, formulas?).

This code does the formatting and validation and could be set to run on Ctrl N:

Selection.Offset(-1, 0).EntireRow.Copy
Selection.EntireRow.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
Selection.EntireRow.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

however it doesn't do the check boxes. I don't see that those copy down with either drag and drop or outright cut and paste.

To confirm, you are currently copying the check boxes when you do the drag and drop? Or have to do those separately?

 

[AliceCujo]

I some how recorded a Macros and had it connected to Ctrl+N, It populates on line A3 and only on A3-AK, but I can't get it to populate in the next open A4. 5, 6, 7 etc cells. Drag & Drop does bring down the boxes and so does copy paste. I just want the VBA or Macros to basically populate my template in the designated cell with Ctrl N. Any suggestions?

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 8/4/2009 by pereali
'
' Keyboard Shortcut: Ctrl+n
'
Range("C2").Select
Selection.Copy
Range("C3").Select
ActiveSheet.Paste
Range("D2").Select
Application.CutCopyMode = False
Selection.Copy
Range("D3").Select
ActiveSheet.Paste
Range("E2").Select
ActiveSheet.Shapes("Group 1286").Select
Range("E3").Select
Application.CutCopyMode = False
Range("D3").Select
ActiveSheet.Shapes("Group 1286").Select
Selection.Copy
Range("E3").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=9
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A3").Select
End Sub

 

[Smitty]

Welcome to the Board!

Maybe something like this:

Range("A2:K2").Copy Cells(ActiveCell.Row, "A")

When called it will copy A2:K2 to column A of the activecell's row.

A note on recorded code is that it's generally inefficient. Where you see "Select" followed by "Selection" you can usually eliminate both statements and join the remaining code, so:

Range("C2").Select
Selection.Copy
Range("C3").Select
ActiveSheet.Paste

Can be cut to one line:

Range("C2").Copy Range("C3")

And this code:

ActiveWindow.SmallScroll ToRight:=9
ActiveWindow.ScrollColumn = 9

Is completely unnecessary, as it's screen navigation, so it can be deleted.

Hope that helps,

 

[foverman]

Try this

curRow = Selection.Row
myTarget = "A" & curRow & ":" & "K" & curRow

Range("A2:K2").Copy
Range(myTarget).PasteSpecial xlPasteAll
ActiveSheet.Shapes("Group 1286").Copy
Cells(curRow, 5).Select ' Column E of the current row
ActiveSheet.Paste
Cells(curRow, 1).Select ' Column A of the current row

The macro knows where to paste based on the row currently selected (the whole row or any cell on that row).

The Range("A2:K2").Copy ... xlPasteAll pastes everything but the check boxes.
The Shapes("Group 1286").Copy / ActiveSheet.Paste takes care of the check boxes.