So I have a worksheet where, on the "Data Entry" tab one will enter in data for a specified date, and then when a button containing the macro "Uptade_Archive" is pressed it updates another tab titled "Archive." The Archive Tab has a bunch of listed dates going down Column A starting with A3 being "Jan-17" , A4 is "Feb 17" , A5 is "Mar-17" , etc. My "Data Entry" sheet has the key date in cell A14. I want my code to take the value in cell A14, copy the data that is next to it ("A14:M14"), find that same date from A14 and find the row containing that date in the "Archive" tab, then paste the data in that row. My code currently looks as follows:
Sub Update_Archive()
Dim n1 As String
Dim n2 As String
'Capturing the date for the archive entry; resides in a hidden row in the Data Entry Sheet
n1 = Sheets("Data Entry").Range("A14").Value 'A14 is equal to B3. If B3 changes, A14 changes.
'n2 is the next data entry date needed
n2 = Sheets("Data Entry").Range("A14").Value + 1
Application.ScreenUpdating = False
'This action copies the hidden cells on the Data Entry sheet that contain the modified (kg->metric tons) data entered
Sheets("Data Entry").Range("A14:M14").Copy
'This block of code matches the data entry date with an archive data entry date
Sheets("Archive").Select
Columns("A:A").Select
Selection.Find(What:=n1, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
'pasting the copied entry data into the archive
ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.RefreshAll
'Clearing the data entry form
Sheets("Data Entry").Select
Range("B6:D8").Select
Selection.ClearContents
'Updating the data entry date needed
Sheets("Data Entry").Range("B3") = n2
Sheets("Data Entry").Range("A1").Select
ActiveWorkbook.Save
MsgBox ("Archive and Plots Updated.")
End Sub
Whenever I run through the code step-by-step, It stops at the Selection.Find() step and gives me "Run-time error '91': Object variable or With block variable not set"
I'm not sure what exactly is wrong here, n1 is dimmed as a String and is set to the value of A14.
Please help!
Bookmarks