Copying cells from on sheet to another sheet (via sheet module)

[Guest]

I am trying to code that when a cell is selected it copies the cell range
N3:Q242 from sheet x to the same cell range on the current sheet. With the
code below, I receive a error:
Run-time error ‘1004’
Select method of Range class failed.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$K$1" Then

Sheets("ProgramSummaryTemplate").Select
Range("N3:Q242").Select
Selection.Copy
Sheets("ProgramSummary").Select
Range("N3:Q242").Select
Selection.PasteSpecial

End If

Cancel = True
End Sub

Also, what is the syntax for the target to become the “active†worksheet
instead of being hard coded.

 

[Guest]

OK, I got this to work BUT... it copies the data from the source worksheet
rather than the formuls on the worksheet. Can I copy the formulas?

-------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$K$1" Then
ActiveSheet.Unprotect
ThisWorkbook.Worksheets("ProgramSummaryTemplate").Unprotect
ActiveSheet.Range("N4:Q242").Value =
Sheets("ProgramSummaryTemplate").Range("N4:Q242").Value
ThisWorkbook.Worksheets("ProgramSummaryTemplate").Protect
ActiveSheet.Protect
End If

Cancel = True
End Sub
---------

 

[Norman Jones]

Hi CrayF,

Try:

'=======================>>
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$K$1" Then
Me.Unprotect
Me.Range("N4:Q242").Formula = _
Sheets("Sheet2").Range("N4:Q242").Formula
ActiveSheet.Protect
End If

End Sub
'<<=======================

 

[Norman Jones]

Hi CRayF,

Change:

Me.Range("N4:Q242").Formula = _
Sheets("Sheet2").Range("N4:Q242").Formula

to:
Me.Range("N4:Q242").Formula = _
Sheets("ProgramSummaryTemplate"). _
Range("N4:Q242").Formula

I failed to rename my test sheet!

 

[Guest]

Thanks Norman...
That works...

---------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$K$1" Then
ActiveSheet.Unprotect
ThisWorkbook.Worksheets("ProgramSummaryTemplate").Unprotect
ActiveSheet.Range("N3:Q242").Formula =
Sheets("ProgramSummaryTemplate").Range("N3:Q242").Formula
ThisWorkbook.Worksheets("ProgramSummaryTemplate").Protect
ActiveSheet.Protect
End If

Cancel = True
End Sub
-----------------------

 

[Norman Jones]

Hi CRayF,.

Thanks Norman...
That works

Excellent.

However, for the purposes of your described operation, it is unnecessary to
unprotect / reprotect the "ProgramSummaryTemplate" sheet. Therefore, the
lines:

ThisWorkbook.Worksheets("ProgramSummaryTemplate").Unprotect
and

ThisWorkbook.Worksheets("ProgramSummaryTemplate").Protect

are redundant.

Also, what is the intended purpose of:

Cancel = True

 

[Guest]

thanks again,
------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$K$1" Then
ActiveSheet.Unprotect
ActiveSheet.Range("N3:Q242").Formula =
Sheets("ProgramSummaryTemplate").Range("N3:Q242").Formula
ActiveSheet.Protect
Range("N3").Select
End If

Cancel = True
End Sub
-------------