Help with errors or IF statments

camandab

Board Regular
Joined
Feb 10, 2010
Messages
79
Excel 2002

Below is my code that will create a new tab for the current month for each workbook in a folder. Sometimes there will be a workbook in the middle of a folder that will generate an error so I would like to be able to correct the error in the code but then re-run the macro. So I need to either start from the beginning of the folder and have it ignore any workbook that contains a tab already called "Feb 10" or have it start from the place I left off. Any help would be great! Thanks!

Code:
Dim fName As String
Dim fOUT As String, ws As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False



CurDir "C:\... Recs2010\"

fName = Dir("*.xls")


Do While Len(fName) > 0     'open each file and make sheet changes
    Workbooks.Open (fName)
    

Sheets("Jan 10").Copy After:=Sheets(Sheets.Count)

ActiveSheet.Name = "Feb 10"
        
        Sheets(Sheets.Count).Select
       Cells.Find(what:="January", After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    'Selection.Copy
    
    
    ActiveCell.FormulaR1C1 = "'February 2010"
    

ActiveWorkbook.Close True           'Close and save workbok
    fName = Dir                         'ready next filename
Loop


Application.ScreenUpdating = True
Application.DisplayAlerts = True

MsgBox ("Done")
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Will you be doing this for each month/year? Judging by the fact that this is currently March of 2010, I'm assuming this will be a recurring thing...just asking as if it's recurring you probably want to write it so it doesn't have the month/year hard-coded like you currently have (that way you don't have to change it every month).
 

camandab

Board Regular
Joined
Feb 10, 2010
Messages
79
Thanks for the quick reply! Yes, this will have to be done for each month/year. I wasn't sure how to write it so it wouldn't have to be hard-coded. I would greatly appreciate some guidance on how to do that. :)
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
I did some limited testing on this; it seemed to work for me. If nothing else, it may give you some ideas even if it doesn't work 100% for you:

Code:
Sub asdf()
Dim C As Range, WB As Workbook
Dim fName As String, fOUT As String
Dim PrevMonth As String, PrevYear As String
Dim CurMonth As String, CurYear As String, ShName As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

CurMonth = Month(Date) 'current month
CurYear = Year(Date) 'current year
If CurMonth = 1 Then 'if current month is Jan.
    PrevMonth = 12 'set prev. month to Dec.
    'name to be used for new sheet
    ShName = Format(DateSerial(Year(Date) - 1, PrevMonth, 1), "MMM YY")
Else
    PrevMonth = CurMonth - 1 'otherwise, just get prev. month
    'name to be used for new sheet
    ShName = Format(DateSerial(Year(Date), PrevMonth, 1), "MMM YY")
End If

CurDir "C:\... Recs2010\"
fName = "*.xls"

Do While Len(fName) > 0     'open each file and make sheet changes
    Set WB = Workbooks.Open(fName)
    With WB
        .Sheets(Sheets.Count).Copy after:=Sheets(Sheets.Count)
        With .Sheets(Sheets.Count)
            On Error Resume Next
            .Name = ShName
            If Err.Number <> 0 Then
                If MsgBox("Sheet for " & ShName & " already exists in this file." & _
                vbCrLf & "Skip to next file? If no, macro will end.", _
                vbYesNo + vbExclamation) = vbYes Then
                    WB.Close False 'close file and DO NOT save
                    On Error GoTo 0
                    GoTo NextFile
                Else
                    On Error GoTo 0
                    WB.Close False 'close file and do not save
                    Application.ScreenUpdating = False
                    Application.DisplayAlerts = False
                    Exit Sub
                End If
            End If
            On Error GoTo 0
            Set C = .Cells.Find(what:=Format(PrevMonth, "MMMM"), LookIn:=xlFormulas _
            , LookAt:=xlPart, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
            If Not C Is Nothing Then C = Format(PrevMonth & " " & Year(Date), "MMMM YYYY")
        End With
        .Close True  'Close and save workbok
    End With
NextFile:    fName = Dir 'ready next filename
Loop


Application.ScreenUpdating = True
Application.DisplayAlerts = True

MsgBox ("Done")
End Sub
 

camandab

Board Regular
Joined
Feb 10, 2010
Messages
79

ADVERTISEMENT

This is perfect! Thanks so much Kristy!! :biggrin:
 

camandab

Board Regular
Joined
Feb 10, 2010
Messages
79
I did some limited testing on this; it seemed to work for me. If nothing else, it may give you some ideas even if it doesn't work 100% for you:

Rich (BB code):
Sub asdf()
Dim C As Range, WB As Workbook
Dim fName As String, fOUT As String
Dim PrevMonth As String, PrevYear As String
Dim CurMonth As String, CurYear As String, ShName As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

CurMonth = Month(Date) 'current month
CurYear = Year(Date) 'current year
If CurMonth = 1 Then 'if current month is Jan.
    PrevMonth = 12 'set prev. month to Dec.
    'name to be used for new sheet
    ShName = Format(DateSerial(Year(Date) - 1, PrevMonth, 1), "MMM YY")
Else
    PrevMonth = CurMonth - 1 'otherwise, just get prev. month
    'name to be used for new sheet
    ShName = Format(DateSerial(Year(Date), PrevMonth, 1), "MMM YY")
End If

CurDir "C:\... Recs2010\"
fName = "*.xls"

Do While Len(fName) > 0     'open each file and make sheet changes
    Set WB = Workbooks.Open(fName)
    With WB
        .Sheets(Sheets.Count).Copy after:=Sheets(Sheets.Count)
        With .Sheets(Sheets.Count)
            On Error Resume Next
            .Name = ShName
            If Err.Number <> 0 Then
                If MsgBox("Sheet for " & ShName & " already exists in this file." & _
                vbCrLf & "Skip to next file? If no, macro will end.", _
                vbYesNo + vbExclamation) = vbYes Then
                    WB.Close False 'close file and DO NOT save
                    On Error GoTo 0
                    GoTo NextFile
                Else
                    On Error GoTo 0
                    WB.Close False 'close file and do not save
                    Application.ScreenUpdating = False
                    Application.DisplayAlerts = False
                    Exit Sub
                End If
            End If
            On Error GoTo 0
            Set C = .Cells.Find(what:=Format(PrevMonth, "MMMM"), LookIn:=xlFormulas _
            , LookAt:=xlPart, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
            If Not C Is Nothing Then C = Format(PrevMonth & " " & Year(Date), "MMMM YYYY")
        End With
        .Close True  'Close and save workbok
    End With
NextFile:    fName = Dir 'ready next filename
Loop


Application.ScreenUpdating = True
Application.DisplayAlerts = True

MsgBox ("Done")
End Sub


The part in blue is not working for me as I've run the macro for this month. It changes the sheet name correctly to Mar 10 but the name in that sheet is still the same one for the previous month. I have tried declaring another variable that takes the CurMonth-2 but that didn't seem to work. Any ideas??
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
The part in blue is not working for me as I've run the macro for this month. It changes the sheet name correctly to Mar 10 but the name in that sheet is still the same one for the previous month. I have tried declaring another variable that takes the CurMonth-2 but that didn't seem to work. Any ideas??

Sorry about the late reply--I don't have quite as much time to check the board during my work hours lately (being busy is nice, but sheesh).

At any rate--I'm going to assume that the text being searched for isn't an exact match to what is in the sheet, hence it isn't being changed. Also, it appears to be searching formulas--is that correct? If you're just wanting to change the value contained in a cell change lookin to values, not formulas.

Basically, you'll just need to make sure the text being searched for (currently set as value of PrevMonth formatted as "MMMM"), is what is being used in the sheet. If it doesn't match, it can't change it.

If you'd like I could take a look at your sheet and see if I can't figure out what's going on. It can be hard to tell sometimes without seeing exactly what we're looking at :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,205
Messages
5,623,363
Members
415,969
Latest member
Rey99

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
Top