run macro from different worksheet 2

[waubain]

I had a cmdButton on the same worksheet as the information it processed. Now I need to move the button to a different worksheet, since the data worksheet("DATA") will change based on the project.

My problem is the macro is not reading the Worksheets("DATA"). It is still reading data from worksheet where the cmdButton is located. It appeared that common fix was to add

With Worksheets("DATA")

...my code

End With

This does not seem to work in my case. Here is my code with the With End With statements.

Thank you for any advice.

Option Explicit

'change this to where your files are stored
Const FilePath As String = "E:\Work Projects\Conversion\Conversion Letter\"

Dim wd As New Word.Application
Dim PersonCell As Range

Private Sub CommandButton1_Click()
Dim doc As Word.Document
Dim PersonRange As Range
Dim PrintedRx As Integer
Dim test As String

wd.Visible = True
PrintedRx = 0

With Worksheets("ORIGINAL")   ' **** This line added when cmdButton moved to different sheet
            
    Range("A2").Select
    Set PersonRange = Range(ActiveCell, ActiveCell.End(xlDown))

        For Each PersonCell In PersonRange  'for each person in list ...
        
            If PersonCell.Offset(0, 3) = "RX CREATED" And PersonCell.Offset(0, 4) = 0 Then
                                
                Set doc = wd.Documents.Open(FilePath & "FormLetter.dotx") 'open a document in Word form letter
        
                'go to each bookmark and type in details
                CopyCell "FirstName", 1
                CopyCell "LastName", 2
                                    
                'doc.PrintOut 'doc.PrintOut prints and closes document without saving
                doc.PrintPreview 'doc.PrintPreview previews and then closes without saving
                doc.Close SaveChanges:=False
                Set doc = Nothing
                PersonCell.Offset(0, 4) = 1
                PrintedRx = PrintedRx + 1
            End If
               
       Next PersonCell
End With ' **** This line added when cmdButton moved to different sheet

       MsgBox PrintedRx & " letters printed", vbOKOnly

wd.Quit
Set wd = Nothing

End Sub


Sub CopyCell(BookMarkName As String, ColumnOffset As Integer)

    'copy each cell to relevant Word bookmark
        wd.Selection.GoTo What:=wdGoToBookmark, Name:=BookMarkName
        wd.Selection.TypeText PersonCell.Offset(0, ColumnOffset).Value

End Sub

You don't know what you don't know...

 

[combo]

The lines that continue referencing to
Worksheets("ORIGINAL") between [tt]With Worksheets("ORIGINAL") ... End With[/tt] have to be begin with dot, for instance [tt].Range("A2").Select[/tt].
In most cases you don't need to select cells or use ActiveCell to work with range. Moreover, if you work with non-active worksheet, it has no active cell, so you may refer to a range that you don't like to.

combo

 

[SkipVought]

hi,

The WITH statement does NOTHING...

UNLESS you use a DOT REFERENCE with the obuordinate object...

With Worksheets("ORIGINAL")   ' **** This line added when cmdButton moved to different sheet
            
    [s]Range("A2").Select[/s]
    Set PersonRange = [highlight].[/highlight]Range([highlight].[/highlight]Range("A2"), [highlight].[/highlight]Range("A2").End(xlDown))

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[waubain]

Works like a charm. Thank you both

You don't know what you don't know...

 

[Andrzejek]

Since you "need to move the button to a different worksheet", you may want to place pretty much all of your code in the Module and pass just the name of your worksheet:

Option Explicit

'change this to where your files are stored
Const FilePath As String = "E:\Work Projects\Conversion\Conversion Letter\"

Dim wd As New Word.Application
Dim PersonCell As Range

Public Sub [red]MyNewSub[/red]([blue]strWKName[/blue] As String)
Dim doc As Word.Document
Dim PersonRange As Range
Dim PrintedRx As Integer
Dim test As String

wd.Visible = True
PrintedRx = 0

With Worksheets([blue]strWKName[/blue])  
Set PersonRange = .Range(.Range("A2"), .Range("A2").End(xlDown))

...

and call it feom your command button on "ORIGINAL" Worksheet:

Private Sub CommandButton1_Click()

Call [red]MyNewSub[/red]("ORIGINAL")

End Sub

Have fun.

---- Andy

 

[SkipVought]

do you really want to MOVE the button, or do you simply want to run the procedure referencing a different SHEET or run the procedure referencing a different WORKBOOK/SHEET?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}