Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Reference Cell In Different Worksheet with Macro

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Reference Cell In Different Worksheet with Macro

    Alright so what I'm trying to do is standardize a workbook. I want to reference a cell in sheet 21. The way the formula is currently set up, it looks at cell A100 in the current active sheet. I want the macro to reference cell C3 in sheet 21 or my input sheet.

    Here's the code

    Code:
    Sub CompanyHeaderChangeHorizontalSheet()
    ' inserts the same header/footer in all worksheets
    Dim ws As Worksheet
        Application.ScreenUpdating = False
        For Each ws In ActiveWorkbook.Worksheets
            Application.StatusBar = "Changing header/footer in " & ws.Name
            With ws.PageSetup
                .CenterHeader = _
                    "&""Arial,Regular""&18&K000080                                                                                         &U&K000080" & Range("a100").Value
            End With
        Next ws
        Set ws = Nothing
        Application.StatusBar = False
    End Sub
    Thanks for your help!

  • #2
    Re: Reference Cell In Different Worksheet with Macro

    Change
    Code:
    Range("a100").Value
    To

    Code:
    worksheets("Sheet21").Range("C3").Value
    However, I would opt to use the [URL="http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm"]sheet code name[/URL]rather than worksheet name

    Code:
    Sheet21.Range("C3").Value
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

    Comment

    Trending

    Collapse

    There are no results that meet this criteria.

    Working...
    X