Alteration to my code here....

cav~firez22

Well-known Member
Joined
Jun 21, 2006
Messages
543
I have the following code

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
How can i make it so it copies more rows, if the data is there?
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Code:
dim x as range
LC = Cells(18,Columns.Count).End(xltoleft).Column
set x = Range(Cells(18,"O"),Cells(18,LC))
x.value = x.value

or if you want to keep the copy/paste function happening...
Code:
dim x as range
LC = Cells(18,Columns.Count).End(xltoleft).Column
set x = Range(Cells(18,"O"),Cells(18,LC))
x.copy
x.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
        :=False, Transpose:=False
 
Upvote 0
so it should look like this?

Code:
dim x as range 
LC = Cells(18,Columns.Count).End(xltoleft).Column 
set x = Range(Cells(18,"O"),Cells(18,LC)) 
x.value = x.value 
 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
??
 
Upvote 0
almost...

Code:
dim x as range 
LC = Cells(18,Columns.Count).End(xltoleft).Column 
set x = Range(Cells(18,"O"),Cells(18,LC)) 
x.value = x.value 
 x.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
 
Upvote 0
Code:
   Windows("Critical Issues Tracking Form.xls").Activate
Dim x As Range
LC = Cells(18, Columns.Count).End(xlToLeft).Column
Set x = Range(Cells(18, "O"), Cells(18, LC))
x.Value = x.Value
 x.Copy

So this is supposed to copy from colum O : AA
and as many rows down as there is data?

ie O18:AA25 .. if there were 7 rows of data?
 
Upvote 0
Oh, ROWS...
Code:
Windows("Critical Issues Tracking Form.xls").Activate 
Dim x As Range 
LR = Cells(Rows.Count, "O").End(xlup).Row 
Set x = Range(Cells(18, "O"), Cells(LR, "AA")) 
x.Value = x.Value 
x.Copy

That will copy from O18 to AA(Last Used Row)
 
Upvote 0
awesome.

Now for a tricky part.

the range that it is copying, originally was formula's

all of them

If(Y="","",Y)

Then, in the code, they copy /paste values.

once the "" (blank cell) is copied and pasted, it contains data, and the x.copy see's it as containing information...


any idea what im talking about?
 
Upvote 0
I think you might need to reset the LR variable after pasting values...
maybe
Code:
Windows("Critical Issues Tracking Form.xls").Activate 
Dim x As Range 
LR = Cells(Rows.Count, "O").End(xlup).Row 
Set x = Range(Cells(18, "O"), Cells(LR, "AA")) 
x.Value = x.Value 

LR = Cells(Rows.Count, "O").End(xlup).Row
Set x = Range(Cells(18, "O"), Cells(LR, "AA")) 
x.Copy
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

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