Help with errors or IF statments

[camandab]

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!

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")

 

[Von Pookie]

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]

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]

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:

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]

This is perfect! Thanks so much Kristy!!

 

[camandab]

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:

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]

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