Call Worksheet name from user input field???

R

Ray Rz

New Member
Joined
Jun 19, 2018
Messages
29
For the following script, I have one issue where I need to ask the user for the month in cell N16 as there is no logical way to figure out the correct tab without user input.
So for the line in Macro 3 listed as Worksheets("July 2018").Activate, I need assistance on pulling the Worksheet name in cell N16 of "Start" tab that the user would input.
Any help on calling this Worksheet name would be greatly appreciated.

Thank you,
Ray
===================================================


Code: 
Option Private Module




Sub Macro1()


Dim ws As Worksheet
Set ws = Sheets("Start")




MsgBox ("Update may take several minutes,  Click Ok to begin")
Workbooks.Open ws.Range("N10").Value
    
    'Selection.AutoFilter
    
    Range("A1:P224").Select
    Selection.Copy
    Windows("VBA Extractor r57with code V2.xlsm").Activate
    Worksheets("Invoice Summary").Activate
    ActiveCell.Offset(0, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False


Call Macro2(ws)


End Sub


Sub Macro2(ws As Worksheet)


' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+f
'


Workbooks.Open ws.Range("N12").Value
    'Selection.AutoFilter
    ActiveCell.Offset(0, 0).Range("A1:AQ35000").Select
    Selection.Copy
    Windows("VBA Extractor r57with code V2.xlsm").Activate
    Worksheets("Vendor Master").Activate
    
    ActiveCell.Offset(-1, 0).Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False




Call Macro3(ws)


End Sub
Sub Macro3(ws As Worksheet)




'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+g
'


Workbooks.Open ws.Range("N14").Value
[U][B]        Worksheets("July 2018").Activate[/B][/U]
        Range("A3").Select
    Selection.AutoFilter
    Columns("A:E").Select
    Selection.EntireColumn.Hidden = False
    Rows("3:3").Select
    Selection.AutoFilter
    ActiveCell.Offset(0, 0).Range("A2:BR26000").Select
        ActiveSheet.Range("$E2").AutoFilter Field:=5, Criteria1:= _
        "Vendor"
    Selection.Copy
    Windows("VBA Extractor r57with code V2.xlsm").Activate
    Worksheets("Const. Prog. Rpt Switches").Activate
    ActiveCell.Offset(0, 0).Range("A1").Select
    
    'ActiveCell.Offset(0, 0).Range("A2").Select
    
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False


Call refresh


End Sub






' Refresh all applicable pivot tables to setup month's data


Sub refresh()
'
' refresh Macro
' refresh data
'
' Keyboard Shortcut: Ctrl+r




' replace with Refresh All (had to remove the check for Enable Background refresh to make it wait)


    ActiveWorkbook.RefreshAll
    
'  Refresh all Pivot tables in all worksheets


Dim shtTemp As Worksheet
Dim pvtTable As PivotTable
 
For Each shtTemp In ActiveWorkbook.Worksheets
    For Each pvtTable In shtTemp.PivotTables
        pvtTable.RefreshTable
    Next
Next
MsgBox ("Update Complete,All data is Up-to date")


End Sub
 
Last edited by a moderator:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
You could use:

Code: 
Dim wks    As Excel.Worksheet

On Error Resume Next
    Set wks = ThisWorkbook.Worksheets(Sheets("Start").Range("N6").Value)
On Error GoTo 0

If Not wks Is Nothing Then
    Call wks.Activate
    'proceed with code
End If
 
Upvote 0
Thank you Jon. I tried this and it makes sense. But it did nothing because of the wks being nothing. I have attached the code for you to see what I am now having an issue with and the paste will not work on Macro3:

Code: 
Option Private Module




Sub Macro1()


Dim ws As Worksheet
Set ws = Sheets("Start")




MsgBox ("Update may take several minutes,  Click Ok to begin")
Workbooks.Open ws.Range("N10").Value
    
    'Selection.AutoFilter
    
    Range("A1:P224").Select
    Selection.Copy
    Windows("VBA Extractor r57with code V2.xlsm").Activate
    Worksheets("Invoice Summary").Activate
    ActiveCell.Offset(0, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False


Call Macro2(ws)


End Sub


Sub Macro2(ws As Worksheet)


' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+f
'


Workbooks.Open ws.Range("N12").Value
    'Selection.AutoFilter
    ActiveCell.Offset(0, 0).Range("A1:AQ35000").Select
    Selection.Copy
    Windows("VBA Extractor r57with code V2.xlsm").Activate
    Worksheets("Vendor Master").Activate
    
    ActiveCell.Offset(-1, 0).Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False




Call Macro3(ws)


End Sub
Sub Macro3(ws As Worksheet)




'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+g
'


Workbooks.Open ws.Range("N14").Value
'
Dim wks    As Excel.Worksheet


On Error Resume Next
    Set wks = ThisWorkbook.Worksheets(Sheets("Start").Range("N16").Value)
On Error GoTo 0


If Not wks Is Nothing Then
    Call wks.Activate
 '
        Worksheets("N16").Activate
        Range("A3").Select
    Selection.AutoFilter
    Columns("A:E").Select
    Selection.EntireColumn.Hidden = False
    Rows("3:3").Select
    Selection.AutoFilter
    ActiveCell.Offset(0, 0).Range("A2:BR26000").Select
        ActiveSheet.Range("$E2").AutoFilter Field:=5, Criteria1:= _
        "Vendor"
    Selection.Copy
    Windows("VBA Extractor r57with code V2.xlsm").Activate
    Worksheets("Const. Prog. Rpt Switches").Activate
    ActiveCell.Offset(0, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False


Call refresh


End If


End Sub






' Refresh all applicable pivot tables to setup month's data


Sub refresh()
'
' refresh Macro
' refresh data
'
' Keyboard Shortcut: Ctrl+r




' replace with Refresh All (had to remove the check for Enable Background refresh to make it wait)


    ActiveWorkbook.RefreshAll
    
'  Refresh all Pivot tables in all worksheets


Dim shtTemp As Worksheet
Dim pvtTable As PivotTable
 
For Each shtTemp In ActiveWorkbook.Worksheets
    For Each pvtTable In shtTemp.PivotTables
        pvtTable.RefreshTable
    Next
Next
MsgBox ("Update Complete,All data is Up-to date")


End Sub
 
Last edited by a moderator:
Upvote 0
@Ray Rz
When posting code please use code tags, the # icon in the reply window.
Cheers
 
Upvote 0
Ok, I see that. Just select text and wrap code tags pull down. Thank you. Just learning the forum.
Ray
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Macro Formatting to all WS
Replies
4
Views
317
MARK858
MARK858
J
  • Question Question
VBA removing heading in spreadsheet
Replies
3
Views
264
rlv01
R
R
  • Solved
VBA - Apply Macros to 100 workbooks without needing to open workbooks individually to apply Macros
Replies
5
Views
685
Micron
M
JohnZ1156
  • Solved
VBA Macro to print table rows from a start date to an end date.
Replies
4
Views
318
Joe4
Joe4
willow1985
  • Solved
Find first blank cell in column A in a Table - Runtime error 91
Replies
4
Views
390
willow1985
willow1985

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top