Macro Problem

[Angel_24477616]

I would be grateful if somebody could help me to create a routine
within a macro that i have recorded. As you can see from the macro
below, a sheet within the existing workbook is copied, a hyperlink on
the original worksheet opens a template that the copied date is pasted
into. Rather handily and not by my design, it opens a copy of the
template without affecting the original. which is not the case if I
include instructions within the macro to open the template rather than
using the hyperlink.

My problem is that once i have saved/printed the document that i have
just created, and return to my orginal workbook, the cursor is over
the hyperlink. If I am not careful, I click the hyperlink which then
obviously opens the orignal template. How do I incorporate an
instruction in my macro to move the cursor to a specified cell within
this worksheet after it has hit the hyperlink and then returns to my
newly created workbook?

The purpose of all this is to avoid confusing other users of this
workbook if they inadvertantly click on the hyperlink itself.

Sub Letterv6()
'
' Letterv6 Macro
' Macro recorded 27.02.2007 by an Angel
'

'
Sheets(".").Select
Cells.Select
Selection.Copy
Sheets("Sales Tariff Calculator").Select
Range("B3").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A1:K54").Select
Application.CutCopyMode = False
ActiveSheet.PageSetup.PrintArea = "$A$1:$K$70"
Range("A1").Select
End Sub

 

[Joel]

I made some changes. This works. Avoid using the .SELECT because it moves
the highligted cells.


Sub Letterv6()
'
' Letterv6 Macro
' Macro recorded 27.02.2007 by an Angel
'
Set Oldsheet = ActiveSheet
Cells.Copy
Sheets("Sales Tariff Calculator").Activate
Range("B3").Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, skipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
skipBlanks:=False, Transpose:=False
Range("A1:K54").Select
Application.CutCopyMode = False
ActiveSheet.PageSetup.PrintArea = "$A$1:$K$70"
Oldsheet.Activate
Range("A1").Select
End Sub

 

[Angel_24477616]

I made some changes. This works. Avoid using the .SELECT because it moves
the highligted cells.

Sub Letterv6()
'
' Letterv6 Macro
' Macro recorded 27.02.2007 by an Angel
'
Set Oldsheet = ActiveSheet
Cells.Copy
Sheets("Sales Tariff Calculator").Activate
Range("B3").Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, skipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
skipBlanks:=False, Transpose:=False
Range("A1:K54").Select
Application.CutCopyMode = False
ActiveSheet.PageSetup.PrintArea = "$A$1:$K$70"
Oldsheet.Activate
Range("A1").Select
End Sub









- Show quoted text -

Thank you for your assistance Joel. The problem with this solution is
that the sheet "Sales Tariff Calculator" is now copied instead of the
sheet "." The Sales sheet is used for data entry while the "." sheet
takes selected data from the sales sheet and puts it in a nic letter
format. The hyperlink on the sales sheet then opens a template for
the data and formats from the "." sheet to be pasted into. The
desired result being that the user ends up looking at his copied data
within the new workbook that he can then either save, print email
etc. When she/he returns to the workbook with the Sales Tariff
Calculator, the cursor should be positioned in a cell other than the
one with the hyperlink in it.

Thanks & regards,
Angel