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

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.

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

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
---------

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

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
'<<=======================

---
Regards,
Norman



"CRayF" wrote in message
...
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
---------

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

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!

---
Regards,
Norman



"Norman Jones" wrote in message
...
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("ProgramSummaryTemplate").Range("N4:Q242"). Formula
ActiveSheet.Protect
End If

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

---
Regards,
Norman



"CRayF" wrote in message
...
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
---------

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

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
-----------------------

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

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


---
Regards,
Norman



"CRayF" wrote in message
...
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
-----------------------

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

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
-------------