EXCEL VBA MOVING DATA 1

[WBURKERT]

Please be kind and help me out.
I have a workbook with two worksheets. The first worksheet is all of my data, which is a parts list. Some parts exist in two sections. I have a column for the first section number and a second column for the second section.

I want to copy parts from 'my all data' worksheet to another worksheet that are in Section 1 and then copy all the parts that are in Section 2 and then sort by section number and then part number.

Example:
p/n S1 S2
x 1 3
y 2
z 2 3

Output
x
y
z
y
x
y

Hope this makes sense and hope that someone can get me pointed in the right direction. I have 5000 parts and some parts exist in two sections and I don't wnat to maintain two descriptions and pricing. Thanks in advance, Will Burkert

 

[ItIsHardToProgram]

I want to copy parts from 'my all data' worksheet to another worksheet

What worksheet?

and then copy all the parts that are in Section 2

Where?

and then sort by section number and then part number.

There is a sorting function in excel

I have 5000 parts and some parts exist in two sections and I don't wnat to maintain two descriptions and pricing.

Dosn't this go in the opposite of what you are trying to achieve?

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[WBURKERT]

In my workbook "ALL PARTS" I have two worksheets. The first worksheet is named ALLPARTS and the second worksheet is named BY SECTION.

Some parts exist in two sections. COLUMN HEADINGS in 'ALLPARTS' are PN DESCRIP PRICE S1 S2 S3

ROW 1 X book $10.00 1 3
ROW 2 Y binder $1.00 2
ROW 3 Z paper $1.50 2 3

IN WORKSHEET BY SECTION
COLUMN HEADINGS ARE PN DESCRIPTION PRICE SECTION

The expected output in worksheet BY SECTION I would like to see

ROW 1 X book $10.00 1
ROW 2 Y binder $1.00 2
ROW 3 Z paper $1.50 2
ROW 4 X book $10.00 3
ROW 5 Z paper $1.50 3

As items in ALLPARTS change, get added or removed BY SECTION will update. Lastly, I would like to sort the BY SECTION worksheet by the SECTION.

 

[ItIsHardToProgram]

I do not suggest running a code on every update, as it is very heavy for nothing.

So basicaly, I get what you are asking, but when a specific part appears in more than 1 section, the data becomes redondant, if the price is the same as you mention, I don't see the necessity of sections.

I have some thing I need to do, I will get back to you in about half an hour with the appropriate code, unless some one beats me to it.


"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

This is not perfect, but it does what you want in the simplest way.

All ranges and references have been hardcoded and need to be changed if the worksheet has a different layout than the one pointed.

By the way, skipvought, or another good programmer can probably do the same with 5 lines of code. I am not that talented.

Option Explicit
Sub DividingInSection()

Dim iRow As Integer
Dim iColumn As Integer

Dim i As Integer, y As Integer
i = 1
y = 1

For iRow = 1 To 5000
      If Worksheets("ALLPARTS").Cells(iRow, 4).Text = "1" Then

           With Worksheets("BY SECTION")

               .Cells(i, 1) = Worksheets("ALLPARTS").Cells(iRow, 1)
               .Cells(i, 2) = Worksheets("ALLPARTS").Cells(iRow, 2)
               .Cells(i, 3) = Worksheets("ALLPARTS").Cells(iRow, 3)
               .Cells(i, 4) = "Section 1"
           End With
           i = i + 1
      End If
           
      If Worksheets("ALLPARTS").Cells(iRow, 5).Text = "2" Then
           
           With Worksheets("BY SECTION")

               .Cells(i, 1) = Worksheets("ALLPARTS").Cells(iRow, 1)
               .Cells(i, 2) = Worksheets("ALLPARTS").Cells(iRow, 2)
               .Cells(i, 3) = Worksheets("ALLPARTS").Cells(iRow, 3)
               .Cells(i, 4) = "Section 2"
           End With
           i = i + 1
      End If
      
      If Worksheets("ALLPARTS").Cells(iRow, 6).Text = "3" Then
           
           With Worksheets("BY SECTION")

               .Cells(i, 1) = Worksheets("ALLPARTS").Cells(iRow, 1)
               .Cells(i, 2) = Worksheets("ALLPARTS").Cells(iRow, 2)
               .Cells(i, 3) = Worksheets("ALLPARTS").Cells(iRow, 3)
               .Cells(i, 4) = "Section 3"
           End With
           i = i + 1
      End If
      
Next iRow

End Sub

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[WBURKERT]

This has worked out great, thank-you (and sorry for the late response)

 

[SkipVought]

hi,

Part of your problem is your data organization. Your data is not normalized.

A better structure would be,
[tt]
p/n Sect Value
x S1 1
x S2 3
y S2 2
z S1 2
z S2 3
[/tt]
I normalized this data using faq68-5287.

Then use the Pivot Table wizard to select either section to display on your sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}