Solved Need to move multiple rows based on one field in each row

February 7, 2012 at 07:22:58
Specs: Windows XP
I have a spreadsheet with hundreds of records. In row "I", there is a number that is representative of an employee. I need to move the entire row based on that one field.

For example, in the below, I need to move Joe Smith and his entire row to sheet 2. I need to move Jason Doe and Jane Does and their entire row to sheet 3 and so on.

Name DOB MR NXTASSESS Emp#
Joe Smith 12/13/1984 307353 1/30/2012 1
Jason Doe 7/31/1920 196986 2/2/2012 2
Jane Does 6/29/1949 480904 2/2/2012 2
Jim Doesnt 4/7/1942 198878 2/3/2012 3


LOST, help!


See More: Need to move multiple rows based on one field in each row

Report •


✔ Best Answer
February 8, 2012 at 17:47:36
I suggest that you try the following:

1 - Create a template with 11 sheets
2 - Group Sheets 2 - 11 (Select Tab 2, Shift-Click Tab 11)
3 - Hide the Columns that you want hidden
4 - Enter your Column Headings
5 - Ungroup the sheets. (Select any sheet tab other than the current sheet)
6 - Press Alt-F11 to open the VBA editor
7 - Paste the following code into the pane that opens
8 - Create a button in Sheet1 and assign the macro to the button
9 - Save the template, Macro enabled
10 - Paste your data into Sheet1 and click the button to run the macro
11 - Save the resulting sheet as an xlsx sheet, unless you want the macro to be saved with every sheet. Your choice.

Note: The code assumes that you have column headings in Sheet1, so it starts searching Column I in Row 2.


Sub CopyRowsToSheets()
'Determine last row with data in Sheet 1
 last_srcRw = Sheets(1).Range("I" & Rows.Count).End(xlUp).Row
'Loop through Sheet 1 rows, starting in Row 2
  For srcRw = 2 To last_srcRw
'Calculate destination sheet by adding 1 to value in Column I
    dstSht = Sheets(1).Range("I" & srcRw) + 1
'Determine the next empty Row in destination sheet
    next_dstRw = Sheets(dstSht).Range("I" & Rows.Count).End(xlUp).Row + 1
'Copy the row to the dstination sheet.
    Range("I" & srcRw).EntireRow.Copy _
      Destination:=Sheets(dstSht).Range("A" & next_dstRw)
  Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
February 7, 2012 at 08:05:12
Copy. Paste. Done.

Report •

#2
February 7, 2012 at 08:12:56
Instead of just a Copy/Paste
Try doing a Sort on your Column I
(there is no Row I, rows are numbered)
that should group all the like names together,
then do a copy/paste.

MIKE

http://www.skeptic.com/


Report •

#3
February 7, 2012 at 10:03:40
1) I mean column "I"
2) Copy and paste? I have a document with hundreds of records to download every day, sort, copy, paste, save, print and hand out. I am looking for a more automated option. I'm not a rocket scientist, but I know how to copy and paste.

Report •

Related Solutions

#4
February 7, 2012 at 12:42:39
In your original post, you did not specify that this was going to be more than just a onetime procedure.

You are in need of a VBA solution if it is going to be an everyday occurrence.
Unfortunately, my VBA skills are just above nil.
Hopefully someone will offer a VBA solution.

MIKE

http://www.skeptic.com/


Report •

#5
February 7, 2012 at 15:05:16
You came here looking for help, you posted incomplete requirements, and then you post back with an attitude towards those that tried to help you?

There is no way for anyone reading your original post to know your level of Excel expertise or that you have "hundreds of records to download every day, sort, copy, paste, save, print and hand out".

Based on what you posted, the sort/copy/paste suggestions will meet the only requirements we could possibly know about.

If you'll be humble enough to acknowledge that the previous responders did not deserve the attitude in your latest post, I'm sure someone will offer a more "automated option".

DerbyDad03
Office Forum Moderator

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
February 8, 2012 at 07:37:24
Yes, I am humble enough to admit I had an attitude and I apologize for that. I am under a lot of pressure to have this done right away and am at a complete loss. I read the "Copy. Paste. Done" as being sarcastic and in the mood I was in yesterday, was annoyed.

I am desperate for a solution and know zilch about vba.


Report •

#7
February 8, 2012 at 08:20:17
What exactly is in Column I? Is it 1, 2 and 3 as posted?

If so, is each number really 1 less than the sheet position it is to be moved to? e.g. all 1's go to Sheet2, all 2's go to Sheet4, etc.?

The reason I ask is because VBA needs to be very specific about what it is looking for and what it does when the item is found. It's just like us...it can't "guess" at what the user wants, it needs to be told.

The more detail you provide, the better chance a solution can be offered the first time, without a lot of back and forth.

Are there any more details that you think we need to know?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
February 8, 2012 at 12:42:43
Hi,
What I do is download a file from a website to excel. It downloads 9 columns.

There are rows in the file. In column "I" there is a number that represents an employee. The numbers are 1-10.

I need to move all rows that have a #1, to Sheet 2. #2, to Sheet 3, #3 to Sheet 4, etc.

This will allow for each employee to have a list of their patients.

Ideally, I would also like to hide columns A, B, F and G if possible when the rows are moved.


Report •

#9
February 8, 2012 at 12:50:29
It's all about the details...

Do Sheets 2 - 11 already exist or do they need to be created?

Are you appending data to the bottom of a sheet or is it new data each time?

Are there column headings on Sheets 2 - 11?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
February 8, 2012 at 13:17:04
Whichever is easier, we can create a template and paste the information into sheet 1 every time and have all of the sheets already existing, OR, we can create them each time. Whichever you think is best.

New data each time.

Yes, there would be headings. If A, B, F and G were hidden the remaining columns would be:

C: SortName
D: DOB
E: FuncData (which Ideally, would say MRN after moving, or could stay the same)
H: Next Assessment Due
I: CM Number


Report •

#11
February 8, 2012 at 17:47:36
✔ Best Answer
I suggest that you try the following:

1 - Create a template with 11 sheets
2 - Group Sheets 2 - 11 (Select Tab 2, Shift-Click Tab 11)
3 - Hide the Columns that you want hidden
4 - Enter your Column Headings
5 - Ungroup the sheets. (Select any sheet tab other than the current sheet)
6 - Press Alt-F11 to open the VBA editor
7 - Paste the following code into the pane that opens
8 - Create a button in Sheet1 and assign the macro to the button
9 - Save the template, Macro enabled
10 - Paste your data into Sheet1 and click the button to run the macro
11 - Save the resulting sheet as an xlsx sheet, unless you want the macro to be saved with every sheet. Your choice.

Note: The code assumes that you have column headings in Sheet1, so it starts searching Column I in Row 2.


Sub CopyRowsToSheets()
'Determine last row with data in Sheet 1
 last_srcRw = Sheets(1).Range("I" & Rows.Count).End(xlUp).Row
'Loop through Sheet 1 rows, starting in Row 2
  For srcRw = 2 To last_srcRw
'Calculate destination sheet by adding 1 to value in Column I
    dstSht = Sheets(1).Range("I" & srcRw) + 1
'Determine the next empty Row in destination sheet
    next_dstRw = Sheets(dstSht).Range("I" & Rows.Count).End(xlUp).Row + 1
'Copy the row to the dstination sheet.
    Range("I" & srcRw).EntireRow.Copy _
      Destination:=Sheets(dstSht).Range("A" & next_dstRw)
  Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#12
February 9, 2012 at 12:54:59
OMW. Works absolutely perfect.

I CANNOT thank you enough. You have completely made my day (more like my month). Thank you SO much.

Sorry for my cranky earlier attitude.

I would send you chocolates if I knew where to send them:)

Thanks again.


Report •

#13
Report •


Ask Question