Macro to parse rows and columns and copy to respective worksheet

**martinmshin** · 11-30-2010, 11:39 AM

Hello,

First of all, thanks for helping with this. This is a great forum and resource.

I'm working on a project where I have an "input" sheet and various sheets for clothing inventory/sizes.

The input sheet has description and other misc data and inventory by size. (ie 10 units for size 6, 20 for size 7, so on)

What would like to accomplish is to have a macro that will parse all inventory on the input sheet and copy all the size 6 products to the "6" sheet, and then size '7", "8", so on."

To add more complexity, the input sheet might have sizes that vary between each product. ie shoes, mens will go from 7-12 and women from 5-10.

And lastly, to combine all the different sizes/inventory to 1 sheet, showing the size of the the product on a column.

Please see attached xls

Thanks again!

**stanleydgromjr** · 11-30-2010, 02:01 PM

martinmshin,

Welcome to the Excel Forum.

Detach/open workbook martinmshin - EF754984 - SDG15.xlsm and run macro CopyData.

**martinmshin** · 11-30-2010, 02:13 PM

Hi Stan,

thanks a lot for your help. Works great! I do have two more questions, could you change the range for data/descriptions to be from A-Z and size columns to be from AA to AZ (worksheets will be named from AA - AZ)? or show me where I need to tweak it?

Lastly, is there anything I could do in return? do you have an amazon wish list?

Thanks,

Martin

Originally Posted by stanleydgromjr

martinmshin,

Welcome to the Excel Forum.

Detach/open workbook martinmshin - EF754984 - SDG15.xlsm and run macro CopyData.

**stanleydgromjr** · 11-30-2010, 03:17 PM

Martin,

You are very welcome.

could you change the range for data/descriptions to be from A-Z and size columns to be from AA to AZ (worksheets will be named from AA - AZ)?

Can you supply another workbook for testing in the new format?

**martinmshin** · 11-30-2010, 03:49 PM

Attached is the example file. I only did products 1-5 and size fields AA-AE as an example but the macro should parse as many rows and size fields from AA-AZ exist. The first column (A) will always be populated, so if it's empty it should stop.

I filled a lot of columns with "test" as some columns might have data. Sorry for how messy it looks, it's not pretty or easy on the eyes.

Thanks again for all your efforts. They will save me hours, if not days!

**stanleydgromjr** · 11-30-2010, 07:24 PM

Martin,

Detach/open workbook martinmshin - EF754984 - V2 - SDG15.xlsm and run macro CopyDataV2.

**martinmshin** · 11-30-2010, 07:58 PM

Thanks again Stan. There's a small issue. The "combined" sheet column AB has "Size Field 26-51". Rather than entering the text, could you do an offset to the size header ($AA$1) on sheets AA-AZ, I would like to capture whatever the ($AA$1) header is on sheets AA-AZ.

Thanks a lot!

**martinmshin** · 11-30-2010, 09:43 PM

I think I got it...

Replaced this:
Worksheets("combined").Range("AB" & NRC).Value = "Size Field " & b - 1

with this:
Worksheets("combined").Range("AB" & NRC).Value = Worksheets(ws).Range("AA1").Value

Thanks so much Stan. I really appreciate it. This is a great macro!

**stanleydgromjr** · 11-30-2010, 09:54 PM

Martin,

Nice fix.

Thanks again for all your efforts. They will save me hours, if not days!

For my own personal information and record keeping:
1. How much time do you think my macro will save you in hours per day/week/month?
2. Please send your company name, phone number, and web site.

**stanleydgromjr** · 11-30-2010, 11:33 PM

Martin,

If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.