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

AliceCujo

New Member
Joined
Aug 4, 2009
Messages
2

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. :confused:
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

foverman

Board Regular
Joined
Sep 2, 2008
Messages
139
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:
Code:
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

New Member
Joined
Aug 4, 2009
Messages
2
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?

Code:
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
 
Last edited by a moderator:

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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:

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

Can be cut to one line:

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

And this code:

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

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

Hope that helps,
 
Last edited:

foverman

Board Regular
Joined
Sep 2, 2008
Messages
139
Try this
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,710
Messages
5,597,701
Members
414,164
Latest member
ARTW

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top