CODEWITHSUNDEEP
excelvba
Jose Montoya
Jose Montoya

Reputation: 167

How to write identical information in a specific cell for all sheets?

I have an Excel file with around 50 sheets where I've inserted a row at the beginning of the data. Now I want to write the variables names. For example: In cell A1 I want to write "code" , in B1 I wanna write "price", and so on till F1.

I want to do this simultaneously for all the Excel sheets.

I tried with this code:

Sub mycode()

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    Range("A1") = "code"
    Range("B1") = "denom"
    Range("C1") = "location"
    Range("D1") = "area_m2"
    Range("E1") = "price_$m2"
    Range("F1") = "zoning"
Next

End Sub

The problem is that I get only the variables names on my active sheet. I want the names for all the sheets of my workbook.

Could you please suggest some modifications for doing this?

Thanks in advance!

Upvotes: 1

Views: 1184

Answers (4)

T.M.
T.M.

Reputation: 9948

Alternative procedure

I'd like to demonstrate an alternative to @BigBen 's valid answer using the FillAcrossSheets method:

Sub FillHeaders(rng As Range, arr)
    rng = arr: Sheets.FillAcrossSheets rng
End Sub

Example call

FillHeaders Sheet1.Range("A1:F1"), Array("code", "price", "foo", "bar", "test2", "test3")

Clarifying notes to the Sheets.FillAcrossSheets method //--- Edit as of 03/13 2024

  • a) The Sheets property of the Workbook object without any further indications returns a collection of all the sheets in the specified or active workbook: Sheets.FillAcrossSheets rng
  • b) One can limit the collection to fewer sheets by subreferencing an array of sheet names (or index numbers): Sheets(SheetsArr).FillAcrossSheets rng, where SheetsArr might comprise e.g. SheetsArray = Split("Sheet1 Sheet3 Sheet4") or even ..= Array(1,3,4). - In this case it is important to include the sheet containing the base range rngto avoid a runtime error 1004.
  • c) Surprisingly one may even pass an empty SheetsArray: Sheets(Array()).FillAcrossSheets rng with the same result as Sheets.FillAcrossSheets rng alone.
  • d) The Sheets.FillAcrossSheets method comprises a further argument Type which can be used for the formatting purposes discribed below.

VBA language reference

Syntax: {Sheets object}.FillAcrossSheets Range[, Type]

Name (Type arg.) Value Description
xlFillWithAll -4104 Copy contents and formats.
xlFillWithContents 2 Copy contents only.
xlFillWithFormats -4122 Copy formats only.

Upvotes: 8

Jose Montoya
Jose Montoya

Reputation: 167

I find the solution. This is it:

 Sub mycode()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Range("A1:F1").Value = Array("code", "denom", "location", "area_m2", "price_$m2", "zoning_use")
Next ws

End Sub

I hope it helps

Upvotes: 2

BigBen
BigBen

Reputation: 50008

Only answering because this piqued my curiosity:

Sub Test()
    Sheets.Select
    Range("A1:F1").Select
    Selection.Value = Array("code", "price", "foo", "bar", "test2", "test3")
End Sub

Note that Selecting is usually discouraged, but seemed to be necessary (though isn't) in this case to avoid looping and write the values to all the sheets at once.

Upvotes: 4

Vityata
Vityata

Reputation: 43585

I do not see anything wrong with the looping, as far as it is not activating the sheet, it is really fast and not visible:

Sub TestCode()

    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        With ws
            .Range("A1") = "code"
            .Range("B1") = "denom"
            .Range("C1") = "location"
            .Range("D1") = "area_m2"
            .Range("E1") = "price_$m2"
            .Range("F1") = "zoning"
        End With
    Next

End Sub

Upvotes: 2

Related Questions