CONDENSE INFORMATION FROM ONE WORKSHEET TO ANOTHER

[Guest]

On Worksheet #1:
In Column Y I have a list of codes
In Column Z, I have a list of weights, related directly to the codes (next
to them)
(not all codes will have a weight connected to it for each job)

In Worksheet #2:
I would like to create a summary only the codes that a weight related to it
(since there will always be more codes in worksheet #1, than I will ever fill
up for one job).

Thanks

 

[Ken Johnson]

On Worksheet #1:
In Column Y I have a list of codes
In Column Z, I have a list of weights, related directly to the codes (next
to them)
(not all codes will have a weight connected to it for each job)

In Worksheet #2:
I would like to create a summary only the codes that a weight related to it
(since there will always be more codes in worksheet #1, than I will ever fill
up for one job).

Thanks

Hi thaenn,

Try this...

Sub CodesUsed()
Const strDestination As String = "A1"
Dim lLastRow As Long
Application.ScreenUpdating = False
With Worksheets(1)
lLastRow = .Range("Z" & _
Range("Z:Z").Rows.Count).End(xlUp).Row
.Columns("Z:Z").AutoFilter _
Field:=1, Criteria1:="<>"
.Range("Y2:Y" & lLastRow).Copy _
Worksheets(2).Range(strDestination)
.Columns("Z:Z").AutoFilter
End With
End Sub

As it is the 'used codes' will appear on sheet #2 starting at A1. If
you are wanting a different starting cell then simply edit the value of
the constant strDestination, that appears in the first line. For
example if you are wanting the list of 'used codes' to start from B5
then change...

Const strDestination As String = "A1" to Const strDestination As String
= "B5"

Ken Johnson

 

[Guest]

Ken,

I am confused on this formula below....
Could you actually write out the formula (without the descriptions)?
Where should I write this formula? In the "destination" cells or the
"source" cells?
Thanks for your help.

 

[Ken Johnson]

Ken,

I am confused on this formula below....
Could you actually write out the formula (without the descriptions)?
Where should I write this formula? In the "destination" cells or the
"source" cells?
Thanks for your help.

Hi thaenn,

The solution I have offered is a VBA solution.

To get this VBA code working you need to follow these steps...

1. Copy the code below...

Sub CodesUsed()
'Change "A1" in next line of code to change
'where on Sheet2 the 'used code' values
'will start to appear.
Const strDestination As String = "A1"
Dim lLastRow As Long
Application.ScreenUpdating = False
With Worksheets(1)
lLastRow = .Range("Z" & _
Range("Z:Z").Rows.Count).End(xlUp).Row
.Columns("Z:Z").AutoFilter _
Field:=1, Criteria1:="<>"
.Range("Y2:Y" & lLastRow).Copy _
Worksheets(2).Range(strDestination)
.Columns("Z:Z").AutoFilter
End With
End Sub

2. Go to your Excel workbook and press Alt + F11 to get into the Visual
Basic Editor. (If that doesn't get you into the Visual Basic Editor you
can go Tools|Macro|Visual Basic
Editor.)

3. In the Visual Basic Editor open up a new standard code Module by
going Insert|Module.

4. Paste the code you copied in step 1 into the code Module that
appears.

5. Read the three green comment lines and change the "A1" to suit your
needs.

6. Save.

7. You now need to check that your workbook will run VBA code by going
Tools|Macro|Security then make sure that the security level is Medium.
If it is already Medium then all you need to do is close the Visual
Basic Editor by pressing Alt + F11 or going File|Close and Return to
Microsoft Excel. However, if you needed to change the Security setting
to Medium from some other setting level then you need to Close the
workbook and reopen it so that the new Security setting will be
applied. When the workbook is reopened a dialog appears with three
buttons. The VBA code will be useable if you press the "Enable Macros"
button.

8. To run the code go Tools|Macro|Macros then look for the macro's name
in the list of macros and either double click its name or select it
then click the Run button.

Ken Johnson