Macro offset, but need to anchor to newly pasted data
Posted by George J on January 14, 2002 8:16 AM
The macro below is supposed to enable me to copy cells A4 to P13 and paste them from A14. This haolds true for all the sheets, but I am stumped as to how after inputting data to cells in row 14, I can trigger the macro again if needed to copy A14 to P13 and paste from A24 etc.
I have looked at previous posts, but could not find the solution. Any help or tips will be appreciated.
Confused but hopeful
George
Sub Paste_Relief_Workings()
'
' Paste_Relief_Workings Macro
' Macro recorded 11/01/2002 by gj
'
' Keyboard Shortcut: Ctrl+n
'
Range("A4:P13").Select
Selection.Copy
ActiveWindow.LargeScroll ToRight:=-1
ActiveCell.Offset(10, 0).Select
ActiveSheet.Paste
Sheets("Orig RV").Select
Range("A4:P13").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.LargeScroll ToRight:=-1
ActiveCell.Offset(10, 0).Select
ActiveSheet.Paste
Sheets("Settled IT Relief").Select
Range("A4:P13").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.LargeScroll ToRight:=-1
ActiveCell.Offset(10, 0).Select
ActiveSheet.Paste
Sheets("Original IT Relief").Select
Range("A4:P13").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.LargeScroll ToRight:=-1
ActiveCell.Offset(10, 0).Select
ActiveSheet.Paste
Sheets("Settled RV").Select
'this takes us back to the original sheet.
End Sub
Posted by Tom Dickinson on January 14, 2002 9:20 PM
I assume this problem would keep occurring. You can loop till you find the next "section" of cells that is free, then paste things down. But I would suggest the following shortcut:
Sub Insert_Relief_Workings()
' Keyboard Shortcut: Ctrl+n
Range("A4:P13").Insert Shift:=xlDown
Range("'Orig RV'!A4:P13").Insert Shift:=xlDown
Range("'Settled IT Relief'!A4:P13").Insert Shift:=xlDown
Range("'Original IT Relief'!A4:P13").Insert Shift:=xlDown
Posted by George J on January 16, 2002 7:22 AM
Thanks
Finally worked it out. Thanks for the input, but the boss wanted to input from top to bottom.
Extra code all over the shop, but ended up with this:
Sub Paste_Relief_Workings()
'
' Paste_Relief_Workings Macro
' Macro recorded 11/01/2002 by gj
'
' Keyboard Shortcut: Ctrl+n
'
Range("A4:P13").Select
Selection.Copy
LastRow = Range("A65536").End(xlUp).Offset(1, 0).Row
If LastRow < 14 Then
NextRow = 14
Else
NextRow = Range("A65536").End(xlUp).Offset(5, 0).Row
Range("A" & NextRow).Select
End If
Range("A" & NextRow).Select
ActiveWindow.LargeScroll ToRight:=-1
ActiveSheet.Paste
Sheets("Orig RV").Visible = True
Sheets("Orig RV").Select
Range("A4:P13").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.LargeScroll ToRight:=-1
ActiveCell.Offset(10, 0).Select
ActiveSheet.Paste
Sheets("Orig RV").Visible = False
Sheets("Settled IT Relief").Visible = True
Sheets("Settled IT Relief").Select
Range("A4:P13").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.LargeScroll ToRight:=-1
ActiveCell.Offset(10, 0).Select
ActiveSheet.Paste
Sheets("Settled IT Relief").Visible = False
Sheets("Original IT Relief").Visible = True
Sheets("Original IT Relief").Select
Range("A4:P13").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.LargeScroll ToRight:=-1
ActiveCell.Offset(10, 0).Select
ActiveSheet.Paste
Sheets("Original IT Relief").Visible = False
Sheets("Settled RV").Select
Range("A" & NextRow).Select
End Sub
(My second ever macro)(and last hopefully)
Regards
George
