 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008copy cell with macro and increment down each time
I need to create a macro that copies two adjacent cells A148 & B148 (same cells each time but with a different value each time) and pastes them into another column but moving down one row each time. The object is to retain the previous values ultimately resulting in a column of various data values. I am able to copy and paste with a macro but when run, it overwrites the previous value because the paste was assigned to a particular cell. Or, is it somehow possible to store the first set of values elsewhere before overwriting? Will will use column Y & Z: Sub rjj() Set r = Range("A148:B148") n = Cells(Rows.Count, "Y").End(xlUp).Row + 1 r.Copy Cells(n, "Y") End Sub  SignatureGary''s Student - gsnu200785 > I need to create a macro that copies two adjacent cells A148 & B148 (same > cells each time but with a different value each time) and pastes them into [quoted text clipped - 3 lines] > value because the paste was assigned to a particular cell. Or, is it somehow > possible to store the first set of values elsewhere before overwriting? I'm sorry but I don't quite understand. Should I edit my existing "copy macro"? This is what I have now that copies to the same cell each time. The reason it is a "Paste Special" is because I am excluding the formula in the cell I am copying from. Sub SaveCells() ' ' SaveCells Macro ' ' Range("A148:B148").Select Selection.Copy Range("E148:F148").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False End Sub > Will will use column Y & Z: > [quoted text clipped - 11 lines] > > value because the paste was assigned to a particular cell. Or, is it somehow > > possible to store the first set of values elsewhere before overwriting? No, continue using your macro. Just replace the single line: Range("E148:F148").Select with n = Cells(Rows.Count, "E").End(xlUp).Row + 1 Range("E" & n & ":F" & n).Select SignatureGary''s Student - gsnu200785 > I'm sorry but I don't quite understand. Should I edit my existing "copy > macro"? This is what I have now that copies to the same cell each time. The [quoted text clipped - 29 lines] > > > value because the paste was assigned to a particular cell. Or, is it somehow > > > possible to store the first set of values elsewhere before overwriting? Thank You, works great but now: It only works with the macro button on the original worksheet. I assigned the macro to a new button on sheet "PO-LLC". but it does not fill the cells in sheet "P.O.# Usage" The original sheet is "P.O. # Usage". My now working macro (only on original sheet) is: Range("A1:B1").Select Selection.Copy n = Cells(Rows.Count, "A").End(xlUp).Row + 1 Range("A" & n & ":B" & n).Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False ActiveWorkbook.Save End Sub I've noticed that when I activate the macro button that resides on the PO-LLC sheet, it tries to populate cells A2 and B2 on that sheet although nothing appears in them. They only highlight when I activate the macro button. I hope that makes sense. I can't tell you how much I appreciate your help with this. > No, continue using your macro. > [quoted text clipped - 40 lines] > > > > value because the paste was assigned to a particular cell. Or, is it somehow > > > > possible to store the first set of values elsewhere before overwriting? I am glad we are making progress. I don't know how your buttons are setup, but let's say we have ten sheets with a button on each sheet. Create a single macro in a module (bring up VBA and Insert > Module). Then you can connect each button with the same macro. When the macro runs, it should run on the active sheet. SignatureGary''s Student - gsnu200785 > Thank You, works great but now: > [quoted text clipped - 62 lines] > > > > > value because the paste was assigned to a particular cell. Or, is it somehow > > > > > possible to store the first set of values elsewhere before overwriting? I can "bring up VBA and Insert Module, but then what? Now, when I set up a macro, I record it with my mouse clicks and then stop recording. That said, I'm not sure what to do when I open a new module via VBA. As far as buttons on my sheet, I really only need one button on sheet PO-LLC to copy, paste, increment, and store values on sheet PO # Usage. > I am glad we are making progress. I don't know how your buttons are setup, > but let's say we have ten sheets with a button on each sheet. Create a [quoted text clipped - 68 lines] > > > > > > value because the paste was assigned to a particular cell. Or, is it somehow > > > > > > possible to store the first set of values elsewhere before overwriting?
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.