Sheet Selection

[Lance]

Hello all,

I'm pretty new to all this, so go easy

I'm trying to write a macro that will allow me to not have to hardcode the
sheet name into the program.

Here is my code:

Sub PasteLink()

'start on Sheet 1R
Dim count As Integer
count = 0
Application.Goto Sheets("Sheet 1E").Range(Selection.Address)
Selection.Copy

If Selection.Font.ColorIndex = 3 Then
count = 1
End If

Application.Goto Sheets("Sheet 1R").Range(Selection.Address)
ActiveSheet.Paste Link:=True

With Selection.Font
.Name = "Arial"
.Size = 8
.Bold = True

If count = 1 Then
.ColorIndex = 3 'change font to red
Else: .ColorIndex = 0 'change font to black
End If

End With
End Sub

You see how I have Sheet 1E and Sheet 1R? I would like to make it so that
those are generic, and it takes the active sheet's selection then goes to the
sheet right before it, selects the same range, copies it, goes back to the
original sheet, pastes link and formats cell. The current order of sheets
are: Sheet1 = "Sheet 1E"; Sheet2 = "Sheet 1R" (there are more sheets in the
workbook titled in the same fasion: Sheet 2E, Sheet 2R, Sheet 3E, Sheet 3R).
Sheet 1E and 1R are carbon copies of each other.

Any help is greatly appreciated. Also, if you could add comments so I can
learn what exactly the coding means, it would be very helpful.

Thanks,

Lance

 

[Dave Peterson]

Did you really want to apply the formatting yourself or did you want to copy the
format from the previous sheet?

I'm guessing that you wanted the format from the previous sheet:

Option Explicit
Sub myPasteLink()

Dim ActSheet As Worksheet
Dim PrevSheet As Worksheet
Dim iCtr As Long

Set ActSheet = ActiveSheet

Set PrevSheet = Nothing
For iCtr = ActSheet.Index - 1 To 1 Step -1
If TypeName(Sheets(iCtr)) = "Worksheet" Then
Set PrevSheet = Sheets(iCtr)
Exit For
End If
Next iCtr

If PrevSheet Is Nothing Then
MsgBox "No previous sheet!"
Exit Sub
End If

PrevSheet.Range(Selection.Address).Copy
ActSheet.Paste link:=True
Selection.PasteSpecial Paste:=xlPasteFormats

End Sub

 

[Lance]

That works. Thanks Dave

Did you really want to apply the formatting yourself or did you want to copy the
format from the previous sheet?

I'm guessing that you wanted the format from the previous sheet:

Option Explicit
Sub myPasteLink()

Dim ActSheet As Worksheet
Dim PrevSheet As Worksheet
Dim iCtr As Long

Set ActSheet = ActiveSheet

Set PrevSheet = Nothing
For iCtr = ActSheet.Index - 1 To 1 Step -1
If TypeName(Sheets(iCtr)) = "Worksheet" Then
Set PrevSheet = Sheets(iCtr)
Exit For
End If
Next iCtr

If PrevSheet Is Nothing Then
MsgBox "No previous sheet!"
Exit Sub
End If

PrevSheet.Range(Selection.Address).Copy
ActSheet.Paste link:=True
Selection.PasteSpecial Paste:=xlPasteFormats

End Sub