Hi, (Welcome to the Board.)
.. This seems on the face of it a fairly simple problem that even I can do…
.. As always getting a clear “Picture” across of what you want initially seems to be a bit of a Hurdle. But understandable if you are new.
. (Your initial Tables are OK, but consider learning fully the Forum Tools for passing info across. It is always worth it in the long run)
. And a very important point, as Alan S. inferred, show exactly how things initially look and very importantly, type in manually an output example based on the initial data. This example output should look exactly as you want after running of the macro.
.. ……………..
….. Anyways…
.
… 1) Terms like “cutting, Pasting, deleting, vanishing” can be a bit confusing I think to a programmer. For example deleting a line could cause complications as you will lose info in that row from A:D also. The preferred word would be “Clear” here. Initially I will assume that is satisfactory..
.
… 2) It Looks like you may have got your code from a macro recording. Can be a good starting point and I think actually you are not far off with your code. Probably the best tip here to give to a beginner is that in a final code there needs typically to be a lot less “selecting” in the code. -You yourself may need to do that manually when doing things. VBA often does not need to do that. Another important point about using the macro recorder that may catch you out specifically here is that “Paste” - ing is a bit of a quick imprecise thing and with things like date format can give you headaches later
…..3) .. So.. Here is A very simple code example that should get you started... (I do have a lot of extra and expanded steps that could be omitted, as well as explaining
‘comments as I am still learning and need those for myself, especially when I come back to the code some time later. I will give also again a simplified code version. (Incidentally in the Excel VBA code window ( Alt F11 ) you do not see so much of the
‘comments as they are typically in long lines which you only see if you scroll in that development Window a long way to the right.) )
…
. The codes will take for example this
| Row\Col | A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q |
1 | Date | OriginalBudget | Budget | Expense | | | | | | | | | | | | | |
2 | 01. Jul 14 | $40,665.00 | $0.00 | $0.00 | | | | | | | | | | 16. Jul 14 | $0.00 | $0.00 | $39.26 |
3 | 02. Jul 14 | $0.00 | ($3,000.00) | $150.35 | | | | | | | | | | 17. Jul 14 | $0.00 | $28.94 | $0.00 |
4 | 02. Jul 14 | $0.00 | $21,000.00 | $114.50 | | | | | | | | | | 21. Jul 14 | $0.00 | $61.34 | $0.00 |
5 | | | | | | | | | | | | | | 21. Jul 14 | $0.00 | $1,163.08 | $0.00 |
6 | | | | | | | | | | | | | | 21. Jul 14 | $0.00 | $193.98 | $0.00 |
……
And after running the code (assuming you have selected the Range shown in red), you should get this
| Row\Col | A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q |
1 | Date | OriginalBudget | Budget | Expense | | | | | | | | | | | | | |
2 | 01. Jul 14 | $40,665.00 | $0.00 | $0.00 | | | | | | | | | | 16. Jul 14 | $0.00 | $0.00 | $39.26 |
3 | 02. Jul 14 | $0.00 | ($3,000.00) | $150.35 | | | | | | | | | | 17. Jul 14 | $0.00 | $28.94 | $0.00 |
4 | 02. Jul 14 | $0.00 | $21,000.00 | $114.50 | | | | | | | | | | | | | |
5 | 21. Jul 14 | $0.00 | $61.34 | $0.00 | | | | | | | | | | 21. Jul 14 | $0.00 | $1,163.08 | $0.00 |
6 | | | | | | | | | | | | | | 21. Jul 14 | $0.00 | $193.98 | $0.00 |
……………………………..
Full Code with comments etc:
Code:
[color=lightgreen]'Option Explicit' This forces you to Dimension all Variable correctly. Helps with both memory space as well as helping detect errors in debugging[/color]
[color=blue]Sub[/color] SimpleSelectionCopyForVbaProblem()
[color=lightgreen]' Application.ScreenUpdating = False'Can Speed things up a bit, but prabably unecerssary here.[/color]
[color=blue]On[/color] [color=blue]Error[/color] [color=blue]GoTo[/color] TheEnd [color=lightgreen]'If something goes wrong we go to "The End" rather than crashing[/color]
[color=blue]Dim[/color] wksOP [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wksOP = ThisWorkbook.Worksheets("OPERATING") [color=lightgreen]'Give Abbreviation the Under objects, Methods and Properties of Object Worksheets (Intellisense then gives suggestions through use of . Dot[/color]
[color=blue]Dim[/color] lr [color=blue]As[/color] [color=blue]Long[/color], Sr [color=blue]As[/color] [color=blue]Long[/color] [color=lightgreen]'Variabnles for Last Row Column 1 and Selected Row ( ( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) -Most smaller numbers, Byte, Integer, Single are converted in computer to long so no advantage of Dim to smaller Type here) >>> Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. http://www.mrexcel.com/forum/excel-questions/803662-byte-backward-loop-3.html[/color]
wksOP.Activate [color=lightgreen]'See the sheet you are interested in. This line is mostly not necerssary, but ocaisionally is needed, at least the first time around.[/color]
[color=blue]Let[/color] Sr = Application.Selection.Row
Application.Selection.Copy [color=lightgreen]'I believe this is a sort of general "Clipboard Process" performed by Excel the "applicastion"[/color]
[color=blue]Let[/color] lr = wksOP.Cells(Rows.Count, 1).End(xlUp).Row [color=lightgreen]'Last Row with entry in First Colunn. Found by starting at last cell in row 1, then going backwards (Upwards) until something is found, then with .End returning a range (Cell) from which the Row property can be used to get the Row number[/color]
[color=lightgreen]' 'Let lr = wksOP.Cells.Find(What:="*", after:=wksOP.Cells(1, 1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 'Get last Row with entry anywhere for Sheet1. Method: You start at first cell then go backwards (which effectively starts at end of sheet), sercching for anything ( = * ) by rows, then get the row number. This allows for different excel versions with different available Row numbers) Just a different method here for fun- finds last row in sheet rather than row for last entry in particular cell[/color]
wksOP.Range("A" & lr + 1 & "").PasteSpecial xlPasteAllUsingSourceTheme [color=lightgreen]'We are "Pasting into the next( + 1)empty Row. The argument "xlPasteAllUsingSourceTheme" is a fairly safe option, keeping all formats. There are many other Options.... Poist #25 http://www.mrexcel.com/forum/excel-questions/785462-visual-basic-applications-copy-value-paste-worksheet-same-name-value-3.html[/color]
wksOP.Range("N" & Sr & ":Q" & Sr & "").Clear [color=lightgreen]'Clear (Empty) the Copied cell. This must be done after PasteSpecial, as I beleive the source may be needed (referrenced) at the pasting point[/color]
Application.CutCopyMode = [color=blue]False[/color] [color=lightgreen]'Stops the screen Flicker around the selection. Sort of Stops The CutCopy process, which again is not specific to a sheet[/color]
wksOP.Columns.AutoFit [color=lightgreen]'Just tidy up a bit[/color]
[color=blue]Exit[/color] [color=blue]Sub[/color] [color=lightgreen]'End assuming no errors occurred[/color]
TheEnd: [color=lightgreen]'[color=blue]End[/color] here if errors occurred**. This ensures importent closing things are done even in the case of an error[/color]
[color=lightgreen]' Application.ScreenUpdating = True[/color]
MsgBox Err.Description [color=lightgreen]'Last action before stoping when error occurs:- Give error description in a Message Box.[/color]
End [color=blue]Sub[/color] [color=lightgreen]'SimpleSelectionCopyForVbaProblem()[/color]
………………………
Simplified Code
Code:
[color=blue]Sub[/color] SimpleSelectionCopyForVbaProblemSHimpfGlified()
Sr = Selection.Row
Selection.Copy
Range("A" & Cells(Rows.Count, 1).End(xlUp).Row + 1 & "").PasteSpecial xlPasteAllUsingSourceTheme
Range("N" & Sr & ":Q" & Sr & "").Clear
CutCopyMode = [color=blue]False[/color]
[color=blue]End[/color] [color=blue]Sub[/color]
…………
. Alan
P.s.
.
………..So instead of cutting and pasting each row every time, just push one key to do that whenever it was needed!......
. As for that assigning of a key to the macro: that is usually described at an early stage in any VBA book… or can quickly be “Googled”. There are various ways from the Excel Spreadsheet to get the appropriate Dialogue boxes up to do that. Here - Just a quick indication of what you should see. Sorry I am using a German version of Excel, but I am sure you get the general drift.. (As an example I am setting here the key combination of Ctrl + Shift + P to the first of my codes )
<a href="http://s1065.photobucket.com/user/DocAElstein/media/AssignKey_zpsqz92o2sj.jpg.html" target="_blank"><img src="http://i1065.photobucket.com/albums/u400/DocAElstein/AssignKey_zpsqz92o2sj.jpg" border="0" alt=" photo AssignKey_zpsqz92o2sj.jpg"/></a>
