Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Updating macros with copied worksheet information
Hi - am currently using Excel 2003
I really appreciate any help on this. I am using a master worksheet (new) to create and copy to other sheets and am using macro to auto update tab name from cell A2 - this works great. Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) ActiveSheet.Name = Range("a2").Value End Sub The problem is that I have another macro that I use to copy information from the copied worksheets to and from another worksheet (budget). When the master is copied, the reference remains at 'new'. I have tried just using Sheet2 as a reference but this won't work as I need to create lots of copied worksheets (therfore the worksheets will be constantly moving) and I will need to use the budget macro in each worksheet at different times. Here is what I currently have (created using recording macros - I really am a VBA amateur). Sub gotocalc() ' ' gotocalc Macro ' Macro recorded by ' ' If Range("m24").Value < "Required" _ Then Application.ScreenUpdating = False ActiveSheet.Unprotect Sheets("calc").Visible = True Sheets("Calc").Select ActiveSheet.Unprotect Sheet2.Select Range("c5").Select Application.CutCopyMode = False Selection.Copy Sheets("Calc").Select Range("B5").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheet2.Select Range("I5").Select Application.CutCopyMode = False Selection.Copy Sheets("Calc").Select Range("B6").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheet2.Select ActiveWindow.SmallScroll Down:=-6 Range("D18:E18").Select Application.CutCopyMode = False Selection.Copy Sheets("Calc").Select Range("B7").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheet2.Select Range("D20").Select Application.CutCopyMode = False Selection.Copy Sheets("Calc").Select Range("B8").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheet2.Select Range("q23").Select ActiveSheet.Protect Sheets("Calc").Select ActiveSheet.Protect Else MsgBox "Budget has not been received from service - budget calculator cannot be created. If budget has been received enter date" Range("m24").Select ActiveSheet.Protect End If End Sub I hope this makes some sense. Thanks heaps in anticipation Janelle |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Updating macros with copied worksheet information
First of all, you can condense the macro-generated code to this:
Sub gotocalc() Application.ScreenUpdating = False If Range("m24").Value < "Required" Then ActiveSheet.Unprotect Sheets("Calc").Visible = True Sheets("Calc").Unprotect Sheet2.Range("c5").Copy Sheets("Calc").Range("B5").PasteSpecial _ Paste:=xlPasteValues Sheet2.Range("I5").Copy Sheets("Calc").Range("B6").PasteSpecial _ Paste:=xlPasteValues Sheet2.Range("D18:E18").Copy Sheets("Calc").Range("B7").PasteSpecial _ Paste:=xlPasteValues Sheet2.Range("D20").Copy Sheets("Calc").Range("B8").PasteSpecial _ Paste:=xlPasteValues Sheet2.Protect Sheets("Calc").Protect Else MsgBox "Budget has not been received from service - " & _ "budget calculator cannot be created. If budget " & _ "has been received enter date" Range("m24").Select ActiveSheet.Protect End If End Sub As fas as identifying which workbook to use, you can store it in a global variable. If you want to do this for all workbooks, you could do some thing along the lines of Sub CalcAll() Dim wsh As Excel.Worksheet Application.ScreenUpdating = False For Each wsh In ThisWorkbook.Worksheets If wsh.Name < "Calc" Then With wsh If wsh.Range("m24").Value < "Required" Then ' whatever code goes here End If End With End If Next wsh End Sub If you just want to do it to the latest worksheet, add this line to ThisWorkbook (outside of any procedure): Public LatestSheet as String Then change your event code to this: Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) LatestSheet = Range("A2").Value ActiveSheet.Name = LatestSheet End Sub Then change your gotocalc references from Sheet2 to Worksheets(ThisWorkbook.LatestSheet) and it should work. On Feb 10, 2:04*am, Janelle S wrote: Hi - am currently using Excel 2003 I really appreciate any help on this. I am using a master worksheet (new) to create and copy to other sheets and am using macro to auto update tab name from cell A2 - this works great. Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ * * * * * * * * * * * * * * * * * * * ByVal Target As Range) * *ActiveSheet.Name = Range("a2").Value End Sub *The problem is that I have another macro that I use to copy information from the copied worksheets to and from another worksheet (budget). When the master is copied, the reference remains at 'new'. I have tried just using Sheet2 as a reference but this won't work as I need to create lots of copied worksheets (therfore the worksheets will be constantly moving) and I will need to use the budget macro in each worksheet at different times. Here is what I currently have (created using recording macros - I really am a VBA amateur). Sub gotocalc() ' ' gotocalc Macro ' Macro recorded by ' ' If Range("m24").Value < "Required" _ * Then * * Application.ScreenUpdating = False * * ActiveSheet.Unprotect * * Sheets("calc").Visible = True * * Sheets("Calc").Select * * ActiveSheet.Unprotect * * Sheet2.Select * * Range("c5").Select * * Application.CutCopyMode = False * * Selection.Copy * * Sheets("Calc").Select * * Range("B5").Select * * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ * * * * :=False, Transpose:=False * * Sheet2.Select * * Range("I5").Select * * Application.CutCopyMode = False * * Selection.Copy * * Sheets("Calc").Select * * Range("B6").Select * * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ * * * * :=False, Transpose:=False * * Sheet2.Select * * ActiveWindow.SmallScroll Down:=-6 * * Range("D18:E18").Select * * Application.CutCopyMode = False * * Selection.Copy * * Sheets("Calc").Select * * Range("B7").Select * * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ * * * * :=False, Transpose:=False * * Sheet2.Select * * Range("D20").Select * * Application.CutCopyMode = False * * Selection.Copy * * Sheets("Calc").Select * * Range("B8").Select * * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ * * * * :=False, Transpose:=False * * Sheet2.Select * * Range("q23").Select * * ActiveSheet.Protect * * Sheets("Calc").Select * * ActiveSheet.Protect * * Else * * MsgBox "Budget has not been received from service - budget calculator cannot be created. If budget has been received enter date" * * Range("m24").Select * * ActiveSheet.Protect * End If End Sub I hope this makes some sense. Thanks heaps in anticipation Janelle |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Updating macros with copied worksheet information
Janelle,
The code's already been written, and it's real easy to use. I'm not being coy or snide -- it's called Access. Bit of a learning curve, compared to Excel, but not bad at all, and once you have the hang of it, it does all this stuff in its sleep. Excel is very good, but not well suited for this kind of work -- you have to do everything yourself. -- Regards from Virginia Beach, Earl Kiosterud www.smokeyl.com ----------------------------------------------------------------------- "Janelle S" wrote in message ... Hi - am currently using Excel 2003 I really appreciate any help on this. I am using a master worksheet (new) to create and copy to other sheets and am using macro to auto update tab name from cell A2 - this works great. Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) ActiveSheet.Name = Range("a2").Value End Sub The problem is that I have another macro that I use to copy information from the copied worksheets to and from another worksheet (budget). When the master is copied, the reference remains at 'new'. I have tried just using Sheet2 as a reference but this won't work as I need to create lots of copied worksheets (therfore the worksheets will be constantly moving) and I will need to use the budget macro in each worksheet at different times. Here is what I currently have (created using recording macros - I really am a VBA amateur). Sub gotocalc() ' ' gotocalc Macro ' Macro recorded by ' ' If Range("m24").Value < "Required" _ Then Application.ScreenUpdating = False ActiveSheet.Unprotect Sheets("calc").Visible = True Sheets("Calc").Select ActiveSheet.Unprotect Sheet2.Select Range("c5").Select Application.CutCopyMode = False Selection.Copy Sheets("Calc").Select Range("B5").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheet2.Select Range("I5").Select Application.CutCopyMode = False Selection.Copy Sheets("Calc").Select Range("B6").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheet2.Select ActiveWindow.SmallScroll Down:=-6 Range("D18:E18").Select Application.CutCopyMode = False Selection.Copy Sheets("Calc").Select Range("B7").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheet2.Select Range("D20").Select Application.CutCopyMode = False Selection.Copy Sheets("Calc").Select Range("B8").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheet2.Select Range("q23").Select ActiveSheet.Protect Sheets("Calc").Select ActiveSheet.Protect Else MsgBox "Budget has not been received from service - budget calculator cannot be created. If budget has been received enter date" Range("m24").Select ActiveSheet.Protect End If End Sub I hope this makes some sense. Thanks heaps in anticipation Janelle |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Updating macros with copied worksheet information
Thanks Earl, but unfortunately my work does not support the use of Access
only Excel. So I guess I am going to have to slog it out with that. "Earl Kiosterud" wrote: Janelle, The code's already been written, and it's real easy to use. I'm not being coy or snide -- it's called Access. Bit of a learning curve, compared to Excel, but not bad at all, and once you have the hang of it, it does all this stuff in its sleep. Excel is very good, but not well suited for this kind of work -- you have to do everything yourself. -- Regards from Virginia Beach, Earl Kiosterud www.smokeyl.com ----------------------------------------------------------------------- "Janelle S" wrote in message ... Hi - am currently using Excel 2003 I really appreciate any help on this. I am using a master worksheet (new) to create and copy to other sheets and am using macro to auto update tab name from cell A2 - this works great. Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) ActiveSheet.Name = Range("a2").Value End Sub The problem is that I have another macro that I use to copy information from the copied worksheets to and from another worksheet (budget). When the master is copied, the reference remains at 'new'. I have tried just using Sheet2 as a reference but this won't work as I need to create lots of copied worksheets (therfore the worksheets will be constantly moving) and I will need to use the budget macro in each worksheet at different times. Here is what I currently have (created using recording macros - I really am a VBA amateur). Sub gotocalc() ' ' gotocalc Macro ' Macro recorded by ' ' If Range("m24").Value < "Required" _ Then Application.ScreenUpdating = False ActiveSheet.Unprotect Sheets("calc").Visible = True Sheets("Calc").Select ActiveSheet.Unprotect Sheet2.Select Range("c5").Select Application.CutCopyMode = False Selection.Copy Sheets("Calc").Select Range("B5").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheet2.Select Range("I5").Select Application.CutCopyMode = False Selection.Copy Sheets("Calc").Select Range("B6").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheet2.Select ActiveWindow.SmallScroll Down:=-6 Range("D18:E18").Select Application.CutCopyMode = False Selection.Copy Sheets("Calc").Select Range("B7").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheet2.Select Range("D20").Select Application.CutCopyMode = False Selection.Copy Sheets("Calc").Select Range("B8").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheet2.Select Range("q23").Select ActiveSheet.Protect Sheets("Calc").Select ActiveSheet.Protect Else MsgBox "Budget has not been received from service - budget calculator cannot be created. If budget has been received enter date" Range("m24").Select ActiveSheet.Protect End If End Sub I hope this makes some sense. Thanks heaps in anticipation Janelle |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Updating Information Revised | New Users to Excel | |||
Macros copied to new PC won't enable | Setting up and Configuration of Excel | |||
macros not updating | Excel Worksheet Functions | |||
Formatting information copied and pasted from the WEB to excel | Excel Discussion (Misc queries) | |||
When updating a worksheet, how do I create a link updating the sa. | Excel Worksheet Functions |