Copying the same Macro to multiple rows

neild1973

New Member
Joined
Aug 6, 2010
Messages
7
Hi, Newbie here so hope this isn't a stupid question. I have created my first Macro which provides a tick box and when pressed places a "Y" in all the 5 cells to the left. I would now like to replicate this macro in the following 100 or so rows below but other than recording 100 macro's I am lost. Please help.
The code that the macro has produced is posted below.
Sub Macro1()
'
' Macro1 Macro
'
'
Range("H10").Select
ActiveCell.FormulaR1C1 = "y"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Times New Roman"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("G10").Select
ActiveCell.FormulaR1C1 = "y"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Times New Roman"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("F10").Select
ActiveCell.FormulaR1C1 = "y"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Times New Roman"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("E10").Select
ActiveCell.FormulaR1C1 = "y"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Times New Roman"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("D10").Select
ActiveCell.FormulaR1C1 = "y"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Times New Roman"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("C10").Select
End Sub
<!-- / message --><!-- BEGIN TEMPLATE: ad_showthread_firstpost_sig --><!-- END TEMPLATE: ad_showthread_firstpost_sig -->
<!-- controls -->
progress.gif
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,971
Office Version
  1. 365
Platform
  1. Windows
Welcome to the board!

I'm not sure if you're misunderstood what your code is doing, or if I've misread your explaination, but there is nothing in your macro to add a tickbox to the sheet, only to add the "y" characters to the 5 cells.

The code below will do the same as your recorded macro, but relative to the active cell on the sheet, for example, if you select F1 then run the code it will insert "y" into A1:E1, if you run it on L10 it will insert "y" into G10:K10, and so on.

Code:
Sub yyyyy()
With Range(ActiveCell.Offset(0, -5), ActiveCell.Offset(0, -1))
.Value = "y"
.Font.Name = "Times New Roman"
.Font.Size = 10
End With
End Sub

Hope that makes sense.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Or maybe

Code:
Sub Macro1()
'
' Macro1 Macro
'
'
With Range("D10").Resize(100, 5)
    .Value = "y"
    With .Characters(Start:=1, Length:=1).Font
        .Name = "Times New Roman"
        .FontStyle = "Regular"
        .Size = 10
    End With
End With
End Sub
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Hi, Most of your recorded Macro is redundant, you can achieve Most of what you want with the line
Code:
Range("D10").Resize(100, 5) = "Y"
I should try getting a book on Basic VBA. Like "Excel VBA programming for Dummies" by John Walkenback.
Regards Mick
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,971
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

VoG & MickG

Code:
With Range("D10").Resize(100, 5)

Would that not simply fill 500 cells at once? Maybe I'm wrong, but I was reading the requirement as 100 checkboxes, with rows filled as checked.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
VoG & MickG

Code:
With Range("D10").Resize(100, 5)
Would that not simply fill 500 cells at once? Maybe I'm wrong, but I was reading the requirement as 100 checkboxes, with rows filled as checked.

You may be right but I didn't see any code for adding checkboxes.
 

neild1973

New Member
Joined
Aug 6, 2010
Messages
7

ADVERTISEMENT

Hi thank you all for your comments, I don't think my original explanation has helped.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
What I am trying to achieve is as follows:-


<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
..............................................SRR.....PDR.....CDR.....TRR.....PRR.....ALL<o:p></o:p>
Bid Details.............................Y<o:p></o:p>
Bill of Materials.................................Y...........Y<o:p></o:p>
Cable Drawings.....................Y..........Y.......... Y.........Y...........Y.......... P

<o:p></o:p>
<o:p></o:p>
The spreadsheet is a Project review sheet and at the beginning of a project the PM will work their way down the sheet ticking which review if any the process/document is required by, now to try and speed thing up I thought if there was a macro button to the far right that said "all" if a process/document was required at all review this would auto fill, speeding the process of completing the form.

<o:p></o:p>
Now what I did was create a Form Control Button and assigned the original macro code posted which works well for that one button but to reproduce this for multiple rows (i.e. 100) would be very time consuming.

So was hoping for a way to copy the Form Control Button down all 100 rows and then have one peice of code that would reproduce the ticks in the 5 cells for that row only?
<o:p></o:p>
Many Thanks Again
 
Last edited:

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Why not follow Jason's suggestion and have one button to act on the active row

Code:
Sub Macro1()
'
' Macro1 Macro
'
'
With Range("D" & ActiveCell.Row).Resize(, 5)
    .Value = "y"
    With .Characters(Start:=1, Length:=1).Font
        .Name = "Times New Roman"
        .FontStyle = "Regular"
        .Size = 10
    End With
End With
End Sub
 

neild1973

New Member
Joined
Aug 6, 2010
Messages
7
Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Unfortunately Jason's suggestion just ticks every cell, 5 across, and for 100 rows where as the user needs to be able to choose this for each row as per my last post, it may be that on one row they only need to select one cell and if so they can just enter this manually but mybe on the next row they might need to tivk all five cells so hitting the Form Control button will populate all five cell with a tick and then the next row may be left blank completely, it will differ for each project?<o:p></o:p>
<o:p> </o:p>
Many Thanks<o:p></o:p>
Neil <o:p></o:p>
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
This will work with the selected cells and they don't need to be contiguous

Code:
Sub Macro1()
'
' Macro1 Macro
'
'
With Selection
    .Value = "y"
    With .Characters(Start:=1, Length:=1).Font
        .Name = "Times New Roman"
        .FontStyle = "Regular"
        .Size = 10
    End With
End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,143
Messages
5,599,975
Members
414,354
Latest member
Flaxarn

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