Stuck on getting a loop to work

[RHTexas]

I am a novice at VBA but was able to put together a macro that serves my needs. The macro works well but I am not able to get it to loop properly. I've attached the files.

RFDS_TEMPLATE_20111130_RAY_Macro is where the macro is stored.

I have the macro opening a file which is also attached in the zip file, "Site Information"

So the gist is I am transfering a ton of information from "Site Information" into a new worksheet and saving it as a cell name in the new worksheet and closing. The code works but stops after the first saved document.

You can see in the macro where I am trying to complete the loop.

Any help will be greatly appreciated. Also, please do not laugh at my skills as this is my first somewhat trying macro.

 

[SkipVought]

hi,

Welcome to Tek-Tips.

Many of us are restricted from downloads by company policy and filters.

Please post your code HERE, as is customary and expected at Tek-Tips.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RHTexas]

Hi Skip,

Thank you very much for responding. I was wondering about the attachment method last night.

Please see code below. I know there has to be a better way to do the below but it actually works ok. I just can't figure out the whole loop issue.

Thanks for helping out!


Sub RFDS_Create()
'
' RFDS_Create Macro
'

'
Application.DisplayAlerts = False

Application.ScreenUpdating = False

referencef = Application.GetOpenFilename(Title:="'Site Information.xlsx'")
ref_file = Application.ActiveWorkbook.Name
reference_dir = Left(referencef, Len(referencef) - Len(ref_file))


'If referencef <> "'Site Information.xlsx'" Then

'Interaction.Beep
'MsgBox ("You did not select a 'UMTS Site Config.xls' !")
'Exit Sub

'End If

Application.ScreenUpdating = False

Workbooks.Open Filename:= _
referencef, _
UpdateLinks:=0


ref_file = Application.ActiveWorkbook.Name
reference_dir = Left(referencef, Len(referencef) - Len(ref_file))


ChDir Left(reference_dir, Len(reference_dir) - 1)
ChDrive Left(reference_dir, Len(reference_dir) - 1)

Application.ScreenUpdating = False

' ///////////////////////////////Selecting all the Data From Site Information.xls and pasting in RFDS Cover Page

Windows("Site Information.xlsx").Activate
Sheets("Site Information").Select
Cells.Select
Selection.Copy

Windows("RFDS_TEMPLATE_20111130_RAY_Macro.xlsm").Activate
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Paste
ActiveSheet.Name = ("Site_Information")


Windows("Site Information.xlsx").Activate
Sheets("Spectrum").Select
Cells.Select
Selection.Copy


Windows("RFDS_TEMPLATE_20111130_RAY_Macro.xlsm").Activate
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Paste
ActiveSheet.Name = ("Spectrum")

Sheets("Site_Information").Select

ActiveSheet.Range("A2").Select

i = 1

Do While Cells(i, 1) <> ""


Windows("RFDS_TEMPLATE_20111130_RAY_Macro.xlsm").Activate

Sheets("Site_Information").Select
Range("A2").Select
Selection.Copy

Sheets("Cover").Select
Range("N32:Z32").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("B2").Select
Selection.Copy

Sheets("Cover").Select
Range("N33:Z33").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("C2").Select
Selection.Copy

Sheets("Cover").Select
Range("N34:Z34").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("D2").Select
Selection.Copy

Sheets("Cover").Select
Range("N35:Z35").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("E2").Select
Selection.Copy

Sheets("Cover").Select
Range("N36:Z36").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("F2").Select
Selection.Copy

Sheets("Cover").Select
Range("N37:Z37").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("G2").Select
Selection.Copy

Sheets("Cover").Select
Range("N38:Z38").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("H2").Select
Selection.Copy

Sheets("Cover").Select
Range("N39:Z39").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("I2").Select
Selection.Copy

Sheets("Cover").Select
Range("N40:Z40").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("J2").Select
Selection.Copy

Sheets("Cover").Select
Range("K44:N44").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("K2").Select
Selection.Copy

Sheets("Cover").Select
Range("K45:N45").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("L2").Select
Selection.Copy

Sheets("Cover").Select
Range("L48:X48").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("M2").Select
Selection.Copy

Sheets("Cover").Select
Range("L49:X49").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("N2").Select
Selection.Copy

Sheets("Cover").Select
Range("L50:X50").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("O2").Select
Selection.Copy

Sheets("Cover").Select
Range("L51:X51").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("P2").Select
Selection.Copy

Sheets("Cover").Select
Range("L54:X54").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("Q2").Select
Selection.Copy

Sheets("Cover").Select
Range("L55:X55").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("R2").Select
Selection.Copy

Sheets("Cover").Select
Range("L56:X56").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("S2").Select
Selection.Copy

Sheets("Cover").Select
Range("L57:X57").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("T2").Select
Selection.Copy

Sheets("Cover").Select
Range("L58:X58").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("U2").Select
Selection.Copy

Sheets("Cover").Select
Range("L60:X60").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("V2").Select
Selection.Copy

Sheets("Cover").Select
Range("V42:X42").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("W2").Select
Selection.Copy

Sheets("Cover").Select
Range("V43:X43").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("X2").Select
Selection.Copy

Sheets("Cover").Select
Range("V44:X44").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("Y2").Select
Selection.Copy

Sheets("Cover").Select
Range("V45:X45").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("Z2").Select
Selection.Copy

Sheets("Cover").Select
Range("AA42:AC42").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("AA2").Select
Selection.Copy

Sheets("Cover").Select
Range("AA43:AC43").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("AB2").Select
Selection.Copy

Sheets("Cover").Select
Range("AA44:AC44").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("AC2").Select
Selection.Copy

Sheets("Cover").Select
Range("AA45:AC45").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("AD2").Select
Selection.Copy

Sheets("Cover").Select
Range("N24:T24").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("AE2").Select
Selection.Copy

Sheets("Cover").Select
Range("N25:T25").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("AF2").Select
Selection.Copy

Sheets("Cover").Select
Range("N26:T26").Select
ActiveSheet.Paste

'//////////////////////////////////////////////////////////////////////Creating Sector IDs

Sheets("Site_Information").Select

GSM850_1 = Range("AG2") & 1
GSM850_2 = Range("AG2") & 2
GSM850_3 = Range("AG2") & 3
GSM1900_1 = Range("AG2") & 7
GSM1900_2 = Range("AG2") & 8
GSM1900_3 = Range("AG2") & 9

If Range("AH2") <> "" And Range("AI2") <> "" Then
Range("AR2").Value = GSM850_1
Range("AS2").Value = GSM850_2
Range("AT2").Value = GSM850_3
Range("AU2").Value = GSM1900_1
Range("AV2").Value = GSM1900_2
Range("AW2").Value = GSM1900_3

ElseIf Range("AH2") = 850 And Range("AI2") = "" Then
Range("AR2").Value = GSM850_1
Range("AS2").Value = GSM850_2
Range("AT2").Value = GSM850_3
Range("AU2").Value = ""
Range("AV2").Value = ""
Range("AW2").Value = ""

ElseIf Range("AH2") = "" And Range("AI2") = 1900 Then
Range("AR2").Value = ""
Range("AS2").Value = ""
Range("AT2").Value = ""
Range("AU2").Value = GSM1900_1
Range("AV2").Value = GSM1900_2
Range("AW2").Value = GSM1900_3

Else
Range("AR2").Value = ""
Range("AS2").Value = ""
Range("AT2").Value = ""
Range("AU2").Value = ""
Range("AV2").Value = ""
Range("AW2").Value = ""
End If

UMTS_1 = Range("AJ2") & 1
UMTS_2 = Range("AJ2") & 2
UMTS_3 = Range("AJ2") & 3
UMTS_4 = Range("AJ2") & 7
UMTS_5 = Range("AJ2") & 8
UMTS_6 = Range("AJ2") & 9
UMTS_7 = Range("AJ2") & "A"
UMTS_8 = Range("AJ2") & "B"
UMTS_9 = Range("AJ2") & "C"
UMTS_10 = Range("AK2") & 1
UMTS_11 = Range("AK2") & 2
UMTS_12 = Range("AK2") & 3
UMTS_13 = Range("AK2") & 7
UMTS_14 = Range("AK2") & 8
UMTS_15 = Range("AK2") & 9
UMTS_16 = Range("AK2") & 1
UMTS_17 = Range("AK2") & 2
UMTS_18 = Range("AK2") & 3
UMTS_19 = Range("AJ2") & 4
UMTS_20 = Range("AJ2") & 5
UMTS_21 = Range("AJ2") & 6
UMTS_22 = Range("AK2") & 4
UMTS_23 = Range("AK2") & 5
UMTS_24 = Range("AK2") & 6



If Range("AL2") = "Austin" And Range("AM2") = 850 Then
Range("AX2").Value = UMTS_1
Range("AY2").Value = UMTS_2
Range("AZ2").Value = UMTS_3


ElseIf Range("AL2") = "San Antonio" And Range("AM2") = 850 Then
Range("AX2").Value = UMTS_1
Range("AY2").Value = UMTS_2
Range("AZ2").Value = UMTS_3

ElseIf Range("AL2") <> "" And Range("AM2") = 1900 Then
Range("AX2").Value = UMTS_4
Range("AY2").Value = UMTS_5
Range("AZ2").Value = UMTS_6

ElseIf Range("AL2") = "Houston" And Range("AM2") = 850 Then
Range("AX2").Value = UMTS_7
Range("AY2").Value = UMTS_8
Range("AZ2").Value = UMTS_9

Else
Range("AX2") = ""
Range("AY2") = ""
Range("AZ2") = ""
End If


If Range("AL2") = "Austin" And Range("AM2") = 850 And Range("AN2") = 850 Then
Range("BA2").Value = UMTS_19
Range("BB2").Value = UMTS_20
Range("BC2").Value = UMTS_21

ElseIf Range("AL2") = "Austin" And Range("AM2") = 850 And Range("AN2") = 1900 Then
Range("BA2").Value = UMTS_4
Range("BB2").Value = UMTS_5
Range("BC2").Value = UMTS_6

ElseIf Range("AL2") = "Austin" And Range("AM2") = 1900 And Range("AN2") = 850 Then
Range("BA2").Value = UMTS_1
Range("BB2").Value = UMTS_2
Range("BC2").Value = UMTS_3

ElseIf Range("AL2") = "San Antonio" And Range("AM2") = 850 And Range("AN2") = 850 Then
Range("BA2").Value = UMTS_19
Range("BB2").Value = UMTS_20
Range("BC2").Value = UMTS_21

ElseIf Range("AL2") = "San Antonio" And Range("AM2") = 850 And Range("AN2") = 1900 Then
Range("BA2").Value = UMTS_4
Range("BB2").Value = UMTS_5
Range("BC2").Value = UMTS_6

ElseIf Range("AL2") = "San Antonio" And Range("AM2") = 1900 And Range("AN2") = 850 Then
Range("BA2").Value = UMTS_1
Range("BB2").Value = UMTS_2
Range("BC2").Value = UMTS_3

ElseIf Range("AL2") = "Houston" And Range("AM2") = 850 And Range("AN2") = 850 Then
Range("BA2").Value = UMTS_19
Range("BB2").Value = UMTS_20
Range("BC2").Value = UMTS_21

ElseIf Range("AL2") = "Houston" And Range("AM2") = 850 And Range("AN2") = 1900 Then
Range("BA2").Value = UMTS_4
Range("BB2").Value = UMTS_5
Range("BC2").Value = UMTS_6

ElseIf Range("AL2") = "Houston" And Range("AM2") = 1900 And Range("AN2") = 850 Then
Range("BA2").Value = UMTS_7
Range("BB2").Value = UMTS_8
Range("BC2").Value = UMTS_9

Else
Range("BA2") = ""
Range("BB2") = ""
Range("BC2") = ""
End If


If Range("AL2") = "Austin" And Range("A02") = 850 Then
Range("BD2").Value = UMTS_10
Range("BE2").Value = UMTS_11
Range("BF2").Value = UMTS_12


ElseIf Range("AL2") = "San Antonio" And Range("AO2") = 850 Then
Range("BD2").Value = UMTS_10
Range("BE2").Value = UMTS_11
Range("BF2").Value = UMTS_12

ElseIf Range("AL2") <> "" And Range("AO2") = 1900 Then
Range("BD2").Value = UMTS_13
Range("BE2").Value = UMTS_14
Range("BF2").Value = UMTS_15

ElseIf Range("AL2") = "Houston" And Range("AO2") = 850 Then
Range("BD2").Value = UMTS_16
Range("BE2").Value = UMTS_17
Range("BF2").Value = UMTS_18

Else
Range("BD2") = ""
Range("BE2") = ""
Range("BF2") = ""
End If

If Range("AL2") = "Austin" And Range("AO2") = 850 And Range("AP2") = 850 Then
Range("BG2").Value = UMTS_22
Range("BG2").Value = UMTS_23
Range("BI2").Value = UMTS_24

ElseIf Range("AL2") = "Austin" And Range("AO2") = 850 And Range("AP2") = 1900 Then
Range("BG2").Value = UMTS_13
Range("BH2").Value = UMTS_14
Range("BI2").Value = UMTS_15

ElseIf Range("AL2") = "Austin" And Range("AO2") = 1900 And Range("AP2") = 850 Then
Range("BG2").Value = UMTS_10
Range("BH2").Value = UMTS_11
Range("BI2").Value = UMTS_12

ElseIf Range("AL2") = "San Antonio" And Range("AO2") = 850 And Range("AP2") = 850 Then
Range("BG2").Value = UMTS_22
Range("BH2").Value = UMTS_23
Range("BI2").Value = UMTS_24

ElseIf Range("AL2") = "San Antonio" And Range("AO2") = 850 And Range("AP2") = 1900 Then
Range("BG2").Value = UMTS_13
Range("BH2").Value = UMTS_14
Range("BI2").Value = UMTS_15

ElseIf Range("AL2") = "San Antonio" And Range("AO2") = 1900 And Range("AP2") = 850 Then
Range("BG2").Value = UMTS_10
Range("BH2").Value = UMTS_11
Range("BI2").Value = UMTS_12

ElseIf Range("AL2") = "Houston" And Range("AO2") = 850 And Range("AP2") = 850 Then
Range("BG2").Value = UMTS_22
Range("BH2").Value = UMTS_23
Range("BI2").Value = UMTS_24

ElseIf Range("AL2") = "Houston" And Range("AO2") = 850 And Range("AP2") = 1900 Then
Range("BG2").Value = UMTS_13
Range("BH2").Value = UMTS_14
Range("BI2").Value = UMTS_15

ElseIf Range("AL2") = "Houston" And Range("AO2") = 1900 And Range("AP2") = 850 Then
Range("BG2").Value = UMTS_10
Range("BH2").Value = UMTS_11
Range("BI2").Value = UMTS_12

Else
Range("BG2") = ""
Range("BH2") = ""
Range("BI2") = ""
End If

LTE_1 = Range("AQ2") & "_7A_1"
LTE_2 = Range("AQ2") & "_7A_2"
LTE_3 = Range("AQ2") & "_7A_3"

If Range("AQ2") <> "" Then
Range("BJ2").Value = LTE_1
Range("BK2").Value = LTE_2
Range("BL2").Value = LTE_3
Else

Range("BJ2").Value = ""
Range("BK2").Value = ""
Range("BL2").Value = ""

End If

'////////////////////////////////////////////Creating Sector 1 Config Sheet

Sheets("Site_Information").Select

Config_1 = Range("BM2").Value
Config_2 = Range("BN2").Value
Config_3 = Range("BO2").Value


Sheets("Sector 1 Config").Select
ActiveSheet.Shapes.SelectAll
Selection.Delete
Cells.Select
Cells.Delete

Sheets("Site_Information").Select

If Range("BM2").Value <> "" Then

Sheets(Config_1).Select
Cells.Select
Selection.Copy
Sheets("Sector 1 Config").Select
Cells.Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("AR1:AW2").Select
Selection.Copy

Sheets("Sector 1 Config").Select
Range("E27:J28").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("AX1:BC2").Select
Selection.Copy

Sheets("Sector 1 Config").Select
Range("E31:J32").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("BD1:BI2").Select
Selection.Copy

Sheets("Sector 1 Config").Select
Range("E35:J36").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("BJ1:BL2").Select
Selection.Copy

Sheets("Sector 1 Config").Select
Range("E38:G39").Select
ActiveSheet.Paste

End If

Sheets("Sector 2 Config").Select
ActiveSheet.Shapes.SelectAll
Selection.Delete
Cells.Select
Cells.Delete

Sheets("Site_Information").Select

If Range("BN2").Value <> "" Then

Sheets(Config_2).Select
Cells.Select
Selection.Copy
Sheets("Sector 2 Config").Select
Cells.Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("AR1:AW2").Select
Selection.Copy

Sheets("Sector 2 Config").Select
Range("E27:J28").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("AX1:BC2").Select
Selection.Copy

Sheets("Sector 2 Config").Select
Range("E31:J32").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("BD1:BI2").Select
Selection.Copy

Sheets("Sector 2 Config").Select
Range("E35:J36").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("BJ1:BL2").Select
Selection.Copy

Sheets("Sector 2 Config").Select
Range("E38:G39").Select
ActiveSheet.Paste

End If

Sheets("Sector 3 Config").Select
ActiveSheet.Shapes.SelectAll
Selection.Delete
Cells.Select
Cells.Delete
Sheets("Site_Information").Select

If Range("BO2").Value <> "" Then

Sheets(Config_3).Select
Cells.Select
Selection.Copy
Sheets("Sector 3 Config").Select
Cells.Select
ActiveSheet.Paste


Sheets("Site_Information").Select
Range("AR1:AW2").Select
Selection.Copy

Sheets("Sector 3 Config").Select
Range("E27:J28").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("AX1:BC2").Select
Selection.Copy

Sheets("Sector 3 Config").Select
Range("E31:J32").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("BD1:BI2").Select
Selection.Copy

Sheets("Sector 3 Config").Select
Range("E35:J36").Select
ActiveSheet.Paste

Sheets("Site_Information").Select
Range("BJ1:BL2").Select
Selection.Copy

Sheets("Sector 3 Config").Select
Range("E38:G39").Select
ActiveSheet.Paste

End If
'/////////////////////////////////////////Creating All Equipment

Sheets(Config_1).Select
Range("A2:C44").Select
Selection.Copy

Sheets("All Equipment").Select
Range("B1145").Select
ActiveSheet.Paste

Range("D45").Select
ActiveCell.FormulaR1C1 = _
"='Sector 1 Config'!R[-9]C[-1]+'Sector 2 Config'!R[-9]C[-1]+'Sector 3 Config'!R[-9]C[-1]"

Range("D46").Select
ActiveCell.FormulaR1C1 = _
"='Sector 1 Config'!R[-9]C[-1]+'Sector 2 Config'!R[-9]C[-1]+'Sector 3 Config'!R[-9]C[-1]"

Range("D47").Select
ActiveCell.FormulaR1C1 = _
"='Sector 1 Config'!R[-9]C[-1]+'Sector 2 Config'!R[-9]C[-1]+'Sector 3 Config'!R[-9]C[-1]"

Range("D48").Select
ActiveCell.FormulaR1C1 = _
"='Sector 1 Config'!R[-9]C[-1]+'Sector 2 Config'!R[-9]C[-1]+'Sector 3 Config'!R[-9]C[-1]"

Range("D49").Select
ActiveCell.FormulaR1C1 = _
"='Sector 1 Config'!R[-9]C[-1]+'Sector 2 Config'!R[-9]C[-1]+'Sector 3 Config'!R[-9]C[-1]"

Range("D50").Select
ActiveCell.FormulaR1C1 = _
"='Sector 1 Config'!R[-9]C[-1]+'Sector 2 Config'!R[-9]C[-1]+'Sector 3 Config'!R[-9]C[-1]"

Range("D51").Select
ActiveCell.FormulaR1C1 = _
"='Sector 1 Config'!R[-9]C[-1]+'Sector 2 Config'!R[-9]C[-1]+'Sector 3 Config'!R[-9]C[-1]"

Range("D52").Select
ActiveCell.FormulaR1C1 = _
"='Sector 1 Config'!R[-9]C[-1]+'Sector 2 Config'!R[-9]C[-1]+'Sector 3 Config'!R[-9]C[-1]"

Range("D53").Select
ActiveCell.FormulaR1C1 = _
"='Sector 1 Config'!R[-9]C[-1]+'Sector 2 Config'!R[-9]C[-1]+'Sector 3 Config'!R[-9]C[-1]"


'////////////////////////////////////////////////////////////////////////Create Regulatory RF Site Form

Sheets("Site_Information").Select
Range("I2").Value = county_1

Sheets("Spectrum").Select
Cells.Select
ActiveSheet.Range("$A$1:$E$898").AutoFilter Field:=3, Criteria1:="=*bexar*" _
, Operator:=xlAnd

Range("E1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("REGULATORY").Select
Range("B38").Select
ActiveSheet.Paste
Application.CutCopyMode = False

'//////////////////////////////////////////////////////////////// Create new Workbook

'Sheets("Cover").Activate
'Range("G15:AC16").Select
'Selection.Copy
'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
' :=False, Transpose:=False
' Application.CutCopyMode = False

Sheets(Array("Cover", "REGULATORY", "All Equipment", "Sector 1 Config", "Sector 2 Config", "Sector 3 Config")).Copy

Sheets("Cover").Select

Name = Range("G15").Value
Date_org = Range("n25").Value
Date_rev = Range("n26").Value

If Date_rev <> "" Then
ActiveWorkbook.SaveAs Filename:=reference_dir & Name & "_" & Date_rev, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

Else

ActiveWorkbook.SaveAs Filename:=reference_dir & Name & "_" & Date_org, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

End If

ActiveWorkbook.Close

i = i + 1

Loop


End Sub

 

[SkipVought]

first, you code is very difficult to follow. You would do youself a favor to STRUCTURE your code using INDENT (TAB) for your control structures, such as...

Option Explcit

Sub atest()
    Dim i As Long
    
    i = 1
        
    Do While Cells(i, 1).Value <> ""
        If SomeCondition Then
            'if SomeCondition is TRUE
            Msgbox "SomeCondition is TRUE"
        Else
            'if SomeCondition is FALSE
            Msgbox "SomeCondition is FALSE"
        End If

        i = i + 1
    Loop
End Sub

Every structure is indented on level and the code within each structure is indented on level.

Second you should DECLARE every variable.

To debug, put a break in your code near the bottom of you loop at the last IF statement. The STEP thru the SaveAs statement and loop to see what's happening.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RHTexas]

Thank you very much for helping I do appreciate your time Skip.

This makes since and I got the loop to work now.

Thank you.

Ray

 

[SkipVought]

Each range should reference a sheet object. This form ASSUMES the ActiveSheet, an assumption I care not to make.

   Sheets(Config_1).Select
    Range("A2:C44").Select
    Selection.Copy
    
    Sheets("All Equipment").Select
    Range("B11:D45").Select
    ActiveSheet.Paste
    
    Range("D45").Select
    ActiveCell.FormulaR1C1 = _
    "='Sector 1 Config'!R[-9]C[-1]+'Sector 2 Config'!R[-9]C[-1]+'Sector 3 Config'!R[-9]C[-1]"
    
    Range("D46").Select
    ActiveCell.FormulaR1C1 = _
    "='Sector 1 Config'!R[-9]C[-1]+'Sector 2 Config'!R[-9]C[-1]+'Sector 3 Config'!R[-9]C[-1]"
    
    Range("D47").Select
    ActiveCell.FormulaR1C1 = _
    "='Sector 1 Config'!R[-9]C[-1]+'Sector 2 Config'!R[-9]C[-1]+'Sector 3 Config'!R[-9]C[-1]"
    
    Range("D48").Select
    ActiveCell.FormulaR1C1 = _
    "='Sector 1 Config'!R[-9]C[-1]+'Sector 2 Config'!R[-9]C[-1]+'Sector 3 Config'!R[-9]C[-1]"
    
    Range("D49").Select
    ActiveCell.FormulaR1C1 = _
    "='Sector 1 Config'!R[-9]C[-1]+'Sector 2 Config'!R[-9]C[-1]+'Sector 3 Config'!R[-9]C[-1]"
        
    Range("D50").Select
    ActiveCell.FormulaR1C1 = _
    "='Sector 1 Config'!R[-9]C[-1]+'Sector 2 Config'!R[-9]C[-1]+'Sector 3 Config'!R[-9]C[-1]"
    
    Range("D51").Select
    ActiveCell.FormulaR1C1 = _
    "='Sector 1 Config'!R[-9]C[-1]+'Sector 2 Config'!R[-9]C[-1]+'Sector 3 Config'!R[-9]C[-1]"
    
    Range("D52").Select
    ActiveCell.FormulaR1C1 = _
    "='Sector 1 Config'!R[-9]C[-1]+'Sector 2 Config'!R[-9]C[-1]+'Sector 3 Config'!R[-9]C[-1]"
    
    Range("D53").Select
    ActiveCell.FormulaR1C1 = _
    "='Sector 1 Config'!R[-9]C[-1]+'Sector 2 Config'!R[-9]C[-1]+'Sector 3 Config'!R[-9]C[-1]"

I would rather see something like this...

    Sheets(Config_1).Range("A2:C44").Copy

    With Sheets("All Equipment")
        .Range("B11:D45").PasteSpecial xlPasteAll
    
        .Range(.Range("D45"), .Range("D53")).FormulaR1C1 = _
    "='Sector 1 Config'!R[-9]C[-1]+'Sector 2 Config'!R[-9]C[-1]+'Sector 3 Config'!R[-9]C[-1]"
 
    End With

But I see several things that raise questions.

1) The COPY and PASTE ranges are DIFFERENT SIZES???

2) The macro is TOO BIG. I'd break it into smaller manageable procedures that make sense and call them from a MAIN sub.

3) I cannot determine what your procedure is doing, but the formula references give me a hint that you have too many specific references where you may be able to use more general lookup technigues. In other words, it is a better approch to use a VLOOKUP or INDEX&MATCH rather than hard reference like ='Sector 1 Config'!C36. and too many COPY 'n' PASTE chunks.

Do you find yourself MODIFYING the macro to accomodate new range locations? A well designed application should not require such periodic maintenance. Just guessing.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}