Excel VBA: input values in array of non contiguous cells

[Ellie3457]

Hi everyone,

I am new to VBA and trying to improve the way I write code because I currently use a combination of recording macro and some other things that I learnt and would like to be more efficient.

I have the following code but it is not working as intended.

    Sheets("Main").Select
    Range("B2:B2, G2:G2, H2:H2").Value = Array("Address", "City", "Country")
    Range("B2:B2, G2:G2, H2:H2").Select
    With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    Range("B:B, G:G, H:H").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

Instead of getting the value in the mentioned cells, the word address is repeated in the mentioned cells.

I was also wondering if there is a way to select multiple sheets at the same time and apply the same command to all of them.

The example below does not work

    Sheets("Main","Report","Data").Select
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

I would appreciate any help/advice.

Thanks in advance

Ellie

 

[bebo021999]

Is it?

Option Explicit
Sub test()
Dim sp As Variant, cell, k&
Dim ws As Worksheet
sp = Array("Address", "City", "Country")
For Each ws In Sheets ' loop thru every sheet
    k = 0
    For Each cell In Range("B2,G2,H2") ' loop thru each item in Array
        k = k + 1
        With cell
            .Value = sp(k - 1)
            .Font.Color = -16776961
            .Font.TintAndShade = 0
        End With
        With cell.EntireColumn.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    Next
k = 0
Next
End Sub

 

[Domenic]

I think you'll need to assign those values one cell at a time. Also, there's no need to do any selecting. Try the following . . .

    With Sheets("Main")
        .Range("B2").Value = "Address"
        .Range("G2").Value = "City"
        .Range("H2").Value = "Country"
        With .Range("B2, G2, H2").Font
            .Color = -16776961
            .TintAndShade = 0
        End With
        With Range("B:B, G:G, H:H").Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End With

 

[Domenic]

Sorry, I understood that there were two separate questions. If this is not the case, please disregard my post.

 

[mikerickson]

For the multiple sheet situation, you can construct a Sheets object. to access the .FillAcrossSheets method

Dim MySheets as Sheets

ThisWorkbook.Sheets("Sheet1").Range("A1").Value = "hello"

Set MySheets = ThisWorkbook.WorkSheets(Array("Sheet1", "Sheet3", "Sheet7")

MySheets.FillAcrossSheets Sheets("Sheet1").Range("A1")