Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

how to assign variable to Sheets(x) in Macro.

Posted on 2011-02-17
18
Medium Priority
?
414 Views
Last Modified: 2012-05-11
I have been searching for a way to consolidate the code for a worksheet that has 20 command buttons.  The buttons are very similar with the only difference being the Sheets(x) being referenced.  If I could find a way to

Dim Sheet1 as String '??
Dim Sheet2 as String '??

sheet1 = Sheets(2)
sheet2= Sheets(22)

Attached is the code that I would like to create a Function/Sub from.  So that I would only need to have the Sheets defined at the Command Button.

I'll also attach the workbook so you can see the purpose.  I'm new to VB so it is kind of a hack job but it is working, just trying to shrink the code.

Private Sub CommandButton21_Click()
  Dim Btn As Object
  Dim FileName As String
  Dim Journals As Workbook
  Dim JrnlWks As Worksheet
  Dim OrigWks As Worksheet
  Dim WksName As String
  Dim fname
  Dim WdObj As Object
  Dim FileLength As Long
  Dim FileCount As Long
  Dim SaveTo As String
  Dim Sheet1 As Sheet
  Dim Sheet2 As Sheet
  
'    Application.ScreenUpdating = False
    Sheet1 = "Sheets(2)"
    Sheet1.Select
    Set OrigWks = ActiveSheet
    
      FileLength = Sheets(3).[M2].Value + 5 'cell counts the number of linse that need to be copied to word.
      SaveTo = Sheets(1).[o8].Value 'cell compiles the SaveTo location "S:\Systems_Analyst\Releases\ & $E$4  &D8"
      ActiveSheet.PageSetup.PrintArea = "$A$1:$I$" & FileLength
  
    FileName = OrigWks.Name & ".xls"
    WksName = OrigWks.Name

    
      If Dir(FileName) <> "" Then
        On Error Resume Next
          Set Journals = Workbooks(FileName)
            If Err = 9 Then Set Journals = Workbooks.Open(FileName)
          Err.Clear
        On Error GoTo 0
      Else
        Set Journals = Workbooks.Add(Template:=xlWBATWorksheet)
'        Journals.SaveAs FileName
      End If
      
      On Error Resume Next
        Set JrnlWks = Journals.Worksheets(WksName)
          If Err = 9 Then
            OrigWks.Copy After:=Journals.Worksheets(Journals.Worksheets.Count)
            Set JrnlWks = Journals.Worksheets(Journals.Worksheets.Count)
            With JrnlWks
              .Name = WksName
              .Range("B2") = Range("B2").Value
              .Range("E2").Hyperlinks.Delete
              .Range("E2").Value = ""
                For Each Btn In .Buttons
                  Btn.Delete
                Next Btn
            End With
          Else
            JrnlWks.UsedRange = OrigWks.UsedRange.Value
            JrnlWks.Range("E2") = ""
          End If
        Err.Clear
      On Error GoTo 0
      
 If WorksheetExists("Sheet1") = True Then
    Application.DisplayAlerts = False
    Sheets("Sheet1").Select
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True
 
End If
    ActiveWorkbook.SaveAs FileName:=SaveTo & FileName
    ActiveWorkbook.Close savechanges:=True
      ThisWorkbook.Activate
      
        
'****************************************** Word Document Generator ***********************************

    Dim wdDoc As Object

    FileName = OrigWks.Name & ".doc"
    
    Set WdObj = CreateObject("Word.Application")
    Set wdDoc = WdObj.Documents.Open _
      (FileName:="S:\Systems_Analyst\Testing Team\ScreenShots.doc")
    WdObj.Visible = False
        
    Sheets(23).Select
    ActiveSheet.Range("A1:C200").Select
    Selection.Replace What:="a=", Replacement:="=", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
        Sheets(23).[A1].Copy
        WdObj.Selection.PasteAndFormat (wdPasteDefault)
        WdObj.Run MacroName:="Personalize"
  
    FileLength = Sheets(3).[n2].Value 'cell counts the number of linse that need to be copied to word.
    WksName = OrigWks.Name
    WdObj.Selection.TypeParagraph
    Sheets(23).[B2:c8].Copy 'Range is copied as a table to Word
    WdObj.Selection.Paste
    Application.CutCopyMode = False
    
    WdObj.Selection.TypeParagraph
        Sheets(23).[B10].Copy
        WdObj.Selection.PasteAndFormat (wdPasteDefault)
    WdObj.Selection.TypeParagraph

    FileCount = 11
    For i = FileCount To FileLength
        Sheets(23).Range("C" & i).Copy
        WdObj.Selection.PasteAndFormat (wdPasteDefault)
    Next i

    Application.CutCopyMode = False
    If FileName <> "" Then 'make sure fname is not blank
    With WdObj
        .ChangeFileOpenDirectory SaveTo
        .ActiveDocument.SaveAs FileName ':=fname & ".doc"
    End With
    Else:
    MsgBox ("File not saved, naming range was botched, guess again.")
    End If
    Set wdDoc = Nothing
    With WdObj
        .ActiveDocument.Close False
        .Quit
    End With
    Set WdObj = Nothing
                
    Sheets(23).Select
    ActiveSheet.Range("A1:C200").Select
    Selection.Replace What:="=", Replacement:="a=", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
  
    Sheets(1).Select
    MsgBox ("Test Scripts have been generated " & Chr$(13) & SaveTo & OrigWks.Name & ".doc" & Chr$(13) & "and" & Chr$(13) & SaveTo & OrigWks.Name & ".xls")
       Range("B8").Select
    With Selection.Interior
        .ColorIndex = 4
        .Pattern = xlSolid
    End With
    Application.ScreenUpdating = True
End Sub

Open in new window

Document-Generator--Taco-.xls
0
Comment
Question by:Gary-Work
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 4
18 Comments
 

Author Comment

by:Gary-Work
ID: 34919605
just notice the code it a little off.  the snippet is showing what should be

Sheet1 = "sheets.(3)"
Sheet2 = "sheets.(23)"
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34919610
Dim s1 As Worksheet

Set s1 = Worksheets("Sheet1")

Like that?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34919615
Dim oSheet as worksheet

Set oSheet = Sheets(1)

or

Set oSheet = Sheets("Sheet1")

Sid
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34919635
Adding further to what Tommy suggested, Worksheet can also include Charts and hence should be avoided. It might give an error if the user by chance modifies the code to

Dim s1 As Worksheet
Set s1 = Worksheets(1)

Sid
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34919643
Do you need something like this?

Private Sub CommandButton11_Click()
runButton(2,8)
End Sub
Private Sub CommandButton11_Click()
runButton(4,12)
End Sub
Sub runButton(s1 As Integer, s2 As Integer)
Sheets(s1).YourCodeHere
'Rest of code
End Sub

Open in new window

0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34919663
I meant for those to be different commandbuttons like CommandButton11_Click and CommandButton12_Click
0
 

Author Comment

by:Gary-Work
ID: 34919677
no i need it to be the position of the sheet. not the tab name.
  Dim WksName As String
  Dim Sheet1 As Worksheet
  Dim Sheet2 As Worksheet

    Sheet1 = "Sheets(3)"
    Sheet1.Select
     WksName = Sheet1.[b2].Value
    MsgBox (Sheet1)

is giving an error
0
 

Author Comment

by:Gary-Work
ID: 34919688
sorry wish i new how to edit my previous entry but i made a type on the code...

  Dim WksName As String
  Dim Sheet1 As Worksheet
  Dim Sheet2 As Worksheet

    Sheet1 = "Sheets(3)"
    Sheet1.Select
     WksName = Sheet1.[b2].Value
    MsgBox (WksName)

is giving an error
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34919692
Refer to my first post.

IS this what you want?

  Dim WksName As String
  Dim Sheet1 As Worksheet
  Dim Sheet2 As Worksheet

   Set Sheet1 =  Sheets(3)
   WksName = Sheet1.[b2].Value
   MsgBox (Sheet1)

Sid
0
 

Author Comment

by:Gary-Work
ID: 34919739
yes, I do want that code to be able to have the masage box display the value that is on cell B2, of the 3rd sheet in the workbook.

0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 34919749
In that case try this

  Dim WksName As String
  Dim Sheet1 As Worksheet
  Dim Sheet2 As Worksheet

   Set Sheet1 =  Sheets(3)
   WksName = Sheet1.[b2].Value
   MsgBox (WksName )

Sid
0
 
LVL 37

Assisted Solution

by:TommySzalapski
TommySzalapski earned 500 total points
ID: 34919782
So you want all the command buttons to run the same code but on different sheet numbers right?
You need to make a sub that takes the sheet numbers as parameters and call that from each button. Like this:
Private Sub CommandButton11_Click()
runButton(2,8)
End Sub
Private Sub CommandButton12_Click()
runButton(4,12)
End Sub
Sub runButton(s1 As Integer, s2 As Integer)
Sheets(s1).YourCodeHere
'Rest of code
End Sub 

Open in new window

0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34919844
Also you were missing the "Set" Command :)

Sid
0
 

Author Comment

by:Gary-Work
ID: 34919856
oh, how do i edit the points i would like to split between the 2 of you.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34919863
Click on the "Request Attention" in your original post. :)

Sid
0
 

Author Comment

by:Gary-Work
ID: 34982269
Thanks, I was out of town
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question