VBA Noob, PasteSpecial problems

[Sarkonis]

Hi Folks,

Complete VBA Noob here. Been reading these forums for a long time. Finally found a topic I can't find anywhere.

I've got a number of frozen panes at the top of a spreadsheet referencing the active row of the same sheet.

I've used this code to transpose my active cells to the top.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A" & (ActiveCell.Row)).Copy Destination:=Range("A3")
Range("B" & (ActiveCell.Row)).Copy Destination:=Range("B3")
Range("C" & (ActiveCell.Row)).Copy Destination:=Range("C3")
End Sub


The problem I'm having is that Column C is calculated by a formula. I understand I have to use PasteSpecial to bring the value and not the formula, but I can't seem to make it work.

Help?

much thanks!

 

[Scott Huish]

Perhaps:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row = 3 Then Exit Sub
Application.EnableEvents = False
    Range("A" & ActiveCell.Row).Resize(, 2).Copy Range("A3")
    Range("C" & ActiveCell.Row).Copy
    Range("C3").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Target.Select
Application.EnableEvents = True
End Sub

 

[Sarkonis]

It works!

I don't understand why it works, or how Column B is still copying to the top, but it works!
I assume that column B has something to do with the .resize(, 2)

Many thanks!

 

[Scott Huish]

It's copying both columns at the same time.
It's taking column A resizing it to 2 columns to grab column B and then pasting both columns starting at A3