Macro which will copy entire ROW to another sheet based on contents of one cell!

**WaylettChris** · 11-04-2012, 07:05 PM

Hi All,

See the attached spreadsheet for exact details, but basically what I want is:

The employee will fill out their workload for each day on the "MAIN INPUT" sheet, then, based on what the cell says in COLUMN E, I want a macro linked to a button that will copy the different outcomes, to the sheet with the matching name which I have already created.

I also need them to appear on their sheets with no rows blank inbetween, with no duplicates, and in the same order as they appear on the "MAIN INPUT" Sheet.

I want the macro linked to a button that the employee can press to update the workbook.

I also want the employee to only be able to scroll from: Columns A - K, and rows 1-2000.

Can this be done? I hope so!

Any questions or if i'm not being clear, do let me know!

Thanks so much!

Chris

P.S. I have had a go at writing some code for the scrolling thing which I don't think works, may be best to just delete that and start over! Thanks!

**p24leclerc** · 11-04-2012, 09:54 PM

here is a macro that will copy all data to proper sheets.
In order to not duplicate data, why don't you delete them from MAININPUT after they are copied. I see no reason to keep a record at 2 places.
I moved your scroll restriction to the WORKBOOK.OPEN event because it will be done only once. You can add any other sheets you want to limit scrolling at the same place.

Public Sub Copy_Data()
Dim C_ell As Range
Application.ScreenUpdating = False
Sheets("MAIN INPUT").Select
For Each C_ell In Range("A2", Cells(Rows.Count, 1).End(xlUp))
  Range(C_ell.Address, Cells(C_ell.Row, 9)).Select
  Selection.Copy
  Sheets(C_ell.Offset(0, 4).Text).Select
  Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
  Selection.PasteSpecial
  Sheets("MAIN INPUT").Select
Next
Application.ScreenUpdating = True
End Sub

hope this help

**WaylettChris** · 11-05-2012, 03:31 PM

Hi p24leclerc!

That's good, but imagine if I wanted to keep the "Main Input" sheet the same as it is, could I do that...

I thought of this idea:

If we inserted the Formula

Formula:

=E2&"_"&COUNTIF(E$2:E2,E2)

into Cell L2 this would then result in the settlement route followed by a number representing the number of times this outcome had occurred in the format: "Cash Settlement_1". If then we copied that down 2000 rows, could the macro copy the rows to the right sheet, and then use the number at the end to paste them in the right order on their respective sheets?

Would this be possible?

Also, I need to be able to scroll to areas which the people I give the workbook to cannot, is there anyway I can override the scroll lock part of the macro, but they can't?

Thanks!

Chris

**WaylettChris** · 02-11-2013, 07:04 AM

Hi P24Leclerc!

Back again with a similar issue. There is a spreadsheet attached to this post, so what I need now is a macro which will "read" column C, identify the surveyor, and then copy the relevant rows to sheets named after the surveyors. If a sheet for the surveyor does not currently exist I need the macro to make one.

I need the copied rows to stay in the same order that they appear on the main Input sheet as before. When copying the rows, I only need columns A to U copied to the sheets.

I hope this makes sense, I need this to happen via a button on the MAIN INPUT Sheet.

I need a response to this URGENTLY please!

Thanks!

Chris

**p24leclerc** · 02-11-2013, 07:10 PM

First of all, you DO NOT want to be pushy on people working for you for FREE.
Secondly, this thread MUST be closed and you MUST start another thread as this is a new question.
I'd be glad to help if I can and you may even get abetter answer from someone else.
Regards

**p24leclerc** · 11-05-2012, 06:21 PM

Chris,
Data will always be transfered as they are entered as the macro proceed from the top down.
Now, in order to keep data in Main Iput and not transfer them multi times, the macro puts a "True" in column "L". Otherwise, the cells in this column are empty. When the macro copy a row into a tab, it will also write "True" in the L cell of this row. Only rows with L cells empty will be copied. Is that OK for you?

Now, about removing the scroll lock, the easy way is to program a macro with a password (Actually password is "Test"). I did it for you. In fact, I created 2 macros: One to remove the scroll lock and the other to reapply it. Both are password protected. This is not high tech protection as anyone can see the expected password by looking at the VB codes. But you can protect this also by a password by going into the VBA Project properties. Be carefull because this one is a real one. Make sure you'll remember it.
Hope this help
Regards

Macro which will copy entire ROW to another sheet based on contents of one cell!

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Macro which will copy entire ROW to another sheet based on contents of one cell!

Re: Macro which will copy entire ROW to another sheet based on contents of one cell!

Re: Macro which will copy entire ROW to another sheet based on contents of one cell!

Re: Macro which will copy entire ROW to another sheet based on contents of one cell!

Re: Macro which will copy entire ROW to another sheet based on contents of one cell!

Re: Macro which will copy entire ROW to another sheet based on contents of one cell!

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions