cav~firez22
Well-known Member
- Joined
- Jun 21, 2006
- Messages
- 543
I have the following code
This part,
How can i make it so it copies more rows, if the data is there?
Code:
Sub Submitissue()
' Step one : open master file and unprotect
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"O:\Templates\Critical Issues Tracking\Critical Issues Tracking_MASTER.xls"
Sheets("Tracking form Combined").Select
With ActiveSheet
.Unprotect "MONICA"
End With
'This will copy hidden cells into another workbook and save that book
Windows("Critical Issues Tracking Form.xls").Activate
Range("O18:AA18").Select
Selection.Copy
Range("O18:AA18").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("O18:AA18").Select
Selection.Copy
Windows("Critical Issues Tracking_MASTER.xls").Activate
Sheets("Tracking form Combined").Select
Range("B" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.DisplayAlerts = False
With ActiveSheet
.Cells.Locked = True
.Cells.FormulaHidden = False
.Protect "MONICA", DrawingObjects:=False, Contents:=True, Scenarios:=False
End With
ActiveWorkbook.Save
ActiveWorkbook.Close
Windows("Critical Issues Tracking Form.xls").Activate
Application.ScreenUpdating = True
Application.DisplayAlerts = False
'REDO Formula in hidden Cells
Range("O18").Select
ActiveCell.FormulaR1C1 = "=R[-11]C[-11]"
Range("P18").Select
ActiveCell.FormulaR1C1 = "=RC[-14]"
Range("Q18").Select
ActiveCell.FormulaR1C1 = "=RC[-14]"
Range("R18").Select
ActiveCell.FormulaR1C1 = "=RC[-14]"
Range("S18").Select
ActiveCell.FormulaR1C1 = "=R[-13]C[-15]"
Range("T18").Select
ActiveCell.FormulaR1C1 = "=RC[-15]"
Range("U18").Select
ActiveCell.FormulaR1C1 = "=RC[-15]"
Range("V18").Select
ActiveCell.FormulaR1C1 = "=RC[-11]"
Range("W18").Select
ActiveCell.FormulaR1C1 = "=RC[-15]"
Range("X18").Select
ActiveCell.FormulaR1C1 = "=RC[-15]"
Range("Y18").Select
ActiveCell.FormulaR1C1 = "=RC[-15]"
Range("Z18").Select
ActiveCell.FormulaR1C1 = "=RC[-15]"
Range("Z19").Select
ActiveWindow.SmallScroll ToRight:=2
Range("AA18").Select
ActiveCell.FormulaR1C1 = "=RC[-15]"
'Clear entry and set up for new one!
Range("B18:L18").Select
Selection.ClearContents
Range("D5:E5").Select
Selection.ClearContents
MsgBox "All Done!"
End Sub
This part,
Code:
'This will copy hidden cells into another workbook and save that book
Windows("Critical Issues Tracking Form.xls").Activate
Range("O18:AA18").Select
Selection.Copy
Range("O18:AA18").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False