Conditional copy of specific cells to another sheet

**Anra** · 07-28-2010, 10:42 AM

there are a few threads related to conditional copy but none are addressing the issue I have.

Have a master sheet , Would like to copy specific column values ( along with the cell formating) to another sheet in the same workbook if there is a "X" in a specific column.

there are multiple columns with X to be copied to mutiple worksheets

Sample file attached
For example Would like to copy from Master sheet to a sheet "Ent App" columns Number and Title based on the "X" in the column Ent App.Would like the formating of the cells to be the same as in the master sheet after copying.
Same for all other columns in the worsheet.

this is the first time with excel macros....Any help appreciated.

**jaslake** · 07-28-2010, 02:43 PM

Hi Anra

Try this code

Please Login or Register  to view this content.

Let me know of issues.

John

**Anra** · 07-28-2010, 11:32 PM

Thanks so much..just ran on the sample data and works perfectly..I will try it out on the real data and will let you know. Thanks again

**jaslake** · 07-29-2010, 12:10 AM

Hi Anra

Yes please. Let me know how it goes.

John

**Anra** · 07-29-2010, 01:19 PM

Hi John - It works great on the real data!! Thank you!!

I'm embedding each worksheet on to a ppt and could you pl let me know how to add to this script -
when a new sheet is created with filtered data it automatically updates the sheet ( as it does now)
& also creates a new file and updates the same data.

So each filter will have 1 sheet + 1new file

Example : From master sheet we have created "Ent App" sheet (happening now with filtered contents) in addition to this create a new file and add a sheet "Ent App" with the same filtered content.

Is this possible? Any help on this will be great...thanks!

**jaslake** · 07-29-2010, 02:27 PM

Hi Anra

A couple of things. I've no idea what this is

a ppt

Secondly, if I understand you correctly, you wish to filter the data for "Ent App" to it's worksheet as the code does now. Then you wish to create a new workbook called "Some Thing" that will have a worksheet in it called "Ent App". This new "Ent App" worksheet will contain the same data as the original copy of "Ent App". Is my understanding correct?

John

**Anra** · 07-29-2010, 02:59 PM

Yes that is correct and ppt - was power point..sorry

**jaslake** · 07-29-2010, 04:45 PM

Hi Anra

I think this code does as you requested

Please Login or Register  to view this content.

Please note there are two procedures here and they should both go in the same module. Also note that I've changed the previous code slightly so you'll need to replace it also. The code is in the attached.

Let me know of issues.

John

**Anra** · 07-30-2010, 07:25 AM

Many Thanks John..tried it out with my actual data and it works great!!

Some Files created are large with many rows..would it be possible to add some condition
to say that when the row count in the newly created file exceeds say 50 (testing purposes) to create
a new file which has header row + rows that are greater than 50...

No changes to the tab sheets created by the macro..just the files should add the confition

**jaslake** · 07-30-2010, 05:14 PM

Hi Anra

Glad it works for you.

Regarding this

Some Files created are large with many rows..would it be possible to add some condition
to say that when the row count in the newly created file exceeds say 50 (testing purposes) to create
a new file which has header row + rows that are greater than 50...

Are you saying that if in a new file to be created, the Row Count is going to be more that 50, then create a second file and split the file into two files? If this is the case, submit a file that exceeds row count of 50 to give me a file to test against.

John

**Anra** · 07-31-2010, 02:24 PM

Hi John - thats correct. Attached are 2 file. Sample file with apx 150 rows ( master file ) and another file Ent App.xls both for test... thanks a bunch

**jaslake** · 07-31-2010, 06:32 PM

Hi Anra

I'm traveling this weekend and next week. I'll have some but not much spare time. I'll look at this possibility for you. What would the second Ent App file be called...perhaps Ent App 1?

John

**Anra** · 07-31-2010, 11:16 PM

yes..that should be fine. if there are 140 rows for example..would like it to create 3 files Ent App1( 50 rows ) Ent App2 ( 50 rows) Ent App3 ( 40 rows). thank you!

**Anra** · 08-03-2010, 09:55 AM

Hi John - would appreciate any help on the rowcount section. Have no macro experience...I tried doing it myself and it started to mess with the existing code...please help

Thank you so much for all the help so far, I ran the macro on the final data set and it ran perfectly... Thank you!!

**jaslake** · 08-03-2010, 10:24 PM

Hi Anra

Sorry I haven't gotten back to you. I've been working with my son, daughter-in-law and two grandsons with their new home. Been working 10 hour days and haven't had the clarity of mind to work on your solution.

Going home on Thursday and will address your issue this weekend.

John

**Anra** · 08-04-2010, 06:52 AM

Hi John, Thank you!!

**jaslake** · 08-05-2010, 08:52 PM

Hi Anra

I've had this issue sorta surrounded several times. Still trying to nail down the problem of adding data to more that two sheets. Haven't given up as yet. Still working on it.

John

**jaslake** · 08-07-2010, 03:23 PM

Hi Anra

I've about gotten this sorted out. I have company this weekend but I'll try to post something late tomorrow or early Monday.

John

**jaslake** · 08-08-2010, 06:28 PM

Hi Anra

This code is included in the attached workbook. It appears to do as you requested.

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

I don't believe I've changed the first procedure but I won't guarantee it

. The second procedure has been changed to accommodate this

if there are 140 rows for example..would like it to create 3 files Ent App1( 50 rows ) Ent App2 ( 50 rows) Ent App3 ( 40 rows)

Try it and see if it does as you require. You can modify the procedure quite easily to do more or less than 50 rows. Simply change the 50's to "Whatever" and the 49's to one less that "Whatever".

Let me know of issues.

John

**Anra** · 08-11-2010, 01:45 AM

Will try and let you know..Thank you so much !

**Anra** · 10-22-2010, 03:14 PM

Hi John - Its been a while..got pulled into another project and could not try and get back. Apologies.
I ran the code I'm getting a debug error on the variable LR...
Can you please help me ? thanks so much.

**Anra** · 10-22-2010, 03:21 PM

LR = bk.Worksheets(ws.Name).Range("A" & Rows.Count).End(xlUp).Row - this line and also the heading colors are not geting copyed to the newly created spread sheets...

thanks again

**jaslake** · 10-22-2010, 03:31 PM

Hi Anra

It has been a while. I've trashed your folder so will need to work with the file attached to your thread.

1. The worksheet runs fine on my machine. What error message are you getting?

2. I'm color blind but my headings don't appear to have colors

Please do this. Upload the file you're using (with appropriate colors) and with the code. I'll take a look at it.

John

**Anra** · 10-25-2010, 10:31 AM

Hi John, File Attached.
1. the Macro is also in the file. I have currently set the row count to 20.
When I get passed the error with LR, the files are getting created works great.
2. Would like the headings/color coded rows to pick up the master file color coding.
IT151.xls is the file that was generated. i would like the file to look like IT151-sample.xls
- with the heading color picked up from MASTER sheet.

thanks so much, sorry for not being clear.
Anra

**jaslake** · 10-25-2010, 01:43 PM

Hi Anra

I've tested the code in Excel 2000 and Excel 2007 and I can't get this line of code to throw an error

Please Login or Register  to view this content.

What is the error message you're getting?

John

**jaslake** · 10-26-2010, 03:05 PM

Hi Anra

I've tested the attached quite thoroughly in Excel 2000 and 2007. I've not been able to duplicate the error you mentioned. If you still get the error, I'll need to know what the error message says.

I've added this line of code

Please Login or Register  to view this content.

If you wish to change the number of lies in each file, change it here.

I'll also point out, you need to run the Update procedure first. This procedure calls AddBook.

Let me know of issues.

John

Conditional copy of specific cells to another sheet

LinkBack

Thread Tools

Rate This Thread

Display

Conditional copy of specific cells to another sheet

Re: Conditional copy of specific cells to another sheet

Re: Conditional copy of specific cells to another sheet

Re: Conditional copy of specific cells to another sheet

Re: Conditional copy of specific cells to another sheet

Re: Conditional copy of specific cells to another sheet

Re: Conditional copy of specific cells to another sheet

Re: Conditional copy of specific cells to another sheet

Re: Conditional copy of specific cells to another sheet

Re: Conditional copy of specific cells to another sheet

Re: Conditional copy of specific cells to another sheet

Re: Conditional copy of specific cells to another sheet

Re: Conditional copy of specific cells to another sheet

Re: Conditional copy of specific cells to another sheet

Re: Conditional copy of specific cells to another sheet

Re: Conditional copy of specific cells to another sheet

Re: Conditional copy of specific cells to another sheet

Re: Conditional copy of specific cells to another sheet

Re: Conditional copy of specific cells to another sheet

Re: Conditional copy of specific cells to another sheet

Re: Conditional copy of specific cells to another sheet

Re: Conditional copy of specific cells to another sheet

Re: Conditional copy of specific cells to another sheet

Re: Conditional copy of specific cells to another sheet

Re: Conditional copy of specific cells to another sheet

Re: Conditional copy of specific cells to another sheet

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions