Copying the same Macro to multiple rows

[neild1973]

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 -->

 

[jasonb75]

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.

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]

Or maybe

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]

Hi, Most of your recorded Macro is redundant, you can achieve Most of what you want with the line

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]

VoG & MickG

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]

VoG & MickG

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]

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


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

<o></o>
<o></o>
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></o>
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></o>
Many Thanks Again

 

[VoG]

Why not follow Jason's suggestion and have one button to act on the active row

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]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
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></o>
<o> </o>
Many Thanks<o></o>
Neil <o></o>

 

[VoG]

This will work with the selected cells and they don't need to be contiguous

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