Add an if statement...

S

sdoppke

Well-known Member
Joined
Jun 10, 2010
Messages
647
Hi everyone, i have this macro that opens an excel workbook, in a folder and copies the contents to the existing open workbook. I am hoping someone can help me modify my macro to look into the existing open workbook for "week 1" worksheet. If it is allreay there, id like the macro to open a message box, saying "already done" and stop, so it doesn't create duplicates. i.e. "week 1(2)". Here is the macro to help with the MOD:

Code: 
Sub Get_Current_Scheduling_Template()
Dim wbThis As Workbook
Dim wbThat As Workbook
Dim ws As Worksheet
Dim x As Integer
Dim y As Integer
Application.ScreenUpdating = False
Set wb = Workbooks.Open(Filename:="S:\WASeattle\WFM\Test\Retail Store Scheduling Template.xls")
Set i = ActiveWorkbook.Sheets
For Each sh In i
sh.Visible = True
Next
Windows("Retail Store Scheduling Template.xls").Activate
    Sheets(Array("Month at a Glance", "Daily Budget for the Month", "Week 1", _
        "Week 1 Chart", "Week 2", "Week 2 Chart", "Week 3", "Week 3 Chart", "Week 4", _
        "Week 4 Chart", "Week 5", "Week 5 Chart", "Productivity Calendars", _
        "Schedule at a Glance", "Peak Hours", "Productivity Calendar Mapping", _
        "Peak Hr Daily Allocation", "Store Productivity Mapping", "Store Names", _
        "Labor Budget")).Select
    Sheets("Month at a Glance").Activate
    Sheets(Array("Month at a Glance", "Daily Budget for the Month", "Week 1", _
        "Week 1 Chart", "Week 2", "Week 2 Chart", "Week 3", "Week 3 Chart", "Week 4", _
        "Week 4 Chart", "Week 5", "Week 5 Chart", "Productivity Calendars", _
        "Schedule at a Glance", "Peak Hours", "Productivity Calendar Mapping", _
        "Peak Hr Daily Allocation", "Store Productivity Mapping", "Store Names", _
        "Labor Budget")).Copy Before:=Workbooks("WFMToolbackupLiteDateImport3.xlsm"). _
        Sheets(2)
For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Schedule Dashboard" And ws.Name <> "Week 1" And ws.Name <> "Week 2" And ws.Name <> "Week 3" And ws.Name <> "Week 4" And ws.Name <> "Week 5" Then ws.Visible = xlSheetHidden
Next
    Sheets("Schedule Tool").Visible = True
    Sheets("Schedule Tool").Select
        Range("A1").Select
    Sheets("Schedule Dashboard").Select
    Sheets("WorksheetList").Visible = True
    Sheets("WorksheetList").Select
    Cells.Select
    Selection.ClearContents
    Range("A1").Select
    Sheets("WorksheetList").Select
Windows("Retail Store Scheduling Template.xls").Activate
With ThisWorkbook.Worksheets("WorksheetList")
.Range("A:A").ClearContents
For Each ws In ActiveWorkbook.Worksheets
y = y + 1
.Range("A" & y) = ws.Name
Next ws
End With
For Each WkbkName In Application.Workbooks()
        If WkbkName.Name <> ThisWorkbook.Name Then WkbkName.Close WkbkName.Saved = False
Next
Sheets("WorksheetList").Visible = False
Sheets("Schedule Dashboard").Select
        Range("B1").Select
Application.ScreenUpdating = True
End Sub


Thanks all in advance :)

sd
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
Use this function to check if a sheet exists

Code: 
Function sheetExists(ByVal shtname As String) As Boolean
    On Error GoTo nosheet
    sheetExists = LCase(Sheets(shtname).Name) = LCase(shtname)
nosheet:
End Function
eg:

Code: 
if sheetExists("mySheet") then
  'things to do if the sheet exists
else
  'things to do if it doesn't
end if
 
Upvote 0
Weaver said:
Use this function to check if a sheet exists

Code: 
Function sheetExists(ByVal shtname As String) As Boolean
    On Error GoTo nosheet
    sheetExists = LCase(Sheets(shtname).Name) = LCase(shtname)
nosheet:
End Function
eg:

Code: 
if sheetExists("mySheet") then
  'things to do if the sheet exists
else
  'things to do if it doesn't
end if
Click to expand...


Thank you very much for the direction. :) does the function get inserted inside the sub?
 
Upvote 0
Here is a way to do it with an error catch as well w/o an if and just using gotos.

Code: 
On Error GoTo SheetDNE:
    Worksheets("week 1").Activate
    'Tries to activate the sheet, if its doesnt exists, it throws an error
    'So we catch the error and handle
On Error GoTo 0

'Here you put for the sheet existing routine

'Then goto what ever is supposed to happen after you handle the sheet existing, just put the jumpend: label to where it should go after you do the sheet already exists
GoTo jumpend:

'Sheet doesnt exists, so you import it here
SheetDNE:
On Error GoTo 0
'Import the data, the sheet doesn't exist
 
Upvote 0
I figured out that the function goes outside the Sub :)

Im hoping someone could help with the second step. The msg box. Im looking to have a message box pop if the "week 1" wrksheet exits, that sais: Scheduling template already exits, press Delete to delete current template and continue or press Stop to cancel

The Delete will run a different macro (macro x) and Stop will End Sub (current macro). Any takers? :)

Here is the current script:

Code: 
Function sheetExists(ByVal shtname As String) As Boolean
    On Error GoTo nosheet
    sheetExists = LCase(Sheets(shtname).Name) = LCase(shtname)
nosheet:
End Function
Sub Get_Current_Scheduling_Template()
Dim wbThis As Workbook
Dim wbThat As Workbook
Dim ws As Worksheet
Dim x As Integer
Dim y As Integer
If sheetExists("Week 1") Then
MsgBox "Scheduling template already exists, Press Delete to delete old template and continue, or Stop to cancel.", vbInformation, "Get Scheduling Template"
Else
Application.ScreenUpdating = False
Set wb = Workbooks.Open(Filename:="S:\WASeattle\WFM\Test\Retail Store Scheduling Template.xls")
Set i = ActiveWorkbook.Sheets
For Each sh In i
sh.Visible = True
Next
Windows("Retail Store Scheduling Template.xls").Activate
    Sheets(Array("Month at a Glance", "Daily Budget for the Month", "Week 1", _
        "Week 1 Chart", "Week 2", "Week 2 Chart", "Week 3", "Week 3 Chart", "Week 4", _
        "Week 4 Chart", "Week 5", "Week 5 Chart", "Productivity Calendars", _
        "Schedule at a Glance", "Peak Hours", "Productivity Calendar Mapping", _
        "Peak Hr Daily Allocation", "Store Productivity Mapping", "Store Names", _
        "Labor Budget")).Select
    Sheets("Month at a Glance").Activate
    Sheets(Array("Month at a Glance", "Daily Budget for the Month", "Week 1", _
        "Week 1 Chart", "Week 2", "Week 2 Chart", "Week 3", "Week 3 Chart", "Week 4", _
        "Week 4 Chart", "Week 5", "Week 5 Chart", "Productivity Calendars", _
        "Schedule at a Glance", "Peak Hours", "Productivity Calendar Mapping", _
        "Peak Hr Daily Allocation", "Store Productivity Mapping", "Store Names", _
        "Labor Budget")).Copy Before:=Workbooks("WFMToolbackupLiteDateImport3.xlsm"). _
        Sheets(2)
For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Schedule Dashboard" And ws.Name <> "Week 1" And ws.Name <> "Week 2" And ws.Name <> "Week 3" And ws.Name <> "Week 4" And ws.Name <> "Week 5" Then ws.Visible = xlSheetHidden
Next
    Sheets("Schedule Tool").Visible = True
    Sheets("Schedule Tool").Select
        Range("A1").Select
    Sheets("Schedule Dashboard").Select
    Sheets("WorksheetList").Visible = True
    Sheets("WorksheetList").Select
    Cells.Select
    Selection.ClearContents
    Range("A1").Select
    Sheets("WorksheetList").Select
Windows("Retail Store Scheduling Template.xls").Activate
With ThisWorkbook.Worksheets("WorksheetList")
.Range("A:A").ClearContents
For Each ws In ActiveWorkbook.Worksheets
y = y + 1
.Range("A" & y) = ws.Name
Next ws
End With
For Each WkbkName In Application.Workbooks()
        If WkbkName.Name <> ThisWorkbook.Name Then WkbkName.Close WkbkName.Saved = False
Next
Sheets("WorksheetList").Visible = False
Sheets("Schedule Dashboard").Select
        Range("B1").Select
Application.ScreenUpdating = True
MsgBox "Schedule template has been retrived. +1 for Doppke.", vbInformation, "Get Current Scheduling Template"
End If
End Sub
 
Upvote 0
forgot to mention if user chooses "delete" and then (macro x) runs, after (macro x) runs it will need to pick up with the rest of script. (or in other words,pick up from 'things to do if "week 1" doesnt exist.

thanks everyone :)

sd
 
Upvote 0
I gotten a bit further, but am stuck. I does nothing if "Week 1" does not exist. It doesnt error out, it just does nothing :(

Here is the code im using:

Code: 
Function sheetExists(ByVal shtname As String) As Boolean
    On Error GoTo nosheet
    sheetExists = LCase(Sheets(shtname).Name) = LCase(shtname)
nosheet:
End Function
Sub Get_Current_Scheduling_Template()
Dim wbThis As Workbook
Dim wbThat As Workbook
Dim ws As Worksheet
Dim x As Integer
Dim y As Integer
Dim Response As VbMsgBoxResult
Dim LR As Long, o As Long
If sheetExists("Week 1") Then
Response = MsgBox("A scheduling template already exists, Press Yes to delete the old template and replace, or No to cancel and quit.", vbQuestion + _
vbYesNo)
    If Response = vbNo Then
MsgBox "No change made...", vbInformation
Exit Sub
Else
Application.ScreenUpdating = False
With Sheets("WorksheetList")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    Application.DisplayAlerts = False
    On Error Resume Next
    For o = 1 To LR
        Sheets(.Range("A" & o).Value).Delete
    Next o
    On Error GoTo 0
    Application.DisplayAlerts = True
End With
Set wb = Workbooks.Open(Filename:="S:\WASeattle\WFM\Test\Retail Store Scheduling Template.xls")
Set i = ActiveWorkbook.Sheets
For Each sh In i
sh.Visible = True
Next
Windows("Retail Store Scheduling Template.xls").Activate
    Sheets(Array("Month at a Glance", "Daily Budget for the Month", "Week 1", _
        "Week 1 Chart", "Week 2", "Week 2 Chart", "Week 3", "Week 3 Chart", "Week 4", _
        "Week 4 Chart", "Week 5", "Week 5 Chart", "Productivity Calendars", _
        "Schedule at a Glance", "Peak Hours", "Productivity Calendar Mapping", _
        "Peak Hr Daily Allocation", "Store Productivity Mapping", "Store Names", _
        "Labor Budget")).Select
    Sheets("Month at a Glance").Activate
    Sheets(Array("Month at a Glance", "Daily Budget for the Month", "Week 1", _
        "Week 1 Chart", "Week 2", "Week 2 Chart", "Week 3", "Week 3 Chart", "Week 4", _
        "Week 4 Chart", "Week 5", "Week 5 Chart", "Productivity Calendars", _
        "Schedule at a Glance", "Peak Hours", "Productivity Calendar Mapping", _
        "Peak Hr Daily Allocation", "Store Productivity Mapping", "Store Names", _
        "Labor Budget")).Copy Before:=Workbooks("WFMToolbackupLiteDateImport3.xlsm"). _
        Sheets(2)
For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Schedule Dashboard" And ws.Name <> "Week 1" And ws.Name <> "Week 2" And ws.Name <> "Week 3" And ws.Name <> "Week 4" And ws.Name <> "Week 5" Then ws.Visible = xlSheetHidden
Next
    Sheets("Schedule Tool").Visible = True
    Sheets("Schedule Tool").Select
        Range("A1").Select
    Sheets("Schedule Dashboard").Select
    Sheets("WorksheetList").Visible = True
    Sheets("WorksheetList").Select
    Cells.Select
    Selection.ClearContents
    Range("A1").Select
    Sheets("WorksheetList").Select
Windows("Retail Store Scheduling Template.xls").Activate
With ThisWorkbook.Worksheets("WorksheetList")
.Range("A:A").ClearContents
For Each ws In ActiveWorkbook.Worksheets
y = y + 1
.Range("A" & y) = ws.Name
Next ws
End With
For Each WkbkName In Application.Workbooks()
        If WkbkName.Name <> ThisWorkbook.Name Then WkbkName.Close WkbkName.Saved = False
Next
Sheets("WorksheetList").Visible = False
Sheets("Schedule Dashboard").Select
        Range("B1").Select
Application.ScreenUpdating = True
MsgBox "Schedule template has been retrived. +1 for Doppke.", vbInformation, "Get Current Scheduling Template"
End If
End If
End Sub

And here is where it should start if "week 1" does not exist:

Code: 
Set wb = Workbooks.Open(Filename:="S:\WASeattle\WFM\Test\Retail Store Scheduling Template.xls")
Set i = ActiveWorkbook.Sheets
For Each sh In i
sh.Visible = True
Next
Windows("Retail Store Scheduling Template.xls").Activate
    Sheets(Array("Month at a Glance", "Daily Budget for the Month", "Week 1", _
        "Week 1 Chart", "Week 2", "Week 2 Chart", "Week 3", "Week 3 Chart", "Week 4", _
        "Week 4 Chart", "Week 5", "Week 5 Chart", "Productivity Calendars", _
        "Schedule at a Glance", "Peak Hours", "Productivity Calendar Mapping", _
        "Peak Hr Daily Allocation", "Store Productivity Mapping", "Store Names", _
        "Labor Budget")).Select
    Sheets("Month at a Glance").Activate
    Sheets(Array("Month at a Glance", "Daily Budget for the Month", "Week 1", _
        "Week 1 Chart", "Week 2", "Week 2 Chart", "Week 3", "Week 3 Chart", "Week 4", _
        "Week 4 Chart", "Week 5", "Week 5 Chart", "Productivity Calendars", _
        "Schedule at a Glance", "Peak Hours", "Productivity Calendar Mapping", _
        "Peak Hr Daily Allocation", "Store Productivity Mapping", "Store Names", _
        "Labor Budget")).Copy Before:=Workbooks("WFMToolbackupLiteDateImport3.xlsm"). _
        Sheets(2)
For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Schedule Dashboard" And ws.Name <> "Week 1" And ws.Name <> "Week 2" And ws.Name <> "Week 3" And ws.Name <> "Week 4" And ws.Name <> "Week 5" Then ws.Visible = xlSheetHidden
Next
    Sheets("Schedule Tool").Visible = True
    Sheets("Schedule Tool").Select
        Range("A1").Select
    Sheets("Schedule Dashboard").Select
    Sheets("WorksheetList").Visible = True
    Sheets("WorksheetList").Select
    Cells.Select
    Selection.ClearContents
    Range("A1").Select
    Sheets("WorksheetList").Select
Windows("Retail Store Scheduling Template.xls").Activate
With ThisWorkbook.Worksheets("WorksheetList")
.Range("A:A").ClearContents
For Each ws In ActiveWorkbook.Worksheets
y = y + 1
.Range("A" & y) = ws.Name
Next ws
End With
For Each WkbkName In Application.Workbooks()
        If WkbkName.Name <> ThisWorkbook.Name Then WkbkName.Close WkbkName.Saved = False
Next
Sheets("WorksheetList").Visible = False
Sheets("Schedule Dashboard").Select
        Range("B1").Select
Application.ScreenUpdating = True
MsgBox "Schedule template has been retrived. +1 for Doppke.", vbInformation, "Get Current Scheduling Template"

Thanks for any help.


sd
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Excel VBA Searching Unique Variables to Match against Exported Schedule Files to Determine Date
Replies
1
Views
185
sijpie
sijpie
C
  • Question Question
If User Selects Cancel All Sheets are Unprotected
Replies
4
Views
152
christianbiker
C
B
  • Question Question
If sheet title is NOT this Monday's date then execute code
Replies
3
Views
582
MARK858
MARK858
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
L
  • Question Question
VBA to export recurring meeting data
Replies
1
Views
1K
John_w
J

Forum statistics

Threads
1,224,557
Messages
6,179,507
Members
452,917
Latest member
MrsMSalt

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