Macro that work from another sheet when my worksheet is hidden

[Jeffreyxx01]

Hi

I need my macro to work from another sheet when my worksheet is hidden

Can anyone help and explain how to do it,

Sub Date_Change_Weekly()'
' Date_Change_Weekly Macro
'
Dim Sht As Worksheet


Set Sht = ActiveWorkbook.Sheets("Pipeline")


Sht.Cells.FormulaR1C1 = "=TODAY()"
    Range("BK1").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

 

[Fluff]

A few questions
Which sheet is hidden?
Which sheet is Range("BK1") on?
What are you trying to do with this line

Sht.Cells.FormulaR1C1 = "=TODAY()"

At the moment that will try (and probably fail) to put today's date into every single cell on sheet Pipeline

 

[njimack]

The below should work for you, but what exactly are you trying to do? This code will write a formula to every single cell on the sheet and then copy/paste value one of the cells.

Sub Date_Change_Weekly() 
' Date_Change_Weekly Macro
'


With Sheets("Pipeline")
    .Cells.Formula = "=TODAY()!"
    With .Range("BK1")
        .Formula = .Value
    End With
End With






End Sub

 

[Jeffreyxx01]

Hi

I tried to get the macro work on the sheet("Pipeline") with the formula =Today() in the cell BK1 and copy paste the date in value after it has adjusted on the day using a button on the sheet Macro

 

[jproffer]

That will put the date into every cell on the sheet. And I seriously doubt it will do it...it will most likely throw a memory error.

Anyhow, what are you trying to do?

BTW, for the date in one cell, you can just use:

Range("A1") = Date

or whatever range you want. Then you don't need to paste values. It puts the actual date (I.E. not a formula) into a range.

 

[Scott T]

Try

Sub Date_Change_Weekly()
' Date_Change_Weekly Macro
'

With Sheets("Pipeline")
  .Range("BK1") = Date
End With
End Sub

 

[Fluff]

How about

Sub Date_Change_Weekly() '
' Date_Change_Weekly Macro
'
   Sheets("Pipeline").Range("BK1").Value = Date
End Sub

 

[Jeffreyxx01]

Thanks guys,
both code are working,
Can you explain whats the way to get a macro working using a button from another worksheet that is hidden,
Thanks

 

[jproffer]

If the worksheet is hidden, you can't click the button so...

I mean, you can call that routine from anywhere you want by putting the name in it's own line of code, but you can't physically click a button that you can't see.

 

[Jeffreyxx01]

I meant,
I have a tab named Macros where I execute my routine, but the worksheet where the routine is executed is hidden,
I supposed I have to write as the guys did

Sheets("name worksheet")

if I am correct?