Select TAB and copy cells in another TAB

[Roj47]

Good morning and thank you for having me on board here.

I am self-teaching by playing with the functions in Excel and using Google searches for answers to my queries.
Unfortunately for me I have come to a dead end on a particular question.

My workbook consists of 3 sheets of which one holds a large number of formulae.
This particular page when users insert new rows, the formula is not copied, so a summary sheet I look at for current sales is out of date and gives incorrect information.

I currently have a Macro to copy and paste the formulae into the inserted rows, but wish to have this automatically done when selecting the Tab with the information on.

What I have to date is the following, but it does not work at all.

User Tab is titled : VTC Summary
The Tab I am attaching the Macro to : VTC M&E Summary

Current Error : Run Time 1004 (At line 3, Range("R11.....

Code :

Private Sub Worksheet_Activate()
Sheets("VTC Detailed").Select
Range("R11:AQ11").Select
Application.CutCopyMode = False
Selection.Copy
Range("R12:AQ188").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("Q8").Select
Sheets("VTC M&E Summary").Select
Range("A1").Select
Application.EnableEvents = False
Sheets("VTC M%E Summary").Activate
Application.EnableEvents = True
End Sub

Thanking you in advance for reading and any responses.

Roj.

 

[JamesW]

Hi there,

Welcome to the board.

You don't need .Select, and should avoid using it at all costs. Try this:

Private Sub Worksheet_Activate()
    Sheets("VTC Detailed").Range("R11:AQ11").Copy
    Application.EnableEvents = False
    Range("R12:AQ188").PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Sheets("VTC M&E Summary").Activate
    Application.EnableEvents = True
End Sub

 

[Roj47]

Thank you for such a quick answer and you can see the difference between using "record Macro" and updating with Google searching compared to knowing VB.

Works a treat and copies/ pastes perfectly.

I do not however understand why it is copying from VTC Summary and pasting the range into VTC M&E Summary.

I have played around, but it is only copying into VTC M&E Summary.

Tried adding in a new line, but it really messed things up.

Sorry to ask for a [hopefully] minor amendment.

Many, many thanks.

 

[JamesW]

Try this:

Private Sub Worksheet_Activate()
    Sheets("VTC Detailed").Range("R11:AQ11").Copy
    Application.EnableEvents = False
    Sheets("VTC Detailed").Range("R12:AQ188").PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Sheets("VTC M&E Summary").Activate
    Application.EnableEvents = True
End Sub

 

[Roj47]

Spot on, thank you.

I should have been able to work that out on a trial and error basis.

Picked up a fair few clues on how to shorten code that I have recorded, so really appreciated.

Should send you a beer!

Regards

 

[JamesW]

Peroni please.