VBA: Code MONSTROSITY, EMBARRASSED TO SHOW BUT "NEED HELP".

B

bamaisgreat

Well-known Member
Joined
Jan 23, 2012
Messages
821
Office Version
  1. 365
Platform
  1. Windows
The code I have below is a major overkill but It works except there is a couple of bugs. The first is when I run the code, on the last line((Set rngfil = Range("A20,B20,C20,D20,J20,R20,U20"))) it puts in the "-" in the Range of cells, I dont need that. Each line may not always have data in it and sometimes there may only be a few of the cells with data in them. The reason I chose the "-" is so no cell will be empty to prevent the line of cells from getting out of line. Any help is appreciated, Thanks

Code: 
Sub ArchiveIt()
'next line adds character into empty cell'
Dim rngfil As Range, cell As Range
Dim NR As Long
Set ws1 = ActiveWorkbook.Sheets("JOB FORM")
Set rngfil = Range("A4,B4,C4,D4,J4,R4,U4")
Set rngfil = Range("A5,B5,C5,D5,J5,R5,U5")
Set rngfil = Range("A6,B6,C6,D6,J6,R6,U6")
Set rngfil = Range("A7,B7,C7,D7,J7,R7,U7")
Set rngfil = Range("A8,B8,C8,D8,J8,R8,U8")
Set rngfil = Range("A9,B9,C9,D9,J9,R9,U9")
Set rngfil = Range("A10,B10,C10,D10,J10,R10,U10")
Set rngfil = Range("A11,B11,C11,D11,J11,R11,U11")
Set rngfil = Range("A12,B12,C12,D12,J12,R12,U12")
Set rngfil = Range("A13,B13,C13,D13,J13,R13,U13")
Set rngfil = Range("A14,B14,C14,D14,J14,R14,U14")
Set rngfil = Range("A15,B15,C15,D15,J15,R15,U15")
Set rngfil = Range("A16,B16,C16,D16,J16,R16,U16")
Set rngfil = Range("A17,B17,C17,D17,J17,R17,U17")
Set rngfil = Range("A18,B18,C18,D18,J18,R18,U18")
Set rngfil = Range("A19,B19,C19,D19,J19,R19,U19")
Set rngfil = Range("A20,B20,C20,D20,J20,R20,U20")
For Each cell In rngfil
 If cell.Value = vbNullString Then
 cell.Value = "-"
 End If
 Next cell
Filename = "H:\Burney Table\CUTTING FORMS (Protected by QC)\fmi archive\My Book.xls"
Workbooks.Open (Filename)
 
NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A4").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B4").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C4").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D4").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J4").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R4").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T4").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U4").Value
NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A5").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B5").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C5").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D5").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J5").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R5").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T5").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U5").Value
NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A6").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B6").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C6").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D6").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J6").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R6").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T6").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U6").Value
 NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A7").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B7").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C7").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D7").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J7").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R7").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T7").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U7").Value
 NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A8").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B8").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C8").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D8").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J8").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R8").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T8").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U8").Value
 NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A9").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B9").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C9").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D9").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J9").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R9").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T9").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U9").Value
 NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A10").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B10").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C10").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D10").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J10").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R10").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T10").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U10").Value
 NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A11").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B11").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C11").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D11").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J11").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R11").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T11").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U11").Value
NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A12").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B12").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C12").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D12").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J12").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R12").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T12").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U12").Value
NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A13").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B13").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C13").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D13").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J13").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R13").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T13").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U13").Value
NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A14").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B14").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C14").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D14").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J14").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R14").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T14").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U14").Value
NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A15").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B15").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C15").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D15").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J15").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R15").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T15").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U15").Value
NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A16").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B16").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C16").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D16").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J16").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R16").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T16").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U16").Value
NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A17").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B17").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C17").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D17").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J17").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R17").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T17").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U17").Value
NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A18").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B18").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C18").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D18").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J18").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R18").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T18").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U18").Value
NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A19").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B19").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C19").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D19").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J19").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R19").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T19").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U19").Value
NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A20").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B20").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C20").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D20").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J20").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R20").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T20").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U20").Value

   
    ActiveWorkbook.save
    'ActiveWorkbook.Close
End Sub
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
bamaisgreat,

As is, the first half of your code is resetting rngfill several times. By the time you get to the last set it only represents your row 20 cells.
It then puts - in those cells.

Try changing the top to this......

Code: 
Sub ArchiveIt2()
'next line adds character into empty cell'
Dim rngfil As Range, cell As Range
Dim NR As Long
Set ws1 = ActiveWorkbook.Sheets("JOB FORM")
Set rngfil = Range("A4,B4,C4,D4,J4,R4,U4")
For r = 1 To 16
For Each cell In rngfil.Offset(r, 0)
If cell.Value = vbNullString Then
 cell.Value = "-"
 End If
Next cell
Next r
Filename = "H:\Burney Table\CUTTING FORMS (Protected by QC)\fmi archive\My Book.xls"


'etc  etc  ..

See if that helps.

I have not even attempted to tame the monster that is the rest of the code. :)
 
Upvote 0
bamaisgreat said:
The code I have below is a major overkill but It works except there is a couple of bugs. The first is when I run the code, on the last line((Set rngfil = Range("A20,B20,C20,D20,J20,R20,U20"))) it puts in the "-" in the Range of cells, I dont need that. Each line may not always have data in it and sometimes there may only be a few of the cells with data in them. The reason I chose the "-" is so no cell will be empty to prevent the line of cells from getting out of line. Any help is appreciated, Thanks

Code: 
Sub ArchiveIt()
'next line adds character into empty cell'
Dim rngfil As Range, cell As Range
Dim NR As Long
Set ws1 = ActiveWorkbook.Sheets("JOB FORM")
Set rngfil = Range("A4,B4,C4,D4,J4,R4,U4")
Set rngfil = Range("A5,B5,C5,D5,J5,R5,U5")
Set rngfil = Range("A6,B6,C6,D6,J6,R6,U6")
Set rngfil = Range("A7,B7,C7,D7,J7,R7,U7")
Set rngfil = Range("A8,B8,C8,D8,J8,R8,U8")
Set rngfil = Range("A9,B9,C9,D9,J9,R9,U9")
Set rngfil = Range("A10,B10,C10,D10,J10,R10,U10")
Set rngfil = Range("A11,B11,C11,D11,J11,R11,U11")
Set rngfil = Range("A12,B12,C12,D12,J12,R12,U12")
Set rngfil = Range("A13,B13,C13,D13,J13,R13,U13")
Set rngfil = Range("A14,B14,C14,D14,J14,R14,U14")
Set rngfil = Range("A15,B15,C15,D15,J15,R15,U15")
Set rngfil = Range("A16,B16,C16,D16,J16,R16,U16")
Set rngfil = Range("A17,B17,C17,D17,J17,R17,U17")
Set rngfil = Range("A18,B18,C18,D18,J18,R18,U18")
Set rngfil = Range("A19,B19,C19,D19,J19,R19,U19")
Set rngfil = Range("A20,B20,C20,D20,J20,R20,U20")
For Each cell In rngfil
 If cell.Value = vbNullString Then
 cell.Value = "-"
 End If
 Next cell
Filename = "H:\Burney Table\CUTTING FORMS (Protected by QC)\fmi archive\My Book.xls"
Workbooks.Open (Filename)
 
NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A4").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B4").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C4").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D4").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J4").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R4").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T4").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U4").Value
NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A5").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B5").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C5").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D5").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J5").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R5").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T5").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U5").Value
NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A6").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B6").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C6").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D6").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J6").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R6").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T6").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U6").Value
 NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A7").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B7").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C7").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D7").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J7").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R7").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T7").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U7").Value
 NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A8").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B8").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C8").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D8").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J8").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R8").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T8").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U8").Value
 NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A9").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B9").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C9").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D9").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J9").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R9").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T9").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U9").Value
 NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A10").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B10").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C10").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D10").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J10").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R10").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T10").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U10").Value
 NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A11").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B11").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C11").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D11").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J11").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R11").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T11").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U11").Value
NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A12").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B12").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C12").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D12").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J12").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R12").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T12").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U12").Value
NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A13").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B13").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C13").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D13").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J13").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R13").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T13").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U13").Value
NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A14").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B14").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C14").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D14").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J14").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R14").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T14").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U14").Value
NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A15").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B15").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C15").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D15").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J15").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R15").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T15").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U15").Value
NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A16").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B16").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C16").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D16").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J16").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R16").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T16").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U16").Value
NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A17").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B17").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C17").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D17").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J17").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R17").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T17").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U17").Value
NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A18").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B18").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C18").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D18").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J18").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R18").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T18").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U18").Value
NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A19").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B19").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C19").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D19").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J19").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R19").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T19").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U19").Value
NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Sheet1").Range("A" & NR).Value = ws1.Range("A20").Value
    Sheets("Sheet1").Range("B" & NR).Value = ws1.Range("B20").Value
    Sheets("Sheet1").Range("C" & NR).Value = ws1.Range("C20").Value
    Sheets("Sheet1").Range("D" & NR).Value = ws1.Range("D20").Value
    Sheets("Sheet1").Range("E" & NR).Value = ws1.Range("J20").Value
    Sheets("Sheet1").Range("F" & NR).Value = ws1.Range("R20").Value
    Sheets("Sheet1").Range("G" & NR).Value = ws1.Range("T20").Value
    Sheets("Sheet1").Range("H" & NR).Value = ws1.Range("U20").Value

   
    ActiveWorkbook.save
    'ActiveWorkbook.Close
End Sub
Click to expand...

Oh my Good Lord.

Your code almost cries out "the time is right for Satan to prosper and bless the sin of sodomy"

What were you thinking my friend?
 
Upvote 0
You could try this.
One question: Why is T4:T20 not included in rngfil?
Code: 
Sub ArchiveIt()
'next line adds character into empty cell'
Dim rngfil As Range, cell As Range
Dim NR As Long, I as Long
Set ws1 = ActiveWorkbook.Sheets("JOB FORM")
Set rngfil = Range("A4:D20,J4:J20,R4:R20,U4:U20")
For Each cell In rngfil
     If cell.Value = vbNullString Then
         cell.Value = "-"
     End If
 Next cell
Filename = "H:\Burney Table\CUTTING FORMS (Protected by QC)\fmi archive\My Book.xls"
Workbooks.Open (Filename)
 
NR = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
For I =0 to 15
    Sheets("Sheet1").Range("A" & NR + I).Value = ws1.Range("A" & I+4).Value
    Sheets("Sheet1").Range("B" & NR + I).Value = ws1.Range("B" & I+4).Value
    Sheets("Sheet1").Range("C" & NR + I).Value = ws1.Range("C" & I+4).Value
    Sheets("Sheet1").Range("D" & NR + I).Value = ws1.Range("D" & I+4).Value
    Sheets("Sheet1").Range("E" & NR + I).Value = ws1.Range("J" & I+4).Value
    Sheets("Sheet1").Range("F" & NR + I).Value = ws1.Range("R" & I+4).Value
    Sheets("Sheet1").Range("G" & NR + I).Value = ws1.Range("T" & I+4).Value
    Sheets("Sheet1").Range("H" & NR + I).Value = ws1.Range("U" & I+4).Value
Next I
   
    ActiveWorkbook.save
    'ActiveWorkbook.Close
End Sub
 
Upvote 0
T4:T20 are filled in by the condition of another cell.
The reason for the mess is I need away of archiving about 12 sheets a day and this seemed to do the trick, with the exception of the bugs. All the other post I have read about making a Master Workbook are way to difficult for me to understand.. Thanks for all your answeres.
And Gsistek thanks for the final solution>
 
Upvote 0
I have a small problem with the code. Sometimes there may only be data in row 2,3,4,5 and then the rest are empty below that. Some how your code feals all the rows with "-" instead of just the ones that contain some text.
 
Upvote 0
Snake I cant seem to get it to put in the "-" in the blank cells on each row. With the code you recommended.
 
Upvote 0
Could you explain what this does ??
What does the For r = 1 To 16 do ? What is it counting ?
For r = 1 To 16
For Each cell In rngfil.Offset(r, 0)
If cell.Value = vbNullString Then
cell.Value = "-"
End If
Next cell
Next r
Click to expand...
 
Upvote 0
The best way I can explain this is that it needs to look in one row at a time to see if any of the specified cells are empty and the add the "-".
WHen it comes to the last row that does not have data in the specified cells that will be the end and then it will tranfser info to the other workbook. Thanks again. This is pretty confusing to me
 
Upvote 0
bamaisgreat,

r is just a variable that could be anything.

As used here this for/next loop it is varying the Row element of the .Offset

So your range is initially set at Range("A4,B4..... etc then the loop offsets that range by 1 row at a time from rows 4 down to 20.

20 is 4 offset by 16. The ,0) part of the offset statement is the column offset which in this case is 0 ie no offset.

**********I have just realised a silly mistake **********

it should read
Code: 
For r = 0 To 16
For Each cell In rngfil.Offset(r, 0)
If cell.Value = vbNullString Then 
cell.Value = "-" 
End If
Next cell
Next r

Sorry about that.

Apologies also as I am having to rush this response. I hope it makes sense.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
VBA compare values on different sheets.
Replies
5
Views
62
rlv01
R
B
  • Question
Matching Multiple Header Row Cell Values - Copy the data below to next available row.
Replies
2
Views
251
B-radK
B
J
  • Solved
VBA to compare rows in two different sheets, if match highlight cell
2
Replies
10
Views
664
kevin9999
K
S
  • Question
Date formatting issue sheet to VBA Array to Sheet
Replies
2
Views
156
MARK858
MARK858
T
  • Solved
Multiple columns into 2?
Replies
6
Views
116
Georgiboy
Georgiboy

Forum statistics

Threads
1,215,336
Messages
6,124,338
Members
449,155
Latest member
ravioli44

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