| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 --------- |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 --------- |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 --------- |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 ----------------------- |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 ----------------------- |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 ------------- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need help Taking alot data from one sheet (if not blank) and copying toa list on another sheet. | Excel Worksheet Functions | |||
| Copying the repeated data of the previous sheet to the next sheet | Excel Discussion (Misc queries) | |||
| Dynamic column chart - copying from Sheet to Sheet. | Charts and Charting in Excel | |||
| How to reference in sheet module value on another sheet? | Excel Programming | |||
| 2 questions, copying data from sheet to sheet and assigning macro | Excel Worksheet Functions | |||
Linear Mode
