Creating List of Data from Multiple Worksheets

**narrowgate88** · 05-13-2009, 11:05 AM

I am trying to create a list of residents, unit types, and unit numbers. I have 3 problems with the following code.

1) The code ignores sheets that have no value in the specified cell. I need it to return a blank cell for those in order to keep the data in each column matched with the sheet it came from.
2) The code includes data from hidden sheets. I only want to list data from unhidden sheets. And more specifically, I want to omit data from unhidden sheets that have their tab colored black.
3) The code includes data from the sheet named "Totals" which I thought I was telling it to ignore.

Please Login or Register  to view this content.

**vikas.bhandari** · 05-13-2009, 11:50 AM

Hi there!

I appreciate the details which you have posted here. It doesn't look like the code is giving a logical conclusion. You have run the For Each loop three times. Or I am unable to understand anything. It would be really great if you can attach the file here.

If the file is heavy, you can send it @ my email id : [email protected]

After looking at the file, I can find out the possible solution for your problem.

Thanks,
Vikas

**narrowgate88** · 05-13-2009, 12:25 PM

My file had tons of private info in it, so here is a small version of it after replacing all the private info.

I don't mind if I need to add an additional column of "sheet names" to the summary sheet, in order to get the info on the right line, because I can add code to delete that out later.

**Special-K** · 05-13-2009, 07:47 PM

3) Your sheet name is "TOTALS" (uppercase) which is not the same as "Totals" so the macro will copy the data from the TOTALS spreadsheet, that's why

**vikas.bhandari** · 05-14-2009, 08:07 AM

I am really not sure about your first problem. However, your other two problems are solved in the following code. If you can explain the first query, I can help you then.

Please Login or Register  to view this content.

HTH

**narrowgate88** · 05-14-2009, 09:31 AM

Hadn't thought about the case issue, so thank you. This code did work to ignore the hidden sheets, but it didn't ignore the sheet with a black tab. Is there a way to do that?

As for the first issue, I am wanting to have a list that says Unit Type, Unit Number, and Resident. I need each column in the row to have come from the same sheet. Right now, because some sheets have no value in one of the cells, it is producing a result that has the wrong resident listed with a particular unit. Does that help clarify?

**vikas.bhandari** · 05-14-2009, 09:36 AM

Originally Posted by narrowgate88

Hadn't thought about the case issue, so thank you. This code did work to ignore the hidden sheets, but it didn't ignore the sheet with a black tab. Is there a way to do that?

As for the first issue, I am wanting to have a list that says Unit Type, Unit Number, and Resident. I need each column in the row to have come from the same sheet. Right now, because some sheets have no value in one of the cells, it is producing a result that has the wrong resident listed with a particular unit. Does that help clarify?

Hi,

yes, that has made your problem clear. However, (in the same file which you attached earlier) produce a sample output which you want to obtain, it will certainly help me to solve the problem.

Thanks,
Vikas

**narrowgate88** · 05-14-2009, 09:48 AM

Result would look like this:

Row 1 = A=Duplex B=Unit 195 C=Resident 1
Row 2 = A=blank B= Unit 198 C=Resident 2
Row 3 = A=Duplex B=Unit 104 C=Resident3

**vikas.bhandari** · 05-15-2009, 01:35 AM

Originally Posted by narrowgate88

Result would look like this:

Row 1 = A=Duplex B=Unit 195 C=Resident 1
Row 2 = A=blank B= Unit 198 C=Resident 2
Row 3 = A=Duplex B=Unit 104 C=Resident3

Check the following code. I am assuming here that whenever you enter the data to output sheet, you want to clean the sheet first.

Let me know if the code doesn't work for you.

Please Login or Register  to view this content.

HTH,
Vikas

**narrowgate88** · 05-18-2009, 09:37 AM

Yes, this worked perfectly. Thank you.

Creating List of Data from Multiple Worksheets

LinkBack

Thread Tools

Rate This Thread

Display

Creating List of Data from Multiple Worksheets

Re: Creating List of Data from Multiple Worksheets

Re: Creating List of Data from Multiple Worksheets

Re: Creating List of Data from Multiple Worksheets

Re: Creating List of Data from Multiple Worksheets

Re: Creating List of Data from Multiple Worksheets

Re: Creating List of Data from Multiple Worksheets

Re: Creating List of Data from Multiple Worksheets

Re: Creating List of Data from Multiple Worksheets

Re: Creating List of Data from Multiple Worksheets

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions