Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating copy/paste/paste special when row references change
I am a rookie with macros and even more of a rookie with VBA. I have
reviewed Excel's help file and posts in this and other groups via Google and have not found anything directly on this point. I am using Excel 2007. I need to copy formulas from a row to the next row down and then overwrite the formulas in the source row with the values themselves. Each row represents numerical values for the end of each week. The formulas grab the number from elsewhere in the same file. I have used the macro recorder and it shows this code in the edit box. I haven't made any manual edits. Sub Macro2test() ' ' Macro2test Macro ' yada yada ' ' Keyboard Shortcut: Ctrl+f ' Range("B260:W260").Select Range("W260").Activate Selection.Copy Range("B261").Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Range("B260").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub In this case, formulas representing the weekly values are in B260 through W260. I need to grab the formulas and paste them to B261 through W261, and then overwrite B260 through W260 with the values per se (not the formulas). It works as far as it goes, but doesn't allow for the fact that I am marching down another row every week. A month from today, I will be operating down around row 264 and the macro as written above is locked into rows 260 and 261. Is there any way to force the row references in the macro to increment weekly, or to simply force the formula paste to the next row down and the value paste special back to the source row, regardless of row number? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating copy/paste/paste special when row references change
The code assumes Column B if filled in for every row. the code uses this
column to determine last row. It also looks like you code was pasting twice. I simplified the original code Sub Macro2test() ' ' Macro2test Macro ' yada yada ' ' Keyboard Shortcut: Ctrl+f ' Lastrow = Cells(Rows.Count, "B").End(xlUp).Row Range("B" & Lastrow & ":W" & Lastrow).Copy Range("B" & (Lastrow + 1)).Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False End Sub "Carl LaFong" wrote: I am a rookie with macros and even more of a rookie with VBA. I have reviewed Excel's help file and posts in this and other groups via Google and have not found anything directly on this point. I am using Excel 2007. I need to copy formulas from a row to the next row down and then overwrite the formulas in the source row with the values themselves. Each row represents numerical values for the end of each week. The formulas grab the number from elsewhere in the same file. I have used the macro recorder and it shows this code in the edit box. I haven't made any manual edits. Sub Macro2test() ' ' Macro2test Macro ' yada yada ' ' Keyboard Shortcut: Ctrl+f ' Range("B260:W260").Select Range("W260").Activate Selection.Copy Range("B261").Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Range("B260").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub In this case, formulas representing the weekly values are in B260 through W260. I need to grab the formulas and paste them to B261 through W261, and then overwrite B260 through W260 with the values per se (not the formulas). It works as far as it goes, but doesn't allow for the fact that I am marching down another row every week. A month from today, I will be operating down around row 264 and the macro as written above is locked into rows 260 and 261. Is there any way to force the row references in the macro to increment weekly, or to simply force the formula paste to the next row down and the value paste special back to the source row, regardless of row number? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating copy/paste/paste special when row references change
If I read his message correctly, Carl needed to paste twice - once for
formulas in a new row, and once to replace the "old" last row with values. So amending the code slightly to do the second paste: --------------------------------------------- Sub Macro2test() ' ' Macro2test Macro ' yada yada ' ' Keyboard Shortcut: Ctrl+f ' Lastrow = Cells(Rows.Count, "B").End(xlUp).Row Range("B" & Lastrow & ":W" & Lastrow).Copy ' Paste formulas into new row: Range("B" & (Lastrow + 1)).PasteSpecial Paste:=xlPasteFormulas, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False ' Replace existing row with values: Range("B" & (Lastrow)).PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False ' Turn off the "highlight" around the copied cells: Application.CutCopyMode = False End Sub --------------------------------------------- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating copy/paste/paste special when row references change
"Cowering Dragon" I agree. If there were formulas in row 260, then the 2nd
paste special replaces the formulas with values. "Cowering Dragon" wrote: If I read his message correctly, Carl needed to paste twice - once for formulas in a new row, and once to replace the "old" last row with values. So amending the code slightly to do the second paste: --------------------------------------------- Sub Macro2test() ' ' Macro2test Macro ' yada yada ' ' Keyboard Shortcut: Ctrl+f ' Lastrow = Cells(Rows.Count, "B").End(xlUp).Row Range("B" & Lastrow & ":W" & Lastrow).Copy ' Paste formulas into new row: Range("B" & (Lastrow + 1)).PasteSpecial Paste:=xlPasteFormulas, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False ' Replace existing row with values: Range("B" & (Lastrow)).PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False ' Turn off the "highlight" around the copied cells: Application.CutCopyMode = False End Sub --------------------------------------------- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating copy/paste/paste special when row references change
"Joel" wrote in message ... The code assumes Column B if filled in for every row. the code uses this column to determine last row. It also looks like you code was pasting twice. I simplified the original code Sub Macro2test() ' ' Macro2test Macro ' yada yada ' ' Keyboard Shortcut: Ctrl+f ' Lastrow = Cells(Rows.Count, "B").End(xlUp).Row Range("B" & Lastrow & ":W" & Lastrow).Copy Range("B" & (Lastrow + 1)).Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False End Sub Joel: Thanks for the help. I am guessing I didn't explain myself well enough in my original post. You state "It also looks like you code was pasting twice". That is correct and I need to paste twice. The first paste must paste the formulas to the next row down. The second paste must paste the raw values (not the formulas) back to the source row. I tried your code. It pastes the formulas down to the correct row, but does NOT then also paste the values back to the source row, overwriting the formulas. I need that to happen. The macro must leave the formulas ONLY in the very last row, so it must overwrite the original row, which becomes the next to last row after the formulas are pasted down. In the meantime, I will fiddle with your code to see if I can get it to work as required. Thanks again. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
Copy; Paste; Paste Special are disabled | Excel Discussion (Misc queries) | |||
Conditional formatting -- copy paste special won't change referenc | Excel Discussion (Misc queries) | |||
help w/ generic copy & paste/paste special routine | Excel Programming | |||
Dynamic Copy/Paste Special Formulas/Paste Special Values | Excel Programming |