?
Solved

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

Posted on 2011-02-17
18
Medium Priority
?
408 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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

770 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