Copy paste value only

[fireslguk]

I have 5 sheets

Sheet 1 range A1 contains =now()

I want on pressing a button to paste the value only of this to B1 on all other 4 sheets which are protected

I then want the sheets to re protect

 

[plettieri]

hello fireslguk,

without via code, you can try this...

Get the value from Sheet1 cell A1
Unprotect the other 4 sheets
Paste the value to cell B1 on each of the other 4 sheets
Re-protect the 4 sheets

a bit more effort if a macro is drafted to handle this

hope this helps
plettieri

 

[fireslguk]

hello fireslguk,

without via code, you can try this...

Get the value from Sheet1 cell A1
Unprotect the other 4 sheets
Paste the value to cell B1 on each of the other 4 sheets
Re-protect the 4 sheets

a bit more effort if a macro is drafted to handle this

hope this helps
plettieri

Thankyou need it to be code so does all in one hit

 

[Alex Blakenburg]

See if this is close to what you are after:

Sub DateTimeStampSheets()

    Dim thisWS As Worksheet
    Dim ws As Worksheet
    Dim dt As Date
    
    Set thisWS = ActiveSheet
    dt = thisWS.Range("A1").Value2
    
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> thisWS.Name  Then
            With ws
                .Unprotect Password:=123
                .Range("B1").Value2 = dt
                .Protect Password:=123, DrawingObjects:=True, Contents:=True, Scenarios:=True
            End With
        End If
    Next ws
End Sub

 

[fireslguk]

See if this is close to what you are after:

Sub DateTimeStampSheets()

    Dim thisWS As Worksheet
    Dim ws As Worksheet
    Dim dt As Date
   
    Set thisWS = ActiveSheet
    dt = thisWS.Range("A1").Value2
   
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> thisWS.Name  Then
            With ws
                .Unprotect Password:=123
                .Range("B1").Value2 = dt
                .Protect Password:=123, DrawingObjects:=True, Contents:=True, Scenarios:=True
            End With
        End If
    Next ws
End Sub

Some manipulation required to the code above my head

I have 10 sheets

Only want sheets “s1”, “s2”, “s3”, “s4”, “s5” b1 to receive this value from sheet “Main”

 

[Alex Blakenburg]

Try:

Sub DateTimeStampSheets()

    Dim thisWS As Worksheet
    Dim ws As Worksheet
    Dim dt As Date
  
    Set thisWS = Worksheets("Main")
    dt = thisWS.Range("A1").Value2
  
    For Each ws In ActiveWorkbook.Worksheets
        Select Case ws.Name
      
            Case "s1", "s2", "s3", "s4", "s5"
                With ws
                    .Unprotect Password:=123
                    .Range("B1").Value2 = dt
                    .Protect Password:=123, DrawingObjects:=True, Contents:=True, Scenarios:=True
                End With
            End Select
          
    Next ws
End Sub

 

[Alex Blakenburg]

Edited my previous post, accidentally deleted the unprotect line

 

[fireslguk]

Edited my previous post, accidentally deleted the unprotect line

That done the job until I realised one sheet “dashboard” needs this value on a different cell “O5”

 

[Alex Blakenburg]

Try:

Sub DateTimeStampSheets()

    Dim thisWS As Worksheet
    Dim ws As Worksheet
    Dim dt As Date
    
    Set thisWS = Worksheets("Main")
    dt = thisWS.Range("A1").Value2
    
    For Each ws In ActiveWorkbook.Worksheets
        Select Case ws.Name
        
            Case "s1", "s2", "s3", "s4", "s5"
                With ws
                    .Unprotect Password:=123
                    .Range("B1").Value2 = dt
                    .Protect Password:=123, DrawingObjects:=True, Contents:=True, Scenarios:=True
                End With
            Case "dashboard"
                    .Unprotect Password:=123
                    .Range("O5").Value2 = dt
                    .Protect Password:=123, DrawingObjects:=True, Contents:=True, Scenarios:=True
            End Select
            
    Next ws
End Sub