Set worksheets as variables

**Grilleman** · 05-03-2012, 09:19 AM

Hello -

I have a procedure that allows me to click a command button on sheet1 (referred to here as "Quote") to add pics to cell R51 based on a values in cells T13, T14 and T15. I also have a procedure that duplicates Sheet1 and creates Sheet2. However, the values in T13, T14 and T15 will be different on sheet1 and sheet2. What I want to do is set sheet1 and sheet2 as variables so I can run the same procedure on either sheet. I need help with declaring these sheets as variables. Thanks! Here's my code:

Public Sub Get_Images()

Dim strGrilleProfile As String
Dim strSurroundProfile As String
Dim strFastenerProfile As String

strGrilleProfile = Range("Quote!T13")
strSurroundProfile = Range("Quote!T14")
strFastenerProfile = Range("Quote!T15")

Application.ScreenUpdating = False
Application.Activesheet.Range("R51").Select
Application.ScreenUpdating = True

Select Case Left(strGrilleProfile, 2)

'Application.ActiveSheet.Range("R51").Select


    Case "WD"
        
        Activesheet.Pictures.Insert("C:\Users\Tom\Documents\BIG BLUE\BBWimages\" & strGrilleProfile & " Drawing.JPG").Select
        
        Selection.ShapeRange.IncrementLeft 5
        Selection.ShapeRange.IncrementTop 20
    
    Select Case strGrilleProfile
    
    Case "WD101A", "WD105", "WD205", "WD210", "WD401", "WD805", "WD211"

        Selection.ShapeRange.LockAspectRatio = msoTrue
        Selection.ShapeRange.ScaleWidth 0.7, msoFalse, msoScaleFromTopLeft
        Selection.ShapeRange.ScaleHeight 0.9, msoFalse, msoScaleFromTopLeft
    
    Case "WD401A", "WD504"

        Selection.ShapeRange.LockAspectRatio = msoTrue
        Selection.ShapeRange.ScaleWidth 0.8, msoFalse, msoScaleFromTopLeft
        Selection.ShapeRange.ScaleHeight 0.8, msoFalse, msoScaleFromTopLeft
    
    Case "WD101B", "WD102A", "WD104", "WD105", "WD202", "WD206", "WD207", "WD209", "WD301A", "WD304", "WD307", "WD402", "WD403", "WD603"

        Selection.ShapeRange.LockAspectRatio = msoTrue
        Selection.ShapeRange.ScaleWidth 1, msoFalse, msoScaleFromTopLeft
        Selection.ShapeRange.ScaleHeight 0.7, msoFalse, msoScaleFromTopLeft
    
     Case "WD303", "WD505", "WD208"

        Selection.ShapeRange.LockAspectRatio = msoTrue
        Selection.ShapeRange.ScaleWidth 0.5, msoFalse, msoScaleFromTopLeft
        Selection.ShapeRange.ScaleHeight 0.5, msoFalse, msoScaleFromTopLeft
    
    Case "WD102", "WD103A", "WD801", "WD210"

        Selection.ShapeRange.LockAspectRatio = msoTrue
        Selection.ShapeRange.ScaleWidth 1.1, msoFalse, msoScaleFromTopLeft
        Selection.ShapeRange.ScaleHeight 1.1, msoFalse, msoScaleFromTopLeft
    
    Case Else
    
End Select

    Case "AL"

        Activesheet.Pictures.Insert("C:\Users\Tom\Documents\BIG BLUE\BBWimages\" & strGrilleProfile & " Drawing.JPG").Select
        
        Selection.ShapeRange.IncrementLeft 5
        Selection.ShapeRange.IncrementTop 5
        
    Case Else
    
End Select

'Insert

Select Case strGrilleProfile
    
    Case "AL307"

        Selection.ShapeRange.LockAspectRatio = msoTrue
        Selection.ShapeRange.ScaleWidth 0.4, msoFalse, msoScaleFromTopLeft
        Selection.ShapeRange.ScaleHeight 0.4, msoFalse, msoScaleFromTopLeft
        
 Case Else
    
End Select



'Stop

Select Case Left(strSurroundProfile, 2)


    Case "SR"
        
        Activesheet.Pictures.Insert("C:\Users\Tom\Documents\BIG BLUE\BBWimages\" & strSurroundProfile & " Drawing.JPG").Select

        Selection.ShapeRange.IncrementLeft 115
        Selection.ShapeRange.IncrementTop 5
                
    Case Else
       
End Select

Select Case strSurroundProfile
    
    Case "SR1001", "SR1002"

        Selection.ShapeRange.LockAspectRatio = msoTrue
        Selection.ShapeRange.ScaleWidth 0.66, msoFalse, msoScaleFromTopLeft
        Selection.ShapeRange.ScaleHeight 0.66, msoFalse, msoScaleFromTopLeft
    
    Case "SR5001"

        Selection.ShapeRange.LockAspectRatio = msoTrue
        Selection.ShapeRange.ScaleWidth 0.23, msoFalse, msoScaleFromTopLeft
        Selection.ShapeRange.ScaleHeight 0.23, msoFalse, msoScaleFromTopLeft
    
    Case "SR4002", "SR4003", "SR4008", "SR7001"

        Selection.ShapeRange.LockAspectRatio = msoTrue
        Selection.ShapeRange.ScaleWidth 0.25, msoFalse, msoScaleFromTopLeft
        Selection.ShapeRange.ScaleHeight 0.25, msoFalse, msoScaleFromTopLeft
    
     Case "SR5002", "SR5002B", "SR5002C"

        Selection.ShapeRange.LockAspectRatio = msoTrue
        Selection.ShapeRange.ScaleWidth 0.34, msoFalse, msoScaleFromTopLeft
        Selection.ShapeRange.ScaleHeight 0.34, msoFalse, msoScaleFromTopLeft
    
    Case "SR4001"

        Selection.ShapeRange.LockAspectRatio = msoTrue
        Selection.ShapeRange.ScaleWidth 0.3, msoFalse, msoScaleFromTopLeft
        Selection.ShapeRange.ScaleHeight 0.35, msoFalse, msoScaleFromTopLeft
    
    Case "SR2001", "SR6001", "SR6002", "SR9003"

        Selection.ShapeRange.LockAspectRatio = msoTrue
        Selection.ShapeRange.ScaleWidth 0.66, msoFalse, msoScaleFromTopLeft
        Selection.ShapeRange.ScaleHeight 0.66, msoFalse, msoScaleFromTopLeft
    
     Case "SR2002", "SR3001", "SR7002", "SR8001"

        Selection.ShapeRange.LockAspectRatio = msoTrue
        Selection.ShapeRange.ScaleWidth 0.4, msoFalse, msoScaleFromTopLeft
        Selection.ShapeRange.ScaleHeight 0.4, msoFalse, msoScaleFromTopLeft
    
     Case "SR1004", "SR1005", "SR1006", "SR1007"

        Selection.ShapeRange.LockAspectRatio = msoTrue
        Selection.ShapeRange.ScaleWidth 0.5, msoFalse, msoScaleFromTopLeft
        Selection.ShapeRange.ScaleHeight 0.4, msoFalse, msoScaleFromTopLeft
    
    Case "SR2003", "SR4004", "SR4009"

        Selection.ShapeRange.LockAspectRatio = msoTrue
        Selection.ShapeRange.ScaleWidth 0.5, msoFalse, msoScaleFromTopLeft
        Selection.ShapeRange.ScaleHeight 0.5, msoFalse, msoScaleFromTopLeft
    
     Case "SR1003"

        Selection.ShapeRange.LockAspectRatio = msoTrue
        Selection.ShapeRange.ScaleWidth 0.5, msoFalse, msoScaleFromTopLeft
        Selection.ShapeRange.ScaleHeight 0.66, msoFalse, msoScaleFromTopLeft
    
    Case Else
    
End Select

Select Case Left(strFastenerProfile, 2)


    Case "FA"
        
        Activesheet.Pictures.Insert("C:\Users\Tom\Documents\BIG BLUE\BBWimages\" & strFastenerProfile & " Drawing.JPG").Select

        Selection.ShapeRange.IncrementLeft 200
        Selection.ShapeRange.IncrementTop 20
                
    Case Else
       
End Select

Select Case strFastenerProfile
    
    Case "FA - Dual Lock", "FA - Slide Pins", "FA - Pins & Grommets", "FA-1216 Concealed Clip", "FA-1212 Concealed Clip", "FA-1213 Concealed Clip", "FA-1203 Concealed Clip"

        Selection.ShapeRange.LockAspectRatio = msoTrue
        Selection.ShapeRange.ScaleWidth 0.88, msoFalse, msoScaleFromTopLeft
        Selection.ShapeRange.ScaleHeight 0.88, msoFalse, msoScaleFromTopLeft
    
    Case Else
    
End Select
End Sub

**TMS** · 05-03-2012, 10:04 AM

You will, no doubt, be asked by the moderators to add code tags to your original post before anyone answers your question. This is just a word of advice to speed up the process. I'll assume that you're going to do that ... fairly quickly.

And it would be something like: Dim Sh1 as Worksheet: Set Sh1 = Sheets("Sheet1")

Regards, TMS