VBA to insert values into multiple sheets, with range variable

glynn1969

Board Regular
Joined
Nov 24, 2018
Messages
52
Office Version
2016
Platform
Windows
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.
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,851
Office Version
2010
Platform
Windows
This assumes your 8 sheets are arranged in ascending order (Sheet1,Sheet2,...,Sheet8).
Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
11,805
Office Version
2007
Platform
Windows
if you want only sheets 2 to 5.
And the value of the column is on sheet1, cell A1


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

Board Regular
Joined
Nov 24, 2018
Messages
52
Office Version
2016
Platform
Windows
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

Board Regular
Joined
Nov 24, 2018
Messages
52
Office Version
2016
Platform
Windows
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

Board Regular
Joined
Nov 24, 2018
Messages
52
Office Version
2016
Platform
Windows
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-known Member
Joined
Dec 3, 2018
Messages
11,805
Office Version
2007
Platform
Windows
Well, it's a pleasure that already works for you, that's the idea that you also know the code to make changes.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,582
Messages
5,469,514
Members
406,656
Latest member
Kriscrawford76

This Week's Hot Topics

Top