Trouble referencing non-selected sheet

[mhaines]

I'm having problems altering the value of a single cell on a sheet that I am not currently working in. Here is my code.

Sub Button1_Click()
    Dim ws As Worksheet
    Dim hold As Integer
    
    
    Worksheets("L1-DIM").Copy _
    Before:=ActiveWorkbook.Sheets("L1-DIM")
    
    Set ws = ActiveSheet
    
    ws.Name = "L1-DIM-" & L1D
    
    Worksheets("Creation").Activate
    upcount = upcount + 1
    L1D = L1D + 1
    hold = upcount + 4
    Set Audit.Range("A1" & hold).Value = upcount
    
End Sub

 

[Scott T]

To work with a cell on a different sheet you use worksheets to specify witch sheet the cell is on.
For example no mater what sheet is the active sheet this code will set S7 on Sheet2 to 7. If you do not specify a worksheet then the range will be for the current worksheet.

Worksheets("Sheet2").Range("S7") = 7

 

[RoryA]

You only use Set with objects so it's just:

Audit.Range("A1" & hold).Value = upcount

assuming that Audit is the codename of the sheet, or a worksheet variable set and declared elsewhere?

 

[mhaines]

I am still receiving an error stating that an object is required. Audit is a sheet defined elsewhere yes.

 

[RoryA]

How is it defined and when is it assigned an object?

Also, that should actually read:

Audit.Range("A" & hold).Value = upcount