any way to one click move

[colman]

Hey there might be a simple solution to this, but I was wondering if there was a way to move a row to another sheet in the workbook with just one click (or something similar), so I dont have to spend any time copying and pasting a couple hundred rows.

Any insight would be great!

 

[MrKowz]

Can you please elaborate on what exactly you want to do with this "one click"?

 

[colman]

I have a sheet of 2000+ rows and only want to use about 500 or so. Instead of dragging or copying each row individually to a new sheet, I was hoping there was a way where I could just click on the row and it would move to where I want (the new sheet). Or it could even just shift it over two columns.

 

[MrKowz]

There is, however we might be able to automate this further so you don't even need to manually click each row!

Is there a particular criteria that you are looking for that determines if a row is moved over or not? And what are the names of the sheet with your data, and the name of the sheet you want to move the rows to?

 

[Dannyh1]

do the rows you require have a distiquishing feature? you could filter on this & then copy filtered results & paste

 

[colman]

No unfortunately they don't, the 500+ names I'm pulling are from a physical list in front of me.

 

[colman]

Sheet1 with data is "2011 Companys" and Sheet2 where I want the rows moved is "Tech Group"

 

[MrKowz]

Understood - and last question. Do you want to copy the rows over and leave the record in the "2011 Companys" sheet? Or do you want to delete the record in the "2011 Companys" sheet after you move it?

 

[colman]

Lets copy and leave the record in 2011 Companys

 

[MrKowz]

Right click on the sheet tab "2011 Companys" and click "View Code". Then Copy/paste the below code into the VBA editor.

Now, in the "2011 Companys" sheet, just double-click on a row and it will move it to the "Tech Group" sheet.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, cancel As Boolean)
Dim LR  As Long
LR = Sheets("Tech Group").Range("A" & Rows.Count).End(xlUp).Row + 1
Target.EntireRow.Copy Destination:=Sheets("Tech Group").Range("A" & LR)
End Sub