Hello, thanks in advance to anyone who can assist me. I have a spreadsheet that gets updated every week. Each week a new sheet is created and formatted by pulling data from a database, and then running macros to format the data. After that I would like to add a macro that will pull data from "last weeks" spreadsheet. The macro I have searches for a number, (in this case, an invoice #), I would like it to cut and paste relative data, IF it finds the same number in the previous worksheet. This is the code I have:
Sub Update_Comments()
'This macro performs a search and copy for updating a new spreadsheet with previously entered comments.
Dim counter As Integer
Dim Findcell, stringtofind As String
Dim dummy As Integer
newshtname = Application.InputBox(Prompt:= _
"Please enter this weeks worksheet name", Type:=2)
oldshtname = Application.InputBox(Prompt:= _
"Please enter the previous weeks worksheet name", Type:=2)
For counter = 7 To 250
Cells(counter, 4).Select
Selection.Copy
stringtofind = Selection
Workbooks.Open Filename:=(oldshtname)
Workbooks(oldshtname).Activate
Set Findcell = Cells.Find(what:=stringtofind)
If Findcell Is Nothing Then
dummy = 1
Else
Findcell.Select
ActiveCell.Offset(rowOffset:=0, columnOffset:=4).Activate
Selection.Copy
Workbooks(newshtname).Activate
Cells(counter, .Select
ActiveSheet.Paste
End If
Next counter
End Sub
This works half way. It will copy the correct data if it finds the invoice # on the previous sheet, however, if it runs into the situation where it does not find it, it handles the error and continues, but then copies data not
relevant to the current invoice number being looked for.
Any help is appreciated, I can't seem to write the correct code to handle not finding the invoice #....
Sub Update_Comments()
'This macro performs a search and copy for updating a new spreadsheet with previously entered comments.
Dim counter As Integer
Dim Findcell, stringtofind As String
Dim dummy As Integer
newshtname = Application.InputBox(Prompt:= _
"Please enter this weeks worksheet name", Type:=2)
oldshtname = Application.InputBox(Prompt:= _
"Please enter the previous weeks worksheet name", Type:=2)
For counter = 7 To 250
Cells(counter, 4).Select
Selection.Copy
stringtofind = Selection
Workbooks.Open Filename:=(oldshtname)
Workbooks(oldshtname).Activate
Set Findcell = Cells.Find(what:=stringtofind)
If Findcell Is Nothing Then
dummy = 1
Else
Findcell.Select
ActiveCell.Offset(rowOffset:=0, columnOffset:=4).Activate
Selection.Copy
Workbooks(newshtname).Activate
Cells(counter, .Select
ActiveSheet.Paste
End If
Next counter
End Sub
This works half way. It will copy the correct data if it finds the invoice # on the previous sheet, however, if it runs into the situation where it does not find it, it handles the error and continues, but then copies data not
relevant to the current invoice number being looked for.
Any help is appreciated, I can't seem to write the correct code to handle not finding the invoice #....