Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to F2, F9, then cut and paste
I'm using Excel 2007, and I'm having problems with something that was simple
in Excel 2002. I have three cells in three rows. After I move to the third cell in the third row, I need to invoke a macro that presses F2, F9, then ENTER in that same cell. Then I need the macro hit the up arrow once, to go up one row and back to the cell in the third row. Finally, I need to cut the resulting contents of the third cell, and paste them into the cell two rows above it (the cell in row 1). Then I need to move down one row (to the cell in the second row) and delete the contents from that second cell. When I record a macro in 2007, it wants to go back to the cells I edited when I first recorded the macro. I don't want it to do that. In the code below, specific cells are specified - B3, B4, and B5. Rather than always running the macro on these three cells, I need it to work on the cell I have currently selected, as well as the two cells immediately above it. How do I need to change the code of this macro so it does that? Thanks, GwenH Sub Macro7() ' ' Macro7 Macro ' ' Keyboard Shortcut: Ctrl+a ' ActiveCell.FormulaR1C1 = "2.1€“1 Control Rod Guide Tube Assembly" With ActiveCell.Characters(Start:=1, Length:=37).Font .Name = "Helvetica" .FontStyle = "Regular" .Size = 9 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With Range("B5").Select Selection.Cut Range("B3").Select ActiveSheet.Paste Range("B4").Select Selection.ClearContents End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to F2, F9, then cut and paste
I'm sure this could be accomplished using the kind of code you have
here, from the macro recorder. To make it work as you want you would have to set the recorder to "relative" reference rather than "absolute" reference. However, as is often the case the recorded code is much longer and more complex than it needs to be. Here is a short sub that will accomplish what I think you are trying to do: Sub DoIt() 'copies value from current cell to cell two rows above, then clears current cell and cell above it 'check to make sure there is room to copy value up two rows If ActiveCell.Row < 3 Then MsgBox "Can't do that here!", vbOKOnly, "" Exit Sub End If 'copy value from current row to row two above ActiveCell.Offset(-2, 0).Value = ActiveCell.Value 'delete value from current cells ActiveCell.ClearContents 'delete value from above cell ActiveCell.Offset(-1, 0).ClearContents End Sub On Mar 2, 3:50 pm, GwenH wrote: I'm using Excel 2007, and I'm having problems with something that was simple in Excel 2002. I have three cells in three rows. After I move to the third cell in the third row, I need to invoke a macro that presses F2, F9, then ENTER in that same cell. Then I need the macro hit the up arrow once, to go up one row and back to the cell in the third row. Finally, I need to cut the resulting contents of the third cell, and paste them into the cell two rows above it (the cell in row 1). Then I need to move down one row (to the cell in the second row) and delete the contents from that second cell. When I record a macro in 2007, it wants to go back to the cells I edited when I first recorded the macro. I don't want it to do that. In the code below, specific cells are specified - B3, B4, and B5. Rather than always running the macro on these three cells, I need it to work on the cell I have currently selected, as well as the two cells immediately above it. How do I need to change the code of this macro so it does that? Thanks, GwenH Sub Macro7() ' ' Macro7 Macro ' ' Keyboard Shortcut: Ctrl+a ' ActiveCell.FormulaR1C1 = "2.1-1 Control Rod Guide Tube Assembly" With ActiveCell.Characters(Start:=1, Length:=37).Font .Name = "Helvetica" .FontStyle = "Regular" .Size = 9 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With Range("B5").Select Selection.Cut Range("B3").Select ActiveSheet.Paste Range("B4").Select Selection.ClearContents End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy multiple worksheets of a workbook, and paste onto a Word document ( either create new doc file or paste onto an existing file.) I need this done by VBA, Excel Macro | Excel Programming | |||
Copy and Paste macro needs to paste to a changing cell reference | Excel Programming | |||
Cut and Paste using Macro gives paste special method error | Excel Programming | |||
Macro to Paste to specific line, and continue to Paste each time on next row not over | Excel Programming | |||
Macro to Copy/Paste then Paste to Next Line | Excel Programming |