Hi Janet,
suggestion was alternative approach you could try to reduce repeating code & was based on code your shared on forum. Yes you would need to expand it to accommodate a bigger requirement but without fully knowing what the requirement is a little difficult to assist. If you could place sample copy of your workbook in a dropbox this would help.
Dave
Hi Dave
Original post was just a sample to see if anyone could answer what I was doing wrong with on error coding. I basically have several sheets, each with different searches in different columns with hyperlinks being populated in varying offset cells if date is found - descriptions for hyperlinks changes throughout too (some are b/f, some are c/f - some are just comments that don't mention the sheet name!)
I don't know how to add workbook in a dropbox (only posted on here once before!) but my full original attempt is below:!!
Workbooks(NewWB).Sheets("Cash Report").Activate
ActiveSheet.Range("E:E").Select
On Error GoTo H2
Selection.Find(What:="Inputs declared from Cash report", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Offset(0, 3).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'Purchase Totals'!A1", TextToDisplay:="c/f to Purchase Totals"
Selection.Font.Italic = True
GoTo H2
H2:
ActiveSheet.Range("E:E").Select
On Error GoTo H3
Selection.Find(What:="Output VAT", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Offset(0, 3).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'Income Totals'!A1", TextToDisplay:="c/f to Income Totals"
Selection.Font.Italic = True
GoTo H3
H3:
Workbooks(NewWB).Sheets("Sales Report").Activate
ActiveSheet.Range("F:F").Select
On Error GoTo H4
Selection.Find(What:="Credit Memo", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Offset(0, 3).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'Income Totals'!A1", TextToDisplay:="c/f to Income Totals"
Selection.Font.Italic = True
GoTo H4
H4:
ActiveSheet.Range("F:F").Select
On Error GoTo H5
Selection.Find(What:="Sales Invoice", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Offset(0, 3).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'Income Totals'!A1", TextToDisplay:="c/f to Income Totals"
Selection.Font.Italic = True
GoTo H5
H5:
ActiveSheet.Range("F:F").Select
On Error GoTo H6
Selection.Find(What:="Debit Memo", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Offset(0, 3).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'Income Totals'!A1", TextToDisplay:="c/f to Income Totals"
Selection.Font.Italic = True
GoTo H6
H6:
Workbooks(NewWB).Sheets("Income Totals").Activate
ActiveSheet.Range("A:A").Select
On Error GoTo H7
Selection.Find(What:="Total Outputs declared from Cash Report", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Offset(0, 3).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'Cash Report'!G5", TextToDisplay:="b/f from Cash Report"
Selection.Font.Italic = True
GoTo H7
H7:
ActiveSheet.Range("A:A").Select
On Error GoTo H8
Selection.Find(What:="Total Output VAT Declared From Sales Report", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Offset(0, 3).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'Sales Report'!H8", TextToDisplay:="b/f from Sales Report"
Selection.Font.Italic = True
GoTo H8
H8:
ActiveSheet.Range("A:A").Select
On Error GoTo H9
Selection.Find(What:="Total Back End Adjustments", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Offset(0, 3).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'Income Totals'!D30", TextToDisplay:="b/f from below. Items added from requests, prior months or Journal posting"
Selection.Font.Italic = True
GoTo H9
H9:
ActiveSheet.Range("A:A").Select
On Error GoTo H10
Selection.Find(What:="Total RC/AQ payable c/f box 1 & 2", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Offset(0, 3).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'Purchase Reports'!H5", TextToDisplay:="b/f from Purchase Report"
Selection.Font.Italic = True
GoTo H10
H10:
Workbooks(NewWB).Sheets("Purchase Report").Activate
ActiveSheet.Range("F:F").Select
On Error GoTo H11
Selection.Find(What:="Total RC/AQ payable c/f box 1 & 2", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Offset(0, 4).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'Purchase Totals'!D25", TextToDisplay:="c/f to Income Totals to Declare as Output Tax"
Selection.Font.Italic = True
GoTo H11
H11:
ActiveSheet.Range("F:F").Select
On Error GoTo H12
Selection.Find(What:="Total COS input tax (excl RC/AQ)", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Offset(0, 4).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'Purchase Totals'!D5", TextToDisplay:="c/f to Purchase Totals"
Selection.Font.Italic = True
GoTo H12
H12:
ActiveSheet.Range("F:F").Select
On Error GoTo H13
Selection.Find(What:="Total business (incl import tax) input tax", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Offset(0, 4).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'Purchase Totals'!D6", TextToDisplay:="c/f to Purchase Totals"
Selection.Font.Italic = True
GoTo H13
H13:
Workbooks(NewWB).Sheets("Purchase Totals").Activate
ActiveSheet.Range("B:B").Select
On Error GoTo H14
Selection.Find(What:="Total COS input tax (excl RC/AQ)", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Offset(0, 3).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'Purchase Report'!I6", TextToDisplay:="b/f from Purchase Report"
Selection.Font.Italic = True
GoTo H14
H14:
ActiveSheet.Range("B:B").Select
On Error GoTo H15
Selection.Find(What:="Total business (incl import tax) input tax", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Offset(0, 3).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'Purchase Report'!I7", TextToDisplay:="b/f from Purchase Report"
Selection.Font.Italic = True
GoTo H15
H15:
ActiveSheet.Range("B:B").Select
On Error GoTo H16
Selection.Find(What:="Inputs declared from Cash report", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Offset(0, 3).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'Cash Report'!G5", TextToDisplay:="b/f from Cash Report"
Selection.Font.Italic = True
GoTo H16
H16:
ActiveSheet.Range("B:B").Select
On Error GoTo H17
Selection.Find(What:="Business related RC/AQ reclaim", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Offset(0, 3).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'Purchase Report'!I5", TextToDisplay:="b/f from Purchase Report"
Selection.Font.Italic = True
GoTo H17
H17:
ActiveSheet.Range("B:B").Select
On Error GoTo H18
Selection.Find(What:="Total Back end adjustments", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Offset(0, 3).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'Purchase Totals'!D14", TextToDisplay:="b/f from below. Items added from requests, prior months or Journal posting"
Selection.Font.Italic = True