Archiving a cell value from one worksheet to another with timestamp

J7House1984

New Member
Joined
Oct 30, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
I am working on making creating an archive of UTMs that have been created using a simple form. I am having issues with the pasting portion. My VBA code will insert a line and then insert the timestamp into column B in the "UTM Log" worksheet as required, however it is not capturing the UTM in the "UTM Template" worksheet. I am receiving a blank in cell "A2" of the "UTM Log" worksheet.
VBA Code:
Sub Archiving()
    Dim wsFrom As Worksheet: Set wsFrom = Sheets("UTM Template")
    Dim wsTo As Worksheet: Set wsTo = Sheets("UTM Log")
    Dim copyRng As Range
    Dim TimeStamp As String

    TimeStamp = Format(CStr(Now), "mm-dd-yyyy hh:mm") 'format the timestampt

    Set copyRng = wsFrom.Range("B27") 'set your copy range

    wsTo.Range("A2:B" & copyRng.Rows.Count + 1).Insert Shift:=xlDown 'insert rows into sheet before pasting data

    copyRng.Copy
wsTo.Range("A2").PasteSpecial xlPasteValues 'paste data into newly inserted empty rows

    wsTo.Range("B2").Value = TimeStamp 'add timestampt to copied range
End Sub

I know I have not done something correctly but I can't figure out exactly what it is. I have added screenshots as reference. Please let me know if you have any questions and I will try to answer them the best I can.

Template: B27 is the cell I want to copy and paste
UTM Template scrnshot.png


Log (Archiving worksheet) Column A is where I want the value of UTM Template cell B27 to go with the timestamp.
UTM Log.png
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,938
Office Version
  1. 365
Platform
  1. Windows
Judging by your image B27 is blank, should it be C27?
 
Solution

J7House1984

New Member
Joined
Oct 30, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Judging by your image B27 is blank, should it be C27?
Oh man I feel like a complete moron. I just changed the B27 and it work perfectly. Man, I was so close. Thank you for catching that for me.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,938
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,768
Messages
5,766,370
Members
425,350
Latest member
procha

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top