VBA - Save As Using Cell Reference

[TiffanyMarie264]

Hello! I am still a newborn with writing VBA and there is an element of my code that I cannot get to work correctly.

In a nutshell, a main file with all of the information (we'll call it WB1), my code opens another workbook (a generic template - I'll refer to it as WB2) - pastes the information as values - then saves as a new workbook using a file name I define in my code as "The value in cell I1 in WB1 & the value in a different cell also in WB1".

My file basically generates a separate file (WB2) for each individual claim number...which is linked to a name. I am trying to save WB2 using the name associated with the particular claim number the information in WB2 pertains to followed by the date (Name MMMYY). <--Both of these pieces of information are only contained in WB1.

The date reference is fine because it is referring to a static cell. However, the value in cell I1 contains an Index Match function that updates when the information changes. The first file always saves correctly. However, when the following files save, the file name is not reflecting the change in cell I1. This results in my macro stopping because it asks me if I want to save over the file previously generated, since it has the same name.

Here is the code I am using... (I have highlighted all of the relevant references in red.)
Sub PasteToExportedFile2()
' PasteToExportedFile Macro
'
Dim FileName As String
Dim path As String
Dim User As String
Dim Period As String <----This is the reference that is a static value in a cell...it is working fine.
Dim ClaimRef As Range <-----This is the reference that is an Index Match formula
Dim CarrierClaim As Range
Dim DateData As Range
Dim DataBody As Range
Dim curr As Worksheet
Dim i As Integer

Application.DisplayAlerts = False

User = Worksheets("Claim Numbers").Range("L1").Value
Period = Worksheets("Claim Numbers").Range("L2").TextSet ClaimRef = Worksheets("Working File").Range("I1")Set CarrierClaim = Worksheets("Working File").Range("F1:G1")
Set DateData = Worksheets("Working File").Range("F3:G6")Set DataBody = Worksheets("Working File").Range("B12:Q2011")

path = "W:\FINSHARED\yearend\finrpt\MCCA - Losses\Billing" & "\Claim Output" & User & ""

Set FileName = ClaimRef & " " & Period & ".xls"
Set curr = ActiveSheet
For i = 2 To Range("A300").End(xlUp).row
Cells(i, 1).Copy
Sheets("Data Consolidation").Select
Range("F1").Select
Selection.PasteSpecial Paste:=xlPasteValues
ActiveSheet.Calculate

Sheets("Working File").Select
ActiveSheet.Calculate
CarrierClaim.Select
Selection.Copy

Workbooks.Open FileName:= _
"W:\FINSHARED\yearend\finrpt\MCCA - Losses\Billing\MMS_RFR_Excel_Template.xls"

Range("F1:G1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Windows("MCCA Formatting Test - Raw Data - October Data - New.xlsm").Activate

Sheets("Claim Numbers").Select
ActiveSheet.Calculate

Sheets("Working File").Select
DateData.Select
Selection.Copy
Windows("MMS_RFR_Excel_Template.xls").Activate
Range("F3:G6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("MCCA Formatting Test - Raw Data - October Data - New.xlsm").Activate
Sheets("Working File").Select
DataBody.Select
Application.CutCopyMode = False
Selection.Copy
Windows("MMS_RFR_Excel_Template.xls").Activate
Range("B12:Q2011").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Calculate ActiveWorkbook.SaveAs path & FileName, xlExcel8 <-----This is where I am running into the issue after the first file. The FileName is not updating to reflect the new value in cell I1 when the new information is generated.
Application.DisplayAlerts = True

ActiveWindow.Close

Windows("MCCA Formatting Test - Raw Data - October Data - New.xlsm").Activate

curr.Activate
Next

End Sub
Any help would be greatly appreciated!!! (Also, if there are any ways to lean out my code, please let me know!)

Thank you!


POST MOVED BY MOD


.

 

[Chihiro]

You can try inserting something like below, before you take info from I2.

This basically forces formula to be recalculated before you move on to rest of the code.

Application.Calculate
If Not Application.CalculationState = xlDone Then
    DoEvents
End If

If above doesn't work. I'd suggest you upload sample workbooks with dummy data.

 

[Dr. Demento]

I'm spit-balling here, but I'm curious if you changed your statement defining ClaimRef to this perhaps it might help:

ClaimRef = Worksheets("Working File").Range("I1").value

Although, I think Chihiro was right -- if you step thru the code, on the second iteration, does the cell update to the appropriate value? If not, need to refresh first.

 

[KenU]

@TiffanyMarie264,

I believe the main problem is that the FileName variable is assigned a value outside of the loop (also, you do not need to use "Set" as it is not an object).

Please edit the beginning of your loop to:

For i = 2 To Range("A300").End(xlUp).row
    FileName = ClaimRef & " " & Period & ".xls"

I hope that helps!

Regards,
Ken