Solved

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

Posted on 2011-02-17
18
394 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
  • 6
  • 6
  • 4
18 Comments
 

Author Comment

by:Gary-Work
Comment Utility
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
Comment Utility
Dim s1 As Worksheet

Set s1 = Worksheets("Sheet1")

Like that?
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Dim oSheet as worksheet

Set oSheet = Sheets(1)

or

Set oSheet = Sheets("Sheet1")

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
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
Comment Utility
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
Comment Utility
I meant for those to be different commandbuttons like CommandButton11_Click and CommandButton12_Click
0
 

Author Comment

by:Gary-Work
Comment Utility
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
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
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
Comment Utility
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 125 total points
Comment Utility
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 125 total points
Comment Utility
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
Comment Utility
Also you were missing the "Set" Command :)

Sid
0
 

Author Comment

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

Expert Comment

by:SiddharthRout
Comment Utility
Click on the "Request Attention" in your original post. :)

Sid
0
 

Author Comment

by:Gary-Work
Comment Utility
Thanks, I was out of town
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now