I revamped this macro yesterday and starting testing. It's been giving me strange problems with lines that have been working all along, like erroring on a UsedRange.Rows.Count line that has been here since the beginning and always worked - and that's just one example. Now it stops down at my "With wsCost" section. When I hover the mouse over each of the variables, the data is in there, and the correct row is selected on the worksheet. But instead of putting the variable data in the row, it errors out. I tried skipping over each and hitting run, and each one of them do the same. Within the workbook is a user form, and the code within the user form uses instances of ".Cells(lRow, 1).Value = Me.txtPart.Value." Near the end of this macro, it calls up the macro below, so I used the same variables but changed Me to the name of the form. What needs to change with this? And if you notice anything you would do different with this macro to improve the speed I'd love suggestions!
Code:
Sub CostChangeForm()
'
'
Dim LastPN As Range, CurrCost As Double, CurrDate As Date, RemVol As Double, AnnDem As Double, OneYr As Double, MinDate As Double, wsCost As Worksheet, NoOfRows As Long, NoOfRowsA As Long, NoOfRowsB As Long, NoOfRowsC As Long, wsForm As Worksheet, LastRowInc As Range, LastRowFrm As Range
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
' open data files
Workbooks.Open Filename:=ThisWorkbook.Path & "\" & "1.10.2.2.xls"
Workbooks.Open Filename:=ThisWorkbook.Path & "\" & "5.13.3.xls"
Workbooks.Open Filename:=ThisWorkbook.Path & "\" & "23.16.xls"
Workbooks.Open Filename:=ThisWorkbook.Path & "\" & "2011 PSNA Purchasing Cost Change Log.xls"
' Activate Log
Windows("2011 PSNA Purchasing Cost Change Log.xls").Activate
Sheets("External Cost Changes").Select
' New Project Number
Set LastPN = Range("D:D").Find("Subtotal Actual").Offset(0, -3)
Set LastPN = LastPN.End(xlUp)
LastPN.Offset(1).Value = Right(frmPartLoc.cboDataYr.Value, 2) & Right(frmPartLoc.cboBuyer.Value, 1) & "-" & Right(LastPN, 3) + 1
' Activate 1.10.2.2, find most recent date, cost for that date
Windows("1.10.2.2.xls").Activate
NoOfRowsA = ActiveSheet.UsedRange.Rows.Count
myFileName = frmPartLoc.txtPart.Value & "_" & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & "_" & Format(Date, "m.d.yyyy") & ".xls"
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & myFileName
Range("K22").Formula = "=MAX(E4:E" & NoOfRowsA & ")"
Range("L22").Formula = "=VLOOKUP(K22,E4:I" & NoOfRowsA & ",5,0)"
CurrCost = Range("L22").Value
CurrDate = Range("K22").Value
ActiveWindow.Close
' Activate 23.16, format data
Windows("23.16.xls").Activate
myFileName = frmPartLoc.txtPart.Value & "_" & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & "_" & Format(Date, "m.d.yyyy") & ".xls"
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & myFileName
NoOfRowsB = ActiveSheet.UsedRange.Rows.Count
Range("J1").Select
ActiveCell.FormulaR1C1 = "1"
Range("J1").Select
Selection.Copy
Range("A2:E" & NoOfRowsB).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Columns("A:A").Select
Application.CutCopyMode = False
Selection.NumberFormat = "m/d/yy;@"
Range("A1").Select
Cells.Replace what:="1/0/1900", Replacement:="", lookat:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
' Sum data in col B
Range("J11").Formula = "=SUM(B2:B" & NoOfRowsB & ")"
RemVol = Range("J11").Value
' Find max date -subtract 1 year- find min date
Range("J12").Formula = "=MAX(A2:A" & NoOfRowsB & ")"
Range("J13").Formula = "=J12-364"
Range("J14").Formula = "=MIN(A2:A" & NoOfRowsB & ")"
OneYr = Range("J13").Value
MinDate = Range("J14").Value
ActiveWindow.Close
' Activate 5.13.3,calculate annual demand
Windows("5.13.3.xls").Activate
myFileName = frmPartLoc.txtPart.Value & "_" & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & "_" & Format(Date, "m.d.yyyy") & ".xls"
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & myFileName
NoOfRowsC = ActiveSheet.UsedRange.Rows.Count
Range("M6").Value = OneYr
Range("M7").Value = MinDate
Range("G1").Select
Application.CutCopyMode = False
Selection.Copy
Range("M4:N4").Select
ActiveSheet.Paste
Range("M5").Value = ">=" & Range("M6").Value
Range("N5").Value = "<=" & Range("N7").Value
Range("M8").Formula = "=DSUM(A1:I" & NoOfRowsC & ",3,M4:N5)"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("M9").Value = RemVol
Range("M10").Formula = "=SUM(M8:M9)"
AnnDem = Range("M10").Value
ActiveWindow.Close
' Activate Cost Change Form, add data
Windows("Cost Change Form.xls").Activate
Sheets("Less Than $1k Increase").Select
Set wsCost = Worksheets("Less Than $1k Increase")
Range("A3").End(xlDown).Offset(1, 0).Select
Set LastRowFrm = ActiveCell.Rows
Range("M2").Value = LastPN
With wsCost
.Cells(LastRowFrm, 1).Value = frmPartLoc.txtSupplier.Value
.Cells(LastRowFrm, 2).Value = frmPartLoc.txtPart.Value
.Cells(LastRowFrm, 3).Value = frmPartLoc.txtDesc.Value
.Cells(LastRowFrm, 4).Value = CurrCost
.Cells(LastRowFrm, 5).Value = CurrDate
.Cells(LastRowFrm, 6).Value = frmPartLoc.txtCost.Value
.Cells(LastRowFrm, 7).Value = frmPartLoc.txtDate.Value
.Cells(LastRowFrm, 8).Value = AnnDem
.Cells(LastRowFrm, 9).Value = RemVol
.Cells(LastRowFrm, 12).Value = frmPartLoc.txtDetails.Value
End With
ActiveWorkbook.Save
' Delete original file copies
If Dir(ThisWorkbook.Path & "\" & "1.10.2.2.xls") <> "" Then _
Kill ThisWorkbook.Path & "\" & "1.10.2.2.xls"
If Dir(ThisWorkbook.Path & "\" & "5.13.3.xls") <> "" Then _
Kill ThisWorkbook.Path & "\" & "5.13.3.xls"
If Dir(ThisWorkbook.Path & "\" & "23.16.xls") <> "" Then _
Kill ThisWorkbook.Path & "\" & "23.16.xls"
' Activate Cost Change Log, add data
Windows("2011 PSNA Purchasing Cost Change Log.xls").Activate
LastRowInc = Range("D:D").Find("Economic Increases Actual").Offset(0, -3).End(xlUp).Offset(1, 0).Row
LastRowFrm = Range("A:A").Find("Approvals:").End(xlUp).Offset(1, 0).Row
' Savings Log Data
If frmPartLoc.cboType.Value = Savings Then
With wsForm
.Cells(LastRowSav, 2).Value = frmPartLoc.txtPart.Value
.Cells(LastRowSav, 3).Value = frmPartLoc.cboReason.Value
.Cells(LastRowSav, 4).Value = frmPartLoc.txtDetails.Value
End With
End If
' Increase Log Data
If frmPartLoc.cboType.Value = Increase Then
With wsForm
.Cells(LastRowInc, 2).Value = frmPartLoc.txtPart.Value
.Cells(LastRowInc, 3).Value = frmPartLoc.cboReason.Value
.Cells(LastRowInc, 4).Value = frmPartLoc.txtDetails.Value
End With
End If
ActiveWorkbook.Save
ActiveWindow.Close
MyPath = ThisWorkbook.Path & "\"
If BuyerCode = "A" Then
Name MyPath & "11A\New Folder" As MyPath & "11A\" & LastPN
End If
If BuyerCode = "B" Then
Name MyPath & "11B\New Folder" As MyPath & "11B\" & LastPN
End If
If BuyerCode = "D" Then
Name MyPath & "11D\New Folder" As MyPath & "11D\" & LastPN
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub
