Results 1 to 7 of 7

Thread: VBA to insert values into multiple sheets, with range variable

  1. #1
    New Member
    Join Date
    Nov 2018
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to insert values into multiple sheets, with range variable

    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.

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,221
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    8 Thread(s)

    Default Re: VBA to insert values into multiple sheets, with range variable

    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
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    4,513
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    12 Thread(s)

    Default Re: VBA to insert values into multiple sheets, with range variable

    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
    Regards Dante Amor

  4. #4
    New Member
    Join Date
    Nov 2018
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to insert values into multiple sheets, with range variable

    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

  5. #5
    New Member
    Join Date
    Nov 2018
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to insert values into multiple sheets, with range variable

    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

  6. #6
    New Member
    Join Date
    Nov 2018
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to insert values into multiple sheets, with range variable

    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

  7. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    4,513
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    12 Thread(s)

    Default Re: VBA to insert values into multiple sheets, with range variable

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •