Updating macros with copied worksheet information

[Janelle S]

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

 

[iliace]

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.

 

[Earl Kiosterud]

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]

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.