L
Legacy 93538
Guest
Hi
I have this macro which was working fine this morning however i added the code highlighted in red and now it keeps getting an error when it gets to a certain file in the folder it is looping through.
<!-- END TEMPLATE: bbcode_code -->I am not sure why it is now not working as it worked fine this morning when i ran it but when i added the line in red it crashes and shows an error saying workbook open failed.
Does anyone understand why? as i can not figure it out why it work before the code was added and isn't now.
Thanks
Jessicaseymour
I have this macro which was working fine this morning however i added the code highlighted in red and now it keeps getting an error when it gets to a certain file in the folder it is looping through.
Code:
Option Explicit
Sub DealerGraphingDraft()
Dim Nrow As Long, Nrow1 As Long, Nrow2 As Long, Nrow3 As Long, Nrow4 As Long, Nrow5 As Long
Dim strF As String, strFldr As String, strFile As String, strFile1 As String, strFile2 As String, Lction As String
Dim strFile3 As String, strFile4 As String, strFile5 As String, i As String, l As String, k As String, g As String
Dim wbResults As Workbook, wbGCT As Workbook, wbNew As Workbook, PWorkbook As Workbook
Application.ScreenUpdating = False: Application.DisplayAlerts = False: Application.EnableEvents = False
'Variable setting
strFldr = "C:\Documents and Settings\SeymourJ\My Documents\Tasks\": Lction = "C:\Documents and Settings\SeymourJ\Desktop\"
strFile = "Graphing_MTH_Actual_Curr_Year*.csv": strFile1 = "Graphing_MTH_Actual_Prev_Year*.csv"
strFile2 = "Graphing_YTD_Actual_Curr_Year*.csv": strFile3 = "Graphing_YTD_Actual_Prev_Year*.csv"
strFile4 = "Graphing_R12_Actual_Curr_Year*.csv": strFile5 = "Graphing_R12_Actual_Prev_Year*.csv"
Nrow = 2: Nrow1 = 2: Nrow2 = 2: Nrow3 = 2: Nrow4 = 2: Nrow5 = 2
i = Range("B7").Value: l = Range("B8").Value
'sorting, filtering and copying the list of dealers codes and names from the Participation document
Set wbGCT = Workbooks.Open(Lction & "GraphingChartTemplate.xlsx")
Set PWorkbook = Application.Workbooks.Open(Lction & "Actual_Participation_04_2011.xls")
PWorkbook.Sheets(1).Select
Range("C1:N1").AutoFilter Field:=i, Criteria1:="<>"
PWorkbook.Sheets(1).Range("A2:A1000").Copy Destination:=wbGCT.Sheets("Graphing").Range("A3")
With Sheets(1)
k = WorksheetFunction.CountA(.Range("A:A"))
.Range("A2:AQ" & k).Sort Key1:=.Columns("B"), Order1:=xlAscending
End With
PWorkbook.Sheets(1).Range("B2:B" & k).Copy Destination:=wbGCT.Sheets("Graphing").Range("D3")
PWorkbook.Sheets(1).Range("A2:A1000").Copy Destination:=wbGCT.Sheets("Graphing").Range("C3")
PWorkbook.Close
'Add dates to template
ActiveWorkbook.Sheets("Settings").Select
Range("B6").Value = i
Range("B7").Value = l
'Create new workbook
Set wbNew = Application.Workbooks.Add
Sheets.Add.Name = ("MTH"): Sheets.Add.Name = ("MTHPrevious"): Sheets.Add.Name = ("YTD")
Sheets.Add.Name = ("YTDPrevious"): Sheets.Add.Name = ("R12"): Sheets.Add.Name = ("R12Previous")
'Loop to go through the folder, open the files and copy the data
ChDir strFldr
strF = Dir("Graphing_*_Actual_*_Year*.csv")
Do While strF <> ""
Set wbResults = Workbooks.Open(strFldr & "\" & strF)
wbResults.Sheets(1).Range("A2:M15").Copy
'If statement to paste the data into the correct sheet
If wbResults.Name Like strFile Then
wbNew.Sheets("MTH").Cells(Nrow, 2).PasteSpecial
Nrow = Nrow + 14
ElseIf wbResults.Name Like strFile1 Then
wbNew.Sheets("MTHPrevious").Cells(Nrow1, 2).PasteSpecial
Nrow1 = Nrow1 + 14
ElseIf wbResults.Name Like strFile2 Then
wbNew.Sheets("YTD").Cells(Nrow2, 2).PasteSpecial
Nrow2 = Nrow2 + 14
ElseIf wbResults.Name Like strFile3 Then
wbNew.Sheets("YTDPrevious").Cells(Nrow3, 2).PasteSpecial
Nrow3 = Nrow3 + 14
ElseIf wbResults.Name Like strFile4 Then
wbNew.Sheets("R12").Cells(Nrow4, 2).PasteSpecial
Nrow4 = Nrow4 + 14
ElseIf wbResults.Name Like strFile5 Then
wbNew.Sheets("R12Previous").Cells(Nrow5, 2).PasteSpecial
Nrow5 = Nrow5 + 14
End If
wbResults.Close SaveChanges:=False
Application.StatusBar = strF
strF = Dir
Loop
'Copying data from new workbook into Template
wbNew.Sheets("MTH").Range("B2:N3000").Copy
wbGCT.Sheets("MTH").Range("B2:N3000").PasteSpecial
wbNew.Sheets("MTHPrevious").Range("B2:N3000").Copy
wbGCT.Sheets("MTHPrevious").Range("B2:N3000").PasteSpecial
wbNew.Sheets("YTD").Range("B2:N3000").Copy
wbGCT.Sheets("YTD").Range("B2:N3000").PasteSpecial
wbNew.Sheets("YTDPrevious").Range("B2:N3000").Copy
wbGCT.Sheets("YTDPrevious").Range("B2:N3000").PasteSpecial
wbNew.Sheets("R12").Range("B2:N3000").Copy
wbGCT.Sheets("R12").Range("B2:M3000").PasteSpecial
wbNew.Sheets("R12Previous").Range("B2:N3000").Copy
wbGCT.Sheets("R12Previous").Range("B2:N3000").PasteSpecial
'close new workbook
wbNew.Close
'Set the Dealer Name drop down box
'g = WorksheetFunction.CountA(Sheets("Graphing").Range("E3:E183"))
'Sheets("Charts").Shapes("Drop Down 1").ControlFormat.ListFillRange = "Graphing!$E$3:$E$" & g
'select A1 on all sheetsSave Full version
Sheets("Graphing").Select: Range("A1").Select: Sheets("Settings").Select: Range("A1").Select
Sheets("R12Previous").Select: Range("A1").Select: Sheets("R12").Select: Range("A1").Select
Sheets("YTDPrevious").Select: Range("A1").Select: Sheets("YTD").Select: Range("A1").Select
Sheets("MTHPrevious").Select: Range("A1").Select: Sheets("MTH").Select: Range("A1").Select
Sheets("Charts").Select: Range("A1").Select: Sheets("Home").Select: Range("A1").Select
wbGCT.SaveAs (strFldr & "Executive_AnalysisFull_" & Format(Date, "mm_yyyy"))
'Hide Sheets
Sheets("Graphing").Visible = xlSheetVeryHidden: Sheets("Settings").Visible = xlSheetVeryHidden
Sheets("R12Previous").Visible = xlSheetVeryHidden: Sheets("R12").Visible = xlSheetVeryHidden
Sheets("YTDPrevious").Visible = xlSheetVeryHidden: Sheets("YTD").Visible = xlSheetVeryHidden
Sheets("MTHPrevious").Visible = xlSheetVeryHidden: Sheets("MTH").Visible = xlSheetVeryHidden
'Remove formatting
Sheets("Charts").Select: Cells.Select: Selection.Copy: Selection.PasteSpecial Paste:=xlPasteValues
Sheets("Charts").Select: Range("A1").Select
ActiveSheet.Protect
Sheets("Home").Select: Cells.Select: Selection.Copy: Selection.PasteSpecial Paste:=xlPasteValues
Sheets("Home").Select: Range("A1").Select
'Save dealer version and close
ActiveWorkbook.SaveAs (strFldr & "Executive_Analysis_" & Format(Date, "mm_yyyy"))
ActiveWorkbook.Close
End Sub<!-- END TEMPLATE: bbcode_code -->I am not sure why it is now not working as it worked fine this morning when i ran it but when i added the line in red it crashes and shows an error saying workbook open failed.
Does anyone understand why? as i can not figure it out why it work before the code was added and isn't now.
Thanks
Jessicaseymour
