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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
 

cav~firez22

Well-known Member
Joined
Jun 21, 2006
Messages
543
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
??
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
 

cav~firez22

Well-known Member
Joined
Jun 21, 2006
Messages
543

ADVERTISEMENT

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?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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)
 

cav~firez22

Well-known Member
Joined
Jun 21, 2006
Messages
543

ADVERTISEMENT

Hahaha, Thanks very much for the help. Greatly Appreciated
 

cav~firez22

Well-known Member
Joined
Jun 21, 2006
Messages
543
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?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,444
Messages
5,642,172
Members
417,259
Latest member
gtacw

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
Top