I currently have the code below that runs from row 4 in worksheet "Coupon" until it reaches the last last non-blank row.
Option Explicit
Sub coupon_loop()
Dim lrow As Long, i As Long
Dim fpath As String
Application.ScreenUpdating = False
Application.DisplayStatusBar = True
Application.StatusBar = "Creating CSV Files"
With Worksheets("Coupon")
lrow = .Range("D" & .Rows.Count).End(xlUp).Row
For i = 4 To lrow
Worksheets("Selections").Range("B2").Value = .Range("D" & i).Value
Worksheets("Selections").Range("C2").Value = .Range("E" & i).Value
Worksheets("Selections").Range("E2").Value = .Range("BA" & i).Value
Worksheets("Selections").Range("Z2").Value = .Range("J" & i).Value
Worksheets("Selections").Range("Z4").Value = .Range("K" & i).Value
Worksheets("Selections").Range("G2").Value = .Range("F" & i).Value
Worksheets("Selections").Range("G4").Value = .Range("G" & i).Value
Worksheets("Markets").Range("AB2:AB83").Value = .Range("V" & i).Value
Call calc_values
Worksheets("Events").Range("A2:W2").Copy
Worksheets("EventsTemporary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
Worksheets("Markets").Range("A2:AB83").Copy
Worksheets("MarketsTemporary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
Worksheets("Selections").Range("A2:U356").Copy
Worksheets("SelectionsTemporary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
Next i
End With
fpath = "C:\Documents and Settings\HOME USER\My Documents"
ThisWorkbook.Worksheets("EventsTemporary").Copy
ActiveWorkbook.SaveAs Filename:=fpath & "\Events - " & Format(Now, "yyyy-mm-dd hh-mm-ss") & ".csv", FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close SaveChanges:=True
ThisWorkbook.Worksheets("MarketsTemporary").Copy
ActiveWorkbook.SaveAs Filename:=fpath & "\Markets - " & Format(Now, "yyyy-mm-dd hh-mm-ss") & ".csv", FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close SaveChanges:=True
ThisWorkbook.Worksheets("SelectionsTemporary").Copy
ActiveWorkbook.SaveAs Filename:=fpath & "\Selections - " & Format(Now, "yyyy-mm-dd hh-mm-ss") & ".csv", FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close SaveChanges:=True
lrow = ThisWorkbook.Worksheets("EventsTemporary").Range("A" & Rows.Count).End(xlUp).Row
ThisWorkbook.Worksheets("EventsTemporary").Range("A2:W" & lrow).ClearContents
lrow = ThisWorkbook.Worksheets("MarketsTemporary").Range("A" & Rows.Count).End(xlUp).Row
ThisWorkbook.Worksheets("MarketsTemporary").Range("A2:AD" & lrow).ClearContents
lrow = ThisWorkbook.Worksheets("SelectionsTemporary").Range("A" & Rows.Count).End(xlUp).Row
ThisWorkbook.Worksheets("SelectionsTemporary").Range("A2:Y" & lrow).ClearContents
ThisWorkbook.Worksheets("Coupon").Activate
ThisWorkbook.Worksheets("Coupon").Range("A1").Select
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub
I want to make a tweak to this so that it is more optimized, not doing unnecessary work any runs much more quickly. What I would like to do is to only run this part of the code
With Worksheets("Coupon")
lrow = .Range("D" & .Rows.Count).End(xlUp).Row
For i = 4 To lrow
Worksheets("Selections").Range("B2").Value = .Range("D" & i).Value
Worksheets("Selections").Range("C2").Value = .Range("E" & i).Value
Worksheets("Selections").Range("E2").Value = .Range("BA" & i).Value
Worksheets("Selections").Range("Z2").Value = .Range("J" & i).Value
Worksheets("Selections").Range("Z4").Value = .Range("K" & i).Value
Worksheets("Selections").Range("G2").Value = .Range("F" & i).Value
Worksheets("Selections").Range("G4").Value = .Range("G" & i).Value
Worksheets("Markets").Range("AB2:AB83").Value = .Range("V" & i).Value
Call calc_values
Worksheets("Events").Range("A2:W2").Copy
Worksheets("EventsTemporary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
Worksheets("Markets").Range("A2:AB83").Copy
Worksheets("MarketsTemporary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
Worksheets("Selections").Range("A2:U356").Copy
Worksheets("SelectionsTemporary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
Next i
End With
If the values in columns J and K are not equal to columns BB and BC. If they are then skip this loop and move on to the next row.
Does this all make sense?
Bookmarks