Data Validation List To Select Worksheet In Macro 1

[ADE6]

Hi,

I have a Data Validation list in cell A1 of a worksheet called LIST.

I would like to modify the macro that I have recorded, the macro uses data from various worksheets. I would like to sort out some code so that at certain points in the macro it will select the worksheet that is shown in cell A1 of the LIST sheet and then carry out the code already recorded.



THis will allow me to quickly change a sheet name that is refererred to in the macro numerous times without actually manually editing the sheet name in the macro code.

Thanks for the ideas

Ade

 

[PHV]

Why not posting your actual code, outlining where you want dynamic sheet name ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ADE6]

Hi PHV,

I have highlighted in red the three times during my macro that I need the code to refer to whatever sheet name appears in cell C2 of the worksheet named LIST.


Thanks for the ideas

Ade

Sub METASTOCK2()

[COLOR=RED] Sheets("BRENT CRUDE").Select[/COLOR]
    Range("AZ3:BA660").Select
    Selection.Copy
    Range("AZ4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("FG32420:GP32459").Select
    Selection.Copy
    Range("FH32420").Select
    ActiveSheet.PasteSpecial Format:=12, Link:=1, DisplayAsIcon:=False, _
        IconFileName:=False
    Range("GQ32420:GQ32459").Select
    Selection.ClearContents
    Range("FG32420:FG32459").Select
    Selection.ClearContents
    Range("FD32420:FD32459").Select
    Selection.Copy
    Range("FG32420").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
Range("BB10001:CH11000").Select
    Selection.Copy
   Range("BB20002").Select
   Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
        Range("BB20002:CH24000").Select
    Cells.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Range("BB20002:BF21000").Select
    Selection.Sort Key1:=Range("BB20002"), Order1:=xlDescending, Header:=xlGuess _
        , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("BI20002:BM21000").Select
    Selection.Sort Key1:=Range("BI20002"), Order1:=xlDescending, Header:=xlGuess _
        , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("BP20002:BT21000").Select
    Selection.Sort Key1:=Range("BP20002"), Order1:=xlDescending, Header:=xlGuess _
        , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("BW20002:CA21000").Select
    Selection.Sort Key1:=Range("BW20002"), Order1:=xlDescending, Header:=xlGuess _
        , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("CD20002:CH21000").Select
    Selection.Sort Key1:=Range("CD20002"), Order1:=xlDescending, Header:=xlGuess _
        , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
        Range("BB20002:CH21000").Select
    Selection.Copy
   Range("BB26002").Select
   Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
       [COLOR=RED] Sheets("BRENT CRUDE").Select[/COLOR]
    Range("BB26002:CH26999").Select
    Selection.Copy
    Sheets("PASTE LINKS").Select
    Range("AC4").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("CALC").Select
    Range("M1:AT56").Select
    Application.CutCopyMode = False
    Selection.Copy
   [COLOR=RED]  Sheets("BRENT CRUDE").Select[/COLOR]
    Range("M1").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("W17").Select
   ActiveWindow.SmallScroll Down:=-45
    Range("AB1").Select
    ActiveWindow.SmallScroll ToRight:=-93
    Range("AE4").Select
End Sub

 

[PHV]

A starting point:
Dim strSheet As String
strSheet = Sheets("LIST").Range("C2").Value
Sheets(strSheet).Select 'Sheets("BRENT CRUDE").Select
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ADE6]

Thanks PH, I really appreciate your help.

That seems to do exactly what I want.

Ade