VBA to insert values into multiple sheets, with range variable

[glynn1969]

Hello

I have a workbook with 8 sheets (sheet1 to sheet8)

in cell A1 on sheet I have a variable number eg 3

i want this number to be used as a column identifier eg 3 = column C, 4 = column D etc

So i then want select sheets 2 to 5 and select range C8 to M8 and enter the value "0" in that range and also for range C12 to M12 and enter the Value "0" also (where C in the above range is determined by cell A1 value above).

Any Help much appreciated.

 

[JoeMo]

This assumes your 8 sheets are arranged in ascending order (Sheet1,Sheet2,...,Sheet8).

Sub glynn()
Dim i As Long, Col As String
For i = 2 To 5
    With Sheets(i)
        Col = Chr(64 + .Range("A1").Value)
        Intersect(.Range(Col & ":M"), .Rows(8)).Value = 0
        Intersect(.Range(Col & ":M"), .Rows(12)).Value = 0
    End With
Next i
End Sub

 

[DanteAmor]

if you want only sheets 2 to 5.
And the value of the column is on sheet1, cell A1

Sub Macro11()
    col = Sheets("Sheet1").Range("A1").Value
    shs = Array("Sheet2", "Sheet3", "Sheet4", "Sheet5")
    For Each sh In Array("Sheet2", "Sheet3", "Sheet4", "Sheet5")
        Sheets(sh).Range(Sheets(sh).Cells(8, col), Sheets(sh).Cells(8, "M")).Value = 0
        Sheets(sh).Range(Sheets(sh).Cells(12, col), Sheets(sh).Cells(12, "M")).Value = 0
    Next
End Sub

 

[glynn1969]

Sorry - getting an error on Col and SHS - do i need to set these as DIM, if so do you know what?

Thank you - very early on my macro learning

 

[glynn1969]

Hi Joe - thats great, just one thing - this requires me to inpu a value into A1 on each sheet as opposed to it just being on A1 on sheet 1 and applied that across all sheets.

Any help on that .
Thank you

 

[glynn1969]

Thanks for setting me in right direction - sorted now, cheers


Sub glynn()
Dim i As Long
Dim Col As Long

Sheets("Sheet1").Select
Col = Range("A1").Value
For i = 2 To 5
With Sheets(i).Select
Range(Cells(8, Col), Cells(8, "M")).Value = 0
Range(Cells(12, Col), Cells(12, "M")).Value = 0

End With
Next i
End Sub

 

[DanteAmor]

Well, it's a pleasure that already works for you, that's the idea that you also know the code to make changes.