Copy rows from my main worksheet to another worksheet based on data in one cell

**gcol** · 08-07-2012, 08:26 AM

Hi,
I have a MAIN worksheet that I use for managing customer service dates with information for all of my clients in rows. In column "I" there is the month (JANUARY, FEBRUARY ETC) that each client should be visited and what I would like to do is copy the rows with the relevant month into a separate worksheet which is named by month. So any client with "MAY" in the service date has the row copied into the worksheet called "MAY", and any with "SEPTEMBER" are copied into the "SEPTEMBER" worksheet etc.

Is there a formula I can enter in each months worksheet to look at the MAIN worksheet and pull the relevant information in? I would like to have it so that if the clients service month changed from say "AUGUST" to "SEPTEMBER" then by changing the month in the MAIN worksheet it would remove it from the AUGUST worksheet and put it in the SEPTEMBER worksheet.
It goes without saying that any help is gratefully received.
Thanks.

**arlu1201** · 08-07-2012, 09:00 AM

You can do this easily on a click of a button.

What is your last column having data in it?

**gcol** · 08-07-2012, 09:15 AM

Ooooo that sounds good. The last column is "BE".

**arlu1201** · 08-07-2012, 09:26 AM

Try this code -
Each time you have made the changes, the code will clear out all the monthly sheets and populate the data afresh.

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

You can also create a button on your sheet and assign the macro to it or you can use a keyboard shortcut to trigger the macro.

**gcol** · 08-07-2012, 10:08 AM

Thanks for your quick reply Arlette. There's something wrong somewhere; what happens when I run the macro is that the contents of the MAIN worksheet get copied to the APRIL worksheet. The other worksheets have nothing in them.

**arlu1201** · 08-07-2012, 10:11 AM

Oops am sorry, here is the corrected code -

Please Login or Register  to view this content.

**gcol** · 08-07-2012, 10:28 AM

Thanks Arlette, that results in the following error:

Run-time error: subscript '9'
Subscript out of range.

It highlights the longest row of text in your code.
Any ideas?

**arlu1201** · 08-07-2012, 10:56 AM

Do you have any blank values in column I?

**gcol** · 08-07-2012, 11:12 AM

No blank values; only months "JANUARY, FEBRUARY ETC" and the word "MONTH" at the top.

**arlu1201** · 08-07-2012, 11:36 AM

Is your sheet containing your data named "MAIN" or something else?

Wherever you see "MAIN" in the code, you can change it to the sheet name.

**gcol** · 08-07-2012, 11:56 AM

The data sheet is called "MAIN".
If you cancel the error message it seems to have partially worked. Except for the may, september, october & november sheets which are blank.

**arlu1201** · 08-07-2012, 01:10 PM

Can you please check if any sheet name has an extra space either before the name or after?

If it still occurs, attach a sample file.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

**gcol** · 08-08-2012, 05:12 AM

Hello Arlette,
It's working now! The worksheet names didn't have any spaces before or after but I re-entered the month information in column I and it has cleared the error.
Many thanks for doing this for me,
Gareth

Copy rows from my main worksheet to another worksheet based on data in one cell

LinkBack

Thread Tools

Rate This Thread

Display

Copy rows from my main worksheet to another worksheet based on data in one cell

Re: Copy rows from my main worksheet to another worksheet based on data in one cell

Re: Copy rows from my main worksheet to another worksheet based on data in one cell

Re: Copy rows from my main worksheet to another worksheet based on data in one cell

Re: Copy rows from my main worksheet to another worksheet based on data in one cell

Re: Copy rows from my main worksheet to another worksheet based on data in one cell

Re: Copy rows from my main worksheet to another worksheet based on data in one cell

Re: Copy rows from my main worksheet to another worksheet based on data in one cell

Re: Copy rows from my main worksheet to another worksheet based on data in one cell

Re: Copy rows from my main worksheet to another worksheet based on data in one cell

Re: Copy rows from my main worksheet to another worksheet based on data in one cell

Re: Copy rows from my main worksheet to another worksheet based on data in one cell

Re: Copy rows from my main worksheet to another worksheet based on data in one cell

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions