Continue to next iteration in Do While Loop

[vidyanand]

Hello, I am trying to update files of one of specific folder. I am using Do While loop to update these files. But I want to add condition. If in these excel files there is particular sheet with previous month name then loop should skip that file & move to next.

I am not even amateur in VBA programming. So I check microsoft help which suggesting that I can use 'Continue do ' but when I used it in my code it is giving Syntax error. My code is as follows

Sub file_folder_update()


Dim folderPath As String
Dim filename As String
Dim wb As Workbook

folderPath = "C:\Users\Vidyanand\Documents\Error Test\" 'change to suit

If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"

filename = Dir(folderPath & "*.xlsx")
Do While filename <> ""
Application.ScreenUpdating = False
Set wb = Workbooks.Open(folderPath & filename)

sheetname = Format(Date - 30, "MMM YY")

Dim ws As Worksheet

Set ws = Sheets(sheetname)
If Not ws Is Nothing Then
continue do
Else
'Call a subroutine here to operate on the just-opened workbook
Call portfolio_worksheet_update
wb.Save
wb.Close
End If
filename = Dir
Loop
Application.ScreenUpdating = True
MsgBox "macro run finished"
End Sub


So could anyone please help me to correct this code?

Thanx in advance

Vidyanand

 

[Dryver14]

you could enter another line under here
If Not ws Is Nothing Then
if ws.name = either month name or a cell value with that name in then
continue do
Else

 

[vidyanand]

I don't think that is logical. Because I have already assign same value to WS, so I don't see any point in if condition as it is repetition. I just want to continue the loop if condition meets.

 

[Dryver14]

sorry that is placed in the wrong place

Dim ws As Worksheet

Set ws = Sheets(sheetname)
if ws.name = either month name or a cell value with that name in then
'Do nothing
EsleIf Not ws Is Nothing Then
continue do
Else

 

[vidyanand]

Hello Dryver, I want to update only those files in the folder which don't have sheet with previous month name. If such sheet exists then skip updating that file & move on to next file in the loop.

As per your code I think we are doing exact opposite. We continue updating those files with sheets having previous month name. When I try not to provide any action if condition meet as you have done in your code, it has given erroneous result. It has opened all the files in the folder.

 

[Dryver14]

the if statement needs to be in this loop code

Call portfolio_worksheet_update

 

[vidyanand]

I try to put the same code in portfolio_worksheet_update previously. Though it has worked perfectly, it is taking so much time. So I thought using the condition in file_folder_update would be quicker solution as it will skip the files which will not meet the criteria.

Do you want to suggest that I can't use this condition in file_folder_update but instead use in portfolio_worksheet_update & still can continue with the loop in file_folder_update?

 

[vidyanand]

My portfolio_worksheet_update code

Sub portfolio_worksheet_update()


Dim sheetname As String
Dim sheetname1 As String


sheetname = Format(Date - 30, "mmm yy")
sheetname1 = Format(Date - 60, "mmm yy")


Dim ws As Worksheet
On Error Resume Next
Set ws = Sheets(sheetname)
On Error GoTo 0


If Not ws Is Nothing Then
Exit Sub
Else

 

[Dryver14]

I really don't know where you are looping through the sheets when you keep putting call macro.

In whatever macro is looping through the sheets try something like
if not ws is nothing then
Select case ws.name
Case " Excluded ws.name "
'do nothing
Case else

do loop




End Select
End If

 

[vidyanand]

I am not looping through sheets but files of the folder. Call macro is used to update those files. But before updating those files, I want to put condition where I want to know if any of the files have sheet with previous month name. If particular file does have such sheet then loop should skip that file for updating & move on to next file. I think this is what I have try to do in my original query.

I am not pro in VBA coding & nor I am aware of its syntax. So if possible could you please demonstrate your latest suggestion, taking my original code as example.