Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 2 of 2

Thread: Reference Cell In Different Worksheet with Macro

  1. #1
    Join Date
    24th March 2011
    Posts
    12

    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

    VB:
    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!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,089

    Re: Reference Cell In Different Worksheet with Macro

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

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

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

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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 7
    Last Post: July 1st, 2008, 21:40
  2. Reference Cell For Worksheet Name
    By jthomps in forum EXCEL HELP
    Replies: 1
    Last Post: June 5th, 2008, 04:33
  3. Variable To Reference Worksheet In Macro
    By bob mcglynn in forum EXCEL HELP
    Replies: 3
    Last Post: April 15th, 2008, 22:32
  4. Reference Cell For Worksheet Name Macro Code
    By ano0014 in forum EXCEL HELP
    Replies: 4
    Last Post: April 12th, 2008, 00:17
  5. Replies: 5
    Last Post: September 22nd, 2004, 21:21

Bookmarks

Posting Permissions

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