Continuously running recorded macro through table

[smichael]

I have the recorded macro down, but I would like it to run continuously down the column through the range of data and stop at the desired cell instead of having to click the shortcut for every row. Is there a way to do this?

 

[Desu Nota from Columbus]

Sub Change2()
'
' Change2 Macro
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
Dim i As Long
Dim lrow As Long

Application.DisplayAlerts = False

lrow = Range("G" & Rows.Count).End(xlUp).Row

For i = 7 To lrow
    Cells(i, "G").Copy
    Cells(i, "K").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Cells(i, "G").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[4]-RC[3]"
    Cells(i, "G").Copy
    Cells(i, "K").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Copy
    Range("G7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Cells(i, "J").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "0"
    Columns("K:K").Delete Shift:=xlToLeft
Next i
Application.DisplayAlerts = True

End Sub

Sorry this took so long, I was out of the office yesterday!

See if this works for you.

 

[smichael]

I've edited some of the stuff to make the macro work how I needed it to, but once i protect the sheet it won't work correctly.

Here is my code:

Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+Shift+x
'
Dim i As Long
Dim lrow As Long
Application.DisplayAlerts = False
lrow = Range("G" & Rows.Count).End(xlUp).Row
For i = 7 To lrow
    Cells(i, "G").Select
    Selection.Copy
    Cells(i, "K").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Cells(i, "G").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "RC[4]-RC[3]"
    Cells(i, "G").Select
    Selection.Copy
    Cells(i, "K").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Copy
    Cells(i, "G").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Cells(i, "J").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "0"
    Columns("K:K").Select
    Cells(i, "K").Activate
    
Next i
Application.DisplayAlerts = True
End Sub

Any idea how to correct this?

 

[Desu Nota from Columbus]

You might need to specify what is allowed while the sheet is protected.

For instance, this macro changes cell values and deletes columns effectively rearranging columns(cells).

What are you trying to preserve by protecting the sheet?

 

[smichael]

In protecting the sheet, column G is protected, but i allowed the users to edit columns J and K. This is for an inventory sheet, and I don't want others to personally edit the values in the "Quanity on Hand" field (column G). I would like an administrator to be able to run this macro without unprotecting the G column, if that is possible.

Thanks again for all the help!

 

[Desu Nota from Columbus]

Try adding this to the macro:

ActiveSheet.Unprotect

rest of code

ActiveSheet.Protect

see if that works

 

[smichael]

hmmm doesn't seem to work. I also have a macro with .Protect being used, will that change anything?

 

[Desu Nota from Columbus]

Not that i know of. I dont know if I can help you. Try posting another thread with your question regarding protected cells.