Macro wipes out Data Validation data

J

Jenawade

Board Regular
Joined
Apr 8, 2002
Messages
231
Macro wipes out Data Validation data

Until last week I used the macro below to increment the row number in the formulas contained in a form. The form would fill in with the data in row one on a data sheet, save, fill in with row 2, save, batch creating the forms. This worked until the last revision of the master form. If we don't use the latest version our files are returned to us. I updated to the new form, and everything has gone downhill since. When I tried to run the macro, the data validation (in many cells, and this happens to all of them) is wiped out. Not the list itself, but I end up with blank drop down boxes with just one empty row.

Code: 
Sub Make_Batch_Forms()
        
      Workbooks.Open Filename:="C:\Users\SmithJ\Desktop\PQs\PQs.xlsm"
      
      startval = 1
      NumToFill = 8
      For cnt = 1 To NumToFill
    
    Workbooks.Open Filename:="C:\Users\SmithJ\Desktop\PQs\PLQ Master Form.xls"
    
    Rows("4:59").Replace What:="$1", Replacement:="$" & cnt, LookAt:=xlPart
         
         ActiveWorkbook.SaveCopyAs Filename:="C:\Users\SmithJ\Desktop\PQs\" & cnt & ".xls"
         startval = startval + cnt

      Next cnt
      
End Sub

I tried recreating the formulas from scratch with the same result. I instead wiped out the formulas and moved to a 'this range equals this range value' macro, like the first one below. Same result with the data validation. I thought, if I can at least capture the data in the other cells, I'll make the choices from the drop down boxes manually for each form. So I wiped out the lines of code referring to the cells that contain the data validation (the final code below). They're still getting wiped out.

Finally, I thought I would just record a macro, change the selection in a few data validation cells and go from there. I get a blank macro. -?-

Any suggestions? Is something in the code causing this? A setting somewhere? My computer has gone loco?

Code: 
Sub CreatePQs()
      
   Workbooks.Open Filename:="C:\Users\SmithJ\Desktop\PQs\PLQ Master Form.xls"

    Windows("PLQ Master Form.xls").Activate
    Sheets("PLQ Form").Select
    Sheets.Add
    Windows("PQs.xlsm").Activate
    Rows("1:50").Select
    Selection.Copy
    Windows("PLQ Master Form.xls").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sheet1").Name = "PQ Data"
    Sheets("PLQ Form").Select

      startval = 1
      NumToFill = 9
      For cnt = 1 To NumToFill

Sheets("PLQ Form").Range("J4") =Sheets("PQ Data").Range("B" & cnt).Value
Sheets("PLQ Form").Range("J5") =Sheets("PQ Data").Range("C" & cnt).Value
Sheets("PLQ Form").Range("J6") =Sheets("PQ Data").Range("D" & cnt).Value
Sheets("PLQ Form").Range("J7") =Sheets("PQ Data").Range("E" & cnt).Value
Sheets("PLQ Form").Range("J8") =Sheets("PQ Data").Range("F" & cnt).Value
Sheets("PLQ Form").Range("J9") =Sheets("PQ Data").Range("G" & cnt).Value
Sheets("PLQ Form").Range("J10") =Sheets("PQ Data").Range("H" & cnt).Value
Sheets("PLQ Form").Range("J11") =Sheets("PQ Data").Range("I" & cnt).Value
Sheets("PLQ Form").Range("J12") =Sheets("PQ Data").Range("J" & cnt).Value
Sheets("PLQ Form").Range("J13") =Sheets("PQ Data").Range("K" & cnt).Value
Sheets("PLQ Form").Range("J14") =Sheets("PQ Data").Range("L" & cnt).Value
Sheets("PLQ Form").Range("J15") =Sheets("PQ Data").Range("M" & cnt).Value
Sheets("PLQ Form").Range("J16") =Sheets("PQ Data").Range("N" & cnt).Value
Sheets("PLQ Form").Range("J17") =Sheets("PQ Data").Range("O" & cnt).Value
Sheets("PLQ Form").Range("J18") =Sheets("PQ Data").Range("P" & cnt).Value
Sheets("PLQ Form").Range("N4") =Sheets("PQ Data").Range("Q" & cnt).Value
Sheets("PLQ Form").Range("N5") =Sheets("PQ Data").Range("R" & cnt).Value
Sheets("PLQ Form").Range("N6") =Sheets("PQ Data").Range("S" & cnt).Value
Sheets("PLQ Form").Range("N7") =Sheets("PQ Data").Range("T" & cnt).Value
Sheets("PLQ Form").Range("N8") =Sheets("PQ Data").Range("U" & cnt).Value
Sheets("PLQ Form").Range("N9") =Sheets("PQ Data").Range("V" & cnt).Value
Sheets("PLQ Form").Range("N10") =Sheets("PQ Data").Range("W" & cnt).Value
Sheets("PLQ Form").Range("N11") =Sheets("PQ Data").Range("X" & cnt).Value
Sheets("PLQ Form").Range("S14") =Sheets("PQ Data").Range("Y" & cnt).Value
Sheets("PLQ Form").Range("N13") =Sheets("PQ Data").Range("Z" & cnt).Value
Sheets("PLQ Form").Range("N14") =Sheets("PQ Data").Range("AA" & cnt).Value
Sheets("PLQ Form").Range("N15") =Sheets("PQ Data").Range("AB" & cnt).Value
Sheets("PLQ Form").Range("S4") =Sheets("PQ Data").Range("AC" & cnt).Value
Sheets("PLQ Form").Range("S5") =Sheets("PQ Data").Range("AD" & cnt).Value
Sheets("PLQ Form").Range("S6") =Sheets("PQ Data").Range("AE" & cnt).Value
Sheets("PLQ Form").Range("S7") =Sheets("PQ Data").Range("AF" & cnt).Value
Sheets("PLQ Form").Range("S8") =Sheets("PQ Data").Range("AG" & cnt).Value
Sheets("PLQ Form").Range("S9") =Sheets("PQ Data").Range("AH" & cnt).Value
Sheets("PLQ Form").Range("S10") =Sheets("PQ Data").Range("AI" & cnt).Value
Sheets("PLQ Form").Range("S11") =Sheets("PQ Data").Range("AJ" & cnt).Value
Sheets("PLQ Form").Range("S12") =Sheets("PQ Data").Range("AK" & cnt).Value
Sheets("PLQ Form").Range("S13") =Sheets("PQ Data").Range("AL" & cnt).Value
Sheets("PLQ Form").Range("S14") =Sheets("PQ Data").Range("AM" & cnt).Value
Sheets("PLQ Form").Range("S15") =Sheets("PQ Data").Range("AN" & cnt).Value
Sheets("PLQ Form").Range("S16") =Sheets("PQ Data").Range("AO" & cnt).Value
Sheets("PLQ Form").Range("S17") =Sheets("PQ Data").Range("AP" & cnt).Value
Sheets("PLQ Form").Range("C49") =Sheets("PQ Data").Range("AQ" & cnt).Value
Sheets("PLQ Form").Range("D49") =Sheets("PQ Data").Range("AR" & cnt).Value
Sheets("PLQ Form").Range("E49") =Sheets("PQ Data").Range("AS" & cnt).Value
Sheets("PLQ Form").Range("F49") =Sheets("PQ Data").Range("AT" & cnt).Value
Sheets("PLQ Form").Range("G49") =Sheets("PQ Data").Range("AU" & cnt).Value
Sheets("PLQ Form").Range("H49") =Sheets("PQ Data").Range("AV" & cnt).Value
Sheets("PLQ Form").Range("I49") =Sheets("PQ Data").Range("AW" & cnt).Value
Sheets("PLQ Form").Range("J49") =Sheets("PQ Data").Range("AX" & cnt).Value
Sheets("PLQ Form").Range("K49") =Sheets("PQ Data").Range("AY" & cnt).Value
Sheets("PLQ Form").Range("M49") =Sheets("PQ Data").Range("AZ" & cnt).Value
Sheets("PLQ Form").Range("N49") =Sheets("PQ Data").Range("BA" & cnt).Value
Sheets("PLQ Form").Range("O49") =Sheets("PQ Data").Range("BB" & cnt).Value
Sheets("PLQ Form").Range("C50") =Sheets("PQ Data").Range("BC" & cnt).Value
Sheets("PLQ Form").Range("D50") =Sheets("PQ Data").Range("BD" & cnt).Value
Sheets("PLQ Form").Range("E50") =Sheets("PQ Data").Range("BE" & cnt).Value
Sheets("PLQ Form").Range("F50") =Sheets("PQ Data").Range("BF" & cnt).Value
Sheets("PLQ Form").Range("G50") =Sheets("PQ Data").Range("BG" & cnt).Value
Sheets("PLQ Form").Range("H50") =Sheets("PQ Data").Range("BH" & cnt).Value
Sheets("PLQ Form").Range("I50") =Sheets("PQ Data").Range("BI" & cnt).Value
Sheets("PLQ Form").Range("J50") =Sheets("PQ Data").Range("BJ" & cnt).Value
Sheets("PLQ Form").Range("K50") =Sheets("PQ Data").Range("BK" & cnt).Value
Sheets("PLQ Form").Range("M50") =Sheets("PQ Data").Range("BL" & cnt).Value
Sheets("PLQ Form").Range("N50") =Sheets("PQ Data").Range("BM" & cnt).Value
Sheets("PLQ Form").Range("O50") =Sheets("PQ Data").Range("BN" & cnt).Value
Sheets("PLQ Form").Range("C51") =Sheets("PQ Data").Range("BO" & cnt).Value
Sheets("PLQ Form").Range("D51") =Sheets("PQ Data").Range("BP" & cnt).Value
Sheets("PLQ Form").Range("E51") =Sheets("PQ Data").Range("BQ" & cnt).Value
Sheets("PLQ Form").Range("F51") =Sheets("PQ Data").Range("BR" & cnt).Value
Sheets("PLQ Form").Range("G51") =Sheets("PQ Data").Range("BS" & cnt).Value
Sheets("PLQ Form").Range("H51") =Sheets("PQ Data").Range("BT" & cnt).Value
Sheets("PLQ Form").Range("I51") =Sheets("PQ Data").Range("BU" & cnt).Value
Sheets("PLQ Form").Range("J51") =Sheets("PQ Data").Range("BV" & cnt).Value
Sheets("PLQ Form").Range("K51") =Sheets("PQ Data").Range("BW" & cnt).Value
Sheets("PLQ Form").Range("M51") =Sheets("PQ Data").Range("BX" & cnt).Value
Sheets("PLQ Form").Range("N51") =Sheets("PQ Data").Range("BY" & cnt).Value
Sheets("PLQ Form").Range("O51") =Sheets("PQ Data").Range("BZ" & cnt).Value
Sheets("PLQ Form").Range("C52") =Sheets("PQ Data").Range("CA" & cnt).Value
Sheets("PLQ Form").Range("D52") =Sheets("PQ Data").Range("CB" & cnt).Value
Sheets("PLQ Form").Range("E52") =Sheets("PQ Data").Range("CC" & cnt).Value
Sheets("PLQ Form").Range("F52") =Sheets("PQ Data").Range("CD" & cnt).Value
Sheets("PLQ Form").Range("G52") =Sheets("PQ Data").Range("CE" & cnt).Value
Sheets("PLQ Form").Range("H52") =Sheets("PQ Data").Range("CF" & cnt).Value
Sheets("PLQ Form").Range("I52") =Sheets("PQ Data").Range("CG" & cnt).Value
Sheets("PLQ Form").Range("J52") =Sheets("PQ Data").Range("CH" & cnt).Value
Sheets("PLQ Form").Range("K52") =Sheets("PQ Data").Range("CI" & cnt).Value
Sheets("PLQ Form").Range("M52") =Sheets("PQ Data").Range("CJ" & cnt).Value
Sheets("PLQ Form").Range("N52") =Sheets("PQ Data").Range("CK" & cnt).Value
Sheets("PLQ Form").Range("O52") =Sheets("PQ Data").Range("CL" & cnt).Value
Sheets("PLQ Form").Range("C53") =Sheets("PQ Data").Range("CM" & cnt).Value
Sheets("PLQ Form").Range("D53") =Sheets("PQ Data").Range("CN" & cnt).Value
Sheets("PLQ Form").Range("E53") =Sheets("PQ Data").Range("CO" & cnt).Value
Sheets("PLQ Form").Range("F53") =Sheets("PQ Data").Range("CP" & cnt).Value
Sheets("PLQ Form").Range("G53") =Sheets("PQ Data").Range("CQ" & cnt).Value
Sheets("PLQ Form").Range("H53") =Sheets("PQ Data").Range("CR" & cnt).Value
Sheets("PLQ Form").Range("I53") =Sheets("PQ Data").Range("CS" & cnt).Value
Sheets("PLQ Form").Range("J53") =Sheets("PQ Data").Range("CT" & cnt).Value
Sheets("PLQ Form").Range("K53") =Sheets("PQ Data").Range("CU" & cnt).Value
Sheets("PLQ Form").Range("M53") =Sheets("PQ Data").Range("CV" & cnt).Value
Sheets("PLQ Form").Range("N53") =Sheets("PQ Data").Range("CW" & cnt).Value
Sheets("PLQ Form").Range("O53") =Sheets("PQ Data").Range("CX" & cnt).Value
Sheets("PLQ Form").Range("C54") =Sheets("PQ Data").Range("CY" & cnt).Value
Sheets("PLQ Form").Range("D54") =Sheets("PQ Data").Range("CZ" & cnt).Value
Sheets("PLQ Form").Range("E54") =Sheets("PQ Data").Range("DA" & cnt).Value
Sheets("PLQ Form").Range("F54") =Sheets("PQ Data").Range("DB" & cnt).Value
Sheets("PLQ Form").Range("G54") =Sheets("PQ Data").Range("DC" & cnt).Value
Sheets("PLQ Form").Range("H54") =Sheets("PQ Data").Range("DD" & cnt).Value
Sheets("PLQ Form").Range("I54") =Sheets("PQ Data").Range("DE" & cnt).Value
Sheets("PLQ Form").Range("J54") =Sheets("PQ Data").Range("DF" & cnt).Value
Sheets("PLQ Form").Range("K54") =Sheets("PQ Data").Range("DG" & cnt).Value
Sheets("PLQ Form").Range("M54") =Sheets("PQ Data").Range("DH" & cnt).Value
Sheets("PLQ Form").Range("N54") =Sheets("PQ Data").Range("DI" & cnt).Value
Sheets("PLQ Form").Range("O54") =Sheets("PQ Data").Range("DJ" & cnt).Value
Sheets("PLQ Form").Range("C55") =Sheets("PQ Data").Range("DK" & cnt).Value
Sheets("PLQ Form").Range("D55") =Sheets("PQ Data").Range("DL" & cnt).Value
Sheets("PLQ Form").Range("E55") =Sheets("PQ Data").Range("DM" & cnt).Value
Sheets("PLQ Form").Range("F55") =Sheets("PQ Data").Range("DN" & cnt).Value
Sheets("PLQ Form").Range("G55") =Sheets("PQ Data").Range("DO" & cnt).Value
Sheets("PLQ Form").Range("H55") =Sheets("PQ Data").Range("DP" & cnt).Value
Sheets("PLQ Form").Range("I55") =Sheets("PQ Data").Range("DQ" & cnt).Value
Sheets("PLQ Form").Range("J55") =Sheets("PQ Data").Range("DR" & cnt).Value
Sheets("PLQ Form").Range("K55") =Sheets("PQ Data").Range("DS" & cnt).Value
Sheets("PLQ Form").Range("M55") =Sheets("PQ Data").Range("DT" & cnt).Value
Sheets("PLQ Form").Range("N55") =Sheets("PQ Data").Range("DU" & cnt).Value
Sheets("PLQ Form").Range("O55") =Sheets("PQ Data").Range("DV" & cnt).Value
Sheets("PLQ Form").Range("C56") =Sheets("PQ Data").Range("DW" & cnt).Value
Sheets("PLQ Form").Range("D56") =Sheets("PQ Data").Range("DX" & cnt).Value
Sheets("PLQ Form").Range("E56") =Sheets("PQ Data").Range("DY" & cnt).Value
Sheets("PLQ Form").Range("F56") =Sheets("PQ Data").Range("DZ" & cnt).Value
Sheets("PLQ Form").Range("G56") =Sheets("PQ Data").Range("EA" & cnt).Value
Sheets("PLQ Form").Range("H56") =Sheets("PQ Data").Range("EB" & cnt).Value
Sheets("PLQ Form").Range("I56") =Sheets("PQ Data").Range("EC" & cnt).Value
Sheets("PLQ Form").Range("J56") =Sheets("PQ Data").Range("ED" & cnt).Value
Sheets("PLQ Form").Range("K56") =Sheets("PQ Data").Range("EE" & cnt).Value
Sheets("PLQ Form").Range("M56") =Sheets("PQ Data").Range("EF" & cnt).Value
Sheets("PLQ Form").Range("N56") =Sheets("PQ Data").Range("EG" & cnt).Value
Sheets("PLQ Form").Range("O56") =Sheets("PQ Data").Range("EH" & cnt).Value
Sheets("PLQ Form").Range("C57") =Sheets("PQ Data").Range("EI" & cnt).Value
Sheets("PLQ Form").Range("D57") =Sheets("PQ Data").Range("EJ" & cnt).Value
Sheets("PLQ Form").Range("E57") =Sheets("PQ Data").Range("EK" & cnt).Value
Sheets("PLQ Form").Range("F57") =Sheets("PQ Data").Range("EL" & cnt).Value
Sheets("PLQ Form").Range("G57") =Sheets("PQ Data").Range("EM" & cnt).Value
Sheets("PLQ Form").Range("H57") =Sheets("PQ Data").Range("EN" & cnt).Value
Sheets("PLQ Form").Range("I57") =Sheets("PQ Data").Range("EO" & cnt).Value
Sheets("PLQ Form").Range("J57") =Sheets("PQ Data").Range("EP" & cnt).Value
Sheets("PLQ Form").Range("K57") =Sheets("PQ Data").Range("EQ" & cnt).Value
Sheets("PLQ Form").Range("M57") =Sheets("PQ Data").Range("ER" & cnt).Value
Sheets("PLQ Form").Range("N57") =Sheets("PQ Data").Range("ES" & cnt).Value
Sheets("PLQ Form").Range("O57") =Sheets("PQ Data").Range("ET" & cnt).Value
Sheets("PLQ Form").Range("C58") =Sheets("PQ Data").Range("EU" & cnt).Value
Sheets("PLQ Form").Range("D58") =Sheets("PQ Data").Range("EV" & cnt).Value
Sheets("PLQ Form").Range("E58") =Sheets("PQ Data").Range("EW" & cnt).Value
Sheets("PLQ Form").Range("F58") =Sheets("PQ Data").Range("EX" & cnt).Value
Sheets("PLQ Form").Range("G58") =Sheets("PQ Data").Range("EY" & cnt).Value
Sheets("PLQ Form").Range("H58") =Sheets("PQ Data").Range("EZ" & cnt).Value
Sheets("PLQ Form").Range("I58") =Sheets("PQ Data").Range("FA" & cnt).Value
Sheets("PLQ Form").Range("J58") =Sheets("PQ Data").Range("FB" & cnt).Value
Sheets("PLQ Form").Range("K58") =Sheets("PQ Data").Range("FC" & cnt).Value
Sheets("PLQ Form").Range("M58") =Sheets("PQ Data").Range("FD" & cnt).Value
Sheets("PLQ Form").Range("N58") =Sheets("PQ Data").Range("FE" & cnt).Value
Sheets("PLQ Form").Range("O58") =Sheets("PQ Data").Range("FF" & cnt).Value

         ActiveWorkbook.SaveCopyAs Filename:="C:\Users\SmithJ\Desktop\PQs\" & cnt & ".xls"

         startval = startval + cnt

      Next cnt

End Sub

Changed to:

Code: 
Sub CreatePQs()
      
   Workbooks.Open Filename:="C:\Users\SmithJ\Desktop\PQs\PLQ Master Form.xls"

    Windows("PLQ Master Form.xls").Activate
    Sheets("PLQ Form").Select
    Sheets.Add
    Windows("PQs.xlsm").Activate
    Rows("1:50").Select
    Selection.Copy
    Windows("PLQ Master Form.xls").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sheet1").Name = "PQ Data"
    Sheets("PLQ Form").Select

      startval = 1
      NumToFill = 9
      For cnt = 1 To NumToFill

Sheets("PLQ Form").Range("J5") =Sheets("PQ Data").Range("C" & cnt).Value
Sheets("PLQ Form").Range("J6") =Sheets("PQ Data").Range("D" & cnt).Value
Sheets("PLQ Form").Range("J7") =Sheets("PQ Data").Range("E" & cnt).Value
Sheets("PLQ Form").Range("J9") =Sheets("PQ Data").Range("G" & cnt).Value
Sheets("PLQ Form").Range("J11") =Sheets("PQ Data").Range("I" & cnt).Value
Sheets("PLQ Form").Range("J12") =Sheets("PQ Data").Range("J" & cnt).Value
Sheets("PLQ Form").Range("J13") =Sheets("PQ Data").Range("K" & cnt).Value
Sheets("PLQ Form").Range("J16") =Sheets("PQ Data").Range("N" & cnt).Value
Sheets("PLQ Form").Range("J17") =Sheets("PQ Data").Range("O" & cnt).Value
Sheets("PLQ Form").Range("J18") =Sheets("PQ Data").Range("P" & cnt).Value
Sheets("PLQ Form").Range("N4") =Sheets("PQ Data").Range("Q" & cnt).Value
Sheets("PLQ Form").Range("N5") =Sheets("PQ Data").Range("R" & cnt).Value
Sheets("PLQ Form").Range("N6") =Sheets("PQ Data").Range("S" & cnt).Value
Sheets("PLQ Form").Range("N7") =Sheets("PQ Data").Range("T" & cnt).Value
Sheets("PLQ Form").Range("N8") =Sheets("PQ Data").Range("U" & cnt).Value
Sheets("PLQ Form").Range("N9") =Sheets("PQ Data").Range("V" & cnt).Value
Sheets("PLQ Form").Range("S14") =Sheets("PQ Data").Range("Y" & cnt).Value
Sheets("PLQ Form").Range("S4") =Sheets("PQ Data").Range("AC" & cnt).Value
Sheets("PLQ Form").Range("S5") =Sheets("PQ Data").Range("AD" & cnt).Value
Sheets("PLQ Form").Range("S6") =Sheets("PQ Data").Range("AE" & cnt).Value
Sheets("PLQ Form").Range("S7") =Sheets("PQ Data").Range("AF" & cnt).Value
Sheets("PLQ Form").Range("S8") =Sheets("PQ Data").Range("AG" & cnt).Value
Sheets("PLQ Form").Range("S9") =Sheets("PQ Data").Range("AH" & cnt).Value
Sheets("PLQ Form").Range("S10") =Sheets("PQ Data").Range("AI" & cnt).Value
Sheets("PLQ Form").Range("S11") =Sheets("PQ Data").Range("AJ" & cnt).Value
Sheets("PLQ Form").Range("S12") =Sheets("PQ Data").Range("AK" & cnt).Value
Sheets("PLQ Form").Range("S16") =Sheets("PQ Data").Range("AO" & cnt).Value
Sheets("PLQ Form").Range("C49") =Sheets("PQ Data").Range("AQ" & cnt).Value
Sheets("PLQ Form").Range("D49") =Sheets("PQ Data").Range("AR" & cnt).Value
Sheets("PLQ Form").Range("E49") =Sheets("PQ Data").Range("AS" & cnt).Value
Sheets("PLQ Form").Range("G49") =Sheets("PQ Data").Range("AU" & cnt).Value
Sheets("PLQ Form").Range("H49") =Sheets("PQ Data").Range("AV" & cnt).Value
Sheets("PLQ Form").Range("I49") =Sheets("PQ Data").Range("AW" & cnt).Value
Sheets("PLQ Form").Range("K49") =Sheets("PQ Data").Range("AY" & cnt).Value
Sheets("PLQ Form").Range("M49") =Sheets("PQ Data").Range("AZ" & cnt).Value
Sheets("PLQ Form").Range("N49") =Sheets("PQ Data").Range("BA" & cnt).Value
Sheets("PLQ Form").Range("O49") =Sheets("PQ Data").Range("BB" & cnt).Value
Sheets("PLQ Form").Range("C50") =Sheets("PQ Data").Range("BC" & cnt).Value
Sheets("PLQ Form").Range("D50") =Sheets("PQ Data").Range("BD" & cnt).Value
Sheets("PLQ Form").Range("E50") =Sheets("PQ Data").Range("BE" & cnt).Value
Sheets("PLQ Form").Range("G50") =Sheets("PQ Data").Range("BG" & cnt).Value
Sheets("PLQ Form").Range("H50") =Sheets("PQ Data").Range("BH" & cnt).Value
Sheets("PLQ Form").Range("I50") =Sheets("PQ Data").Range("BI" & cnt).Value
Sheets("PLQ Form").Range("K50") =Sheets("PQ Data").Range("BK" & cnt).Value
Sheets("PLQ Form").Range("M50") =Sheets("PQ Data").Range("BL" & cnt).Value
Sheets("PLQ Form").Range("N50") =Sheets("PQ Data").Range("BM" & cnt).Value
Sheets("PLQ Form").Range("O50") =Sheets("PQ Data").Range("BN" & cnt).Value
Sheets("PLQ Form").Range("C51") =Sheets("PQ Data").Range("BO" & cnt).Value
Sheets("PLQ Form").Range("D51") =Sheets("PQ Data").Range("BP" & cnt).Value
Sheets("PLQ Form").Range("E51") =Sheets("PQ Data").Range("BQ" & cnt).Value
Sheets("PLQ Form").Range("G51") =Sheets("PQ Data").Range("BS" & cnt).Value
Sheets("PLQ Form").Range("H51") =Sheets("PQ Data").Range("BT" & cnt).Value
Sheets("PLQ Form").Range("I51") =Sheets("PQ Data").Range("BU" & cnt).Value
Sheets("PLQ Form").Range("K51") =Sheets("PQ Data").Range("BW" & cnt).Value
Sheets("PLQ Form").Range("M51") =Sheets("PQ Data").Range("BX" & cnt).Value
Sheets("PLQ Form").Range("N51") =Sheets("PQ Data").Range("BY" & cnt).Value
Sheets("PLQ Form").Range("O51") =Sheets("PQ Data").Range("BZ" & cnt).Value
Sheets("PLQ Form").Range("C52") =Sheets("PQ Data").Range("CA" & cnt).Value
Sheets("PLQ Form").Range("D52") =Sheets("PQ Data").Range("CB" & cnt).Value
Sheets("PLQ Form").Range("E52") =Sheets("PQ Data").Range("CC" & cnt).Value
Sheets("PLQ Form").Range("G52") =Sheets("PQ Data").Range("CE" & cnt).Value
Sheets("PLQ Form").Range("H52") =Sheets("PQ Data").Range("CF" & cnt).Value
Sheets("PLQ Form").Range("I52") =Sheets("PQ Data").Range("CG" & cnt).Value
Sheets("PLQ Form").Range("K52") =Sheets("PQ Data").Range("CI" & cnt).Value
Sheets("PLQ Form").Range("M52") =Sheets("PQ Data").Range("CJ" & cnt).Value
Sheets("PLQ Form").Range("N52") =Sheets("PQ Data").Range("CK" & cnt).Value
Sheets("PLQ Form").Range("O52") =Sheets("PQ Data").Range("CL" & cnt).Value
Sheets("PLQ Form").Range("C53") =Sheets("PQ Data").Range("CM" & cnt).Value
Sheets("PLQ Form").Range("D53") =Sheets("PQ Data").Range("CN" & cnt).Value
Sheets("PLQ Form").Range("E53") =Sheets("PQ Data").Range("CO" & cnt).Value
Sheets("PLQ Form").Range("G53") =Sheets("PQ Data").Range("CQ" & cnt).Value
Sheets("PLQ Form").Range("H53") =Sheets("PQ Data").Range("CR" & cnt).Value
Sheets("PLQ Form").Range("I53") =Sheets("PQ Data").Range("CS" & cnt).Value
Sheets("PLQ Form").Range("K53") =Sheets("PQ Data").Range("CU" & cnt).Value
Sheets("PLQ Form").Range("M53") =Sheets("PQ Data").Range("CV" & cnt).Value
Sheets("PLQ Form").Range("N53") =Sheets("PQ Data").Range("CW" & cnt).Value
Sheets("PLQ Form").Range("O53") =Sheets("PQ Data").Range("CX" & cnt).Value
Sheets("PLQ Form").Range("C54") =Sheets("PQ Data").Range("CY" & cnt).Value
Sheets("PLQ Form").Range("D54") =Sheets("PQ Data").Range("CZ" & cnt).Value
Sheets("PLQ Form").Range("E54") =Sheets("PQ Data").Range("DA" & cnt).Value
Sheets("PLQ Form").Range("G54") =Sheets("PQ Data").Range("DC" & cnt).Value
Sheets("PLQ Form").Range("H54") =Sheets("PQ Data").Range("DD" & cnt).Value
Sheets("PLQ Form").Range("I54") =Sheets("PQ Data").Range("DE" & cnt).Value
Sheets("PLQ Form").Range("J54") =Sheets("PQ Data").Range("DF" & cnt).Value
Sheets("PLQ Form").Range("K54") =Sheets("PQ Data").Range("DG" & cnt).Value
Sheets("PLQ Form").Range("M54") =Sheets("PQ Data").Range("DH" & cnt).Value
Sheets("PLQ Form").Range("N54") =Sheets("PQ Data").Range("DI" & cnt).Value
Sheets("PLQ Form").Range("O54") =Sheets("PQ Data").Range("DJ" & cnt).Value
Sheets("PLQ Form").Range("C55") =Sheets("PQ Data").Range("DK" & cnt).Value
Sheets("PLQ Form").Range("D55") =Sheets("PQ Data").Range("DL" & cnt).Value
Sheets("PLQ Form").Range("E55") =Sheets("PQ Data").Range("DM" & cnt).Value
Sheets("PLQ Form").Range("G55") =Sheets("PQ Data").Range("DO" & cnt).Value
Sheets("PLQ Form").Range("H55") =Sheets("PQ Data").Range("DP" & cnt).Value
Sheets("PLQ Form").Range("I55") =Sheets("PQ Data").Range("DQ" & cnt).Value
Sheets("PLQ Form").Range("J55") =Sheets("PQ Data").Range("DR" & cnt).Value
Sheets("PLQ Form").Range("K55") =Sheets("PQ Data").Range("DS" & cnt).Value
Sheets("PLQ Form").Range("M55") =Sheets("PQ Data").Range("DT" & cnt).Value
Sheets("PLQ Form").Range("N55") =Sheets("PQ Data").Range("DU" & cnt).Value
Sheets("PLQ Form").Range("O55") =Sheets("PQ Data").Range("DV" & cnt).Value
Sheets("PLQ Form").Range("C56") =Sheets("PQ Data").Range("DW" & cnt).Value
Sheets("PLQ Form").Range("D56") =Sheets("PQ Data").Range("DX" & cnt).Value
Sheets("PLQ Form").Range("E56") =Sheets("PQ Data").Range("DY" & cnt).Value
Sheets("PLQ Form").Range("G56") =Sheets("PQ Data").Range("EA" & cnt).Value
Sheets("PLQ Form").Range("H56") =Sheets("PQ Data").Range("EB" & cnt).Value
Sheets("PLQ Form").Range("I56") =Sheets("PQ Data").Range("EC" & cnt).Value
Sheets("PLQ Form").Range("J56") =Sheets("PQ Data").Range("ED" & cnt).Value
Sheets("PLQ Form").Range("K56") =Sheets("PQ Data").Range("EE" & cnt).Value
Sheets("PLQ Form").Range("M56") =Sheets("PQ Data").Range("EF" & cnt).Value
Sheets("PLQ Form").Range("N56") =Sheets("PQ Data").Range("EG" & cnt).Value
Sheets("PLQ Form").Range("O56") =Sheets("PQ Data").Range("EH" & cnt).Value
Sheets("PLQ Form").Range("C57") =Sheets("PQ Data").Range("EI" & cnt).Value
Sheets("PLQ Form").Range("D57") =Sheets("PQ Data").Range("EJ" & cnt).Value
Sheets("PLQ Form").Range("E57") =Sheets("PQ Data").Range("EK" & cnt).Value
Sheets("PLQ Form").Range("G57") =Sheets("PQ Data").Range("EM" & cnt).Value
Sheets("PLQ Form").Range("H57") =Sheets("PQ Data").Range("EN" & cnt).Value
Sheets("PLQ Form").Range("I57") =Sheets("PQ Data").Range("EO" & cnt).Value
Sheets("PLQ Form").Range("J57") =Sheets("PQ Data").Range("EP" & cnt).Value
Sheets("PLQ Form").Range("K57") =Sheets("PQ Data").Range("EQ" & cnt).Value
Sheets("PLQ Form").Range("M57") =Sheets("PQ Data").Range("ER" & cnt).Value
Sheets("PLQ Form").Range("N57") =Sheets("PQ Data").Range("ES" & cnt).Value
Sheets("PLQ Form").Range("O57") =Sheets("PQ Data").Range("ET" & cnt).Value
Sheets("PLQ Form").Range("C58") =Sheets("PQ Data").Range("EU" & cnt).Value
Sheets("PLQ Form").Range("D58") =Sheets("PQ Data").Range("EV" & cnt).Value
Sheets("PLQ Form").Range("E58") =Sheets("PQ Data").Range("EW" & cnt).Value
Sheets("PLQ Form").Range("G58") =Sheets("PQ Data").Range("EY" & cnt).Value
Sheets("PLQ Form").Range("H58") =Sheets("PQ Data").Range("EZ" & cnt).Value
Sheets("PLQ Form").Range("I58") =Sheets("PQ Data").Range("FA" & cnt).Value
Sheets("PLQ Form").Range("J58") =Sheets("PQ Data").Range("FB" & cnt).Value
Sheets("PLQ Form").Range("K58") =Sheets("PQ Data").Range("FC" & cnt).Value
Sheets("PLQ Form").Range("M58") =Sheets("PQ Data").Range("FD" & cnt).Value
Sheets("PLQ Form").Range("N58") =Sheets("PQ Data").Range("FE" & cnt).Value
Sheets("PLQ Form").Range("O58") =Sheets("PQ Data").Range("FF" & cnt).Value

         ActiveWorkbook.SaveCopyAs Filename:="C:\Users\SmithJ\Desktop\PQs\" & cnt & ".xls"

         startval = startval + cnt

      Next cnt

End Sub
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
You must log in or register to reply here.

Similar threads

I
  • Question Question
Advice for slight alteration to existing working code
Replies
1
Views
192
ipbr21054
I
D
  • Solved
VBA to copy only matching data from 2 csv workbooks into the "MasterCSV"
2
Replies
18
Views
883
Peter_SSs
Peter_SSs
I
  • Solved
Subscript out of range when copying values to another sheet
Replies
9
Views
181
ipbr21054
I
S
  • Solved
Open and Copy last Row of All Files in Folder Then Paste to Master
Replies
6
Views
1K
DanteAmor
DanteAmor
C
  • Question Question
VBA: Filter specific data from table then make formula and put result to summary workbook
Replies
1
Views
590
HighAndWilder
H

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top