VBA Timestamp for non-active worksheet?

[sky5ky]

Hello, im currently trying to timestamp cells in a non-active worksheet, my "sheet1" loads a Userform box with a command button to load data into the next sheet...i need to timestamp the cells into the sheet the data is being dumped in, although it looks like the command button needs a named table/range to dump both data values into, any help?

 

[Deek6t8]

does using something like sheet1.range("A1").value = now() not work? I have used this from form code many times in the past successfully

 

[sky5ky]

does using something like sheet1.range("A1").value = now() not work? I have used this from form code many times in the past successfully

it might be messy but this is what im using atm
Private Sub Worksheet_Change(ByVal Target As Range)
'Update 20140722
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("B:B"), Target)
xOffsetColumn = 1
Dim WorkRng1 As Range
Dim Rng1 As Range
Dim xOffsetColumn1 As Integer
Set WorkRng1 = Intersect(Application.ActiveSheet.Range("D:D"), Target)
xOffsetColumn1 = 2
If Not WorkRng Is Nothing Then
Application.EnableEvents = False
For Each Rng In WorkRng
If Not VBA.IsEmpty(Rng.Value) Then
Rng.Offset(0, xOffsetColumn).Value = Now
Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
Else
Rng.Offset(0, xOffsetColumn).ClearContents
End If
Next
Application.EnableEvents = True
End If

If Not WorkRng1 Is Nothing Then
Application.EnableEvents = False
For Each Rng1 In WorkRng1
If Not VBA.IsEmpty(Rng1.Value) Then
Rng1.Offset(0, xOffsetColumn1).Value = Now
Rng1.Offset(0, xOffsetColumn1).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
Else
Rng1.Offset(0, xOffsetColumn1).ClearContents
End If
Next
Application.EnableEvents = True
End If
End Sub

the text highlighted in orange is whats throwing my debug, im dumping the data into a named table/range on an inactive page and gives me an error, but when i manually type into that table/range it timestamps as it should

 

[sky5ky]

does using something like sheet1.range("A1").value = now() not work? I have used this from form code many times in the past successfully

so heres what it looks like, it goes from the user form to the inactive sheet

 

[sky5ky]

it might be messy but this is what im using atm
Private Sub Worksheet_Change(ByVal Target As Range)
'Update 20140722
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("B:B"), Target)
xOffsetColumn = 1
Dim WorkRng1 As Range
Dim Rng1 As Range
Dim xOffsetColumn1 As Integer
Set WorkRng1 = Intersect(Application.ActiveSheet.Range("D:D"), Target)
xOffsetColumn1 = 2
If Not WorkRng Is Nothing Then
Application.EnableEvents = False
For Each Rng In WorkRng
If Not VBA.IsEmpty(Rng.Value) Then
Rng.Offset(0, xOffsetColumn).Value = Now
Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
Else
Rng.Offset(0, xOffsetColumn).ClearContents
End If
Next
Application.EnableEvents = True
End If

If Not WorkRng1 Is Nothing Then
Application.EnableEvents = False
For Each Rng1 In WorkRng1
If Not VBA.IsEmpty(Rng1.Value) Then
Rng1.Offset(0, xOffsetColumn1).Value = Now
Rng1.Offset(0, xOffsetColumn1).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
Else
Rng1.Offset(0, xOffsetColumn1).ClearContents
End If
Next
Application.EnableEvents = True
End If
End Sub

the text highlighted in orange is whats throwing my debug, im dumping the data into a named table/range on an inactive page and gives me an error, but when i manually type into that table/range it timestamps as it should

My fault i highlighted the wrong line thats the issue.....its actually
Set WorkRng = Intersect(Application.ActiveSheet.Range("B:B"), Target)