Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
CONDENSE INFORMATION FROM ONE WORKSHEET TO ANOTHER
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
CONDENSE INFORMATION FROM ONE WORKSHEET TO ANOTHER
thaenn wrote:
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
CONDENSE INFORMATION FROM ONE WORKSHEET TO ANOTHER
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" wrote: thaenn wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
CONDENSE INFORMATION FROM ONE WORKSHEET TO ANOTHER
thaenn wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calling up information from a different worksheet. | Excel Worksheet Functions | |||
how can I group information from more than one worksheet | Excel Worksheet Functions | |||
How do i add to information to microsoft exel worksheet | Excel Worksheet Functions | |||
Pull information from one worksheet to another | Excel Discussion (Misc queries) | |||
Can I sync information on one worksheet with another worksheet | Excel Worksheet Functions |