Declaring variables for use in a Module
 

Is there a way to declare these statement within a Module so that I don't
have to do it in every Subroutine in the Module?

Set errorWS = Worksheets("Error_MarketList")
Set bulkuploadWS = Worksheets("Bulkupload Result")

Right now I have this:

Private Sub cmdbuildBulkUpload_Click()
Set errorWS = Worksheets("Error_MarketList")
errorWS_lastRow = errorWS.Cells(Rows.Count, "A").End(xlUp).Row

Private Sub cmdupdateDates_Click()
Set errorWS = Worksheets("Error_MarketList")
errorWS_lastRow = errorWS.Cells(Rows.Count, "A").End(xlUp).Row

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Set siteWS = Worksheets("Site Milestone Dates")
Set errorWS = Worksheets("Error_MarketList")
errorWS_lastRow = errorWS.Cells(Rows.Count, "A").End(xlUp).Row

Private Sub cmdupdateMarketlist_Click()
Set updateWS = Worksheets("Updated_MarketList")
Set errorWS = Worksheets("Error_MarketList")
errorWS_lastRow = errorWS.Cells(Rows.Count, "A").End(xlUp).Row
updateWS_lastRow = updateWS.Cells(Rows.Count, "A").End(xlUp).Row - 2

Sub changeMarketList()
Set errorWS = Worksheets("Error_MarketList")
Set updateWS = Worksheets("Updated_MarketList")

Sub build_BulkUpload(Sdate As String, Sstatus As String, cRow As Integer)
Dim siteRow As Long
Dim bulkuploadWS As Worksheet, errorWS As Worksheet
Set errorWS = Worksheets("Error_MarketList")
Set bulkuploadWS = Worksheets("Bulkupload Result")

Declaring variables for use in a Module
 

You could make them a Public variable.

Put this in a standard module.

Public errorWS As Worksheet
Public bulkuploadWS As Worksheet

Keep in mind this only declares the variable. You still need to set a
worksheet to it. Maybe you could use the Workbook Open Event.

Private Sub Workbook_Open()

Set errorWS = Worksheets("Error_MarketList")
Set bulkuploadWS = Worksheets("Bulkupload Result")

End Sub

At this point you can use your errorWS and bulkuploadWS variables in your
procedures. Note: If anywhere in your procedures you use the End method
your public variable will have to be re Set.

Hope this helps! If so, let me know, click "YES" below.


--
Cheers,
Ryan


"Ayo" wrote:

Is there a way to declare these statement within a Module so that I don't
have to do it in every Subroutine in the Module?

Set errorWS = Worksheets("Error_MarketList")
Set bulkuploadWS = Worksheets("Bulkupload Result")

Right now I have this:

Private Sub cmdbuildBulkUpload_Click()
Set errorWS = Worksheets("Error_MarketList")
errorWS_lastRow = errorWS.Cells(Rows.Count, "A").End(xlUp).Row

Private Sub cmdupdateDates_Click()
Set errorWS = Worksheets("Error_MarketList")
errorWS_lastRow = errorWS.Cells(Rows.Count, "A").End(xlUp).Row

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Set siteWS = Worksheets("Site Milestone Dates")
Set errorWS = Worksheets("Error_MarketList")
errorWS_lastRow = errorWS.Cells(Rows.Count, "A").End(xlUp).Row

Private Sub cmdupdateMarketlist_Click()
Set updateWS = Worksheets("Updated_MarketList")
Set errorWS = Worksheets("Error_MarketList")
errorWS_lastRow = errorWS.Cells(Rows.Count, "A").End(xlUp).Row
updateWS_lastRow = updateWS.Cells(Rows.Count, "A").End(xlUp).Row - 2

Sub changeMarketList()
Set errorWS = Worksheets("Error_MarketList")
Set updateWS = Worksheets("Updated_MarketList")

Sub build_BulkUpload(Sdate As String, Sstatus As String, cRow As Integer)
Dim siteRow As Long
Dim bulkuploadWS As Worksheet, errorWS As Worksheet
Set errorWS = Worksheets("Error_MarketList")
Set bulkuploadWS = Worksheets("Bulkupload Result")