Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro or Formula that copies and moves data?
Hello,
Example data: BATCH ORDER# ASSIGNED NUMBERS From To 1 1 30 1 55 60 2 1 30 Let's say the the columns are full to 100 entries of this data. I want to be able to either by formula or macro have on another sheet...first to look at the Batch#..which in this case will find 1 then look at the next to cells which says to look at the data for number 1 through number 30 and find this on another sheet and place all the data listed under batch 1 number 1, batch 1 number 2....batch 1 number 30. I hope this makes sense. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro or Formula that copies and moves data?
I put my data on two sheets.
On the first sheet, I put the batch number in column A, the From Number in column B and the To number in column C. On the second sheet, I put the batch number in column A. The assigned number in column B. Then I added a button from the forms toolbar to the first sheet. Click the button and the macro assigned to it will remove the existing data|filter|autofilter from sheet2. Then it'll filter on column A to match the batch numbers. Then it'll filter on column B for numbers between the From and To. And I applied the filter arrows to column A to column X--change that if you want. If your data is laid out differently, you'll have to modify this code: Option Explicit Sub testme01() Dim BatchNumber As Long Dim BatchFrom As Long Dim BatchTo As Long Dim OtherWks As Worksheet Dim myRngToFilter As Range Set OtherWks = Worksheets("sheet2") With ActiveCell.EntireRow 'pick values from from columns A:C of the activecell's row BatchNumber = .Cells(1, 1).Value BatchFrom = .Cells(1, 2).Value BatchTo = .Cells(1, 3).Value End With With OtherWks 'remove any existing autofilter .AutoFilterMode = False Set myRngToFilter = .Range("a1:x" & _ .Cells(.Rows.Count, "A").End(xlUp).Row) myRngToFilter.AutoFilter field:=1, Criteria1:=BatchNumber myRngToFilter.AutoFilter field:=2, Criteria1:="=" & BatchFrom, _ Operator:=xlAnd, Criteria2:="<=" & BatchTo If myRngToFilter.Columns(1).Cells _ .SpecialCells(xlCellTypeVisible).Count = 1 Then Beep MsgBox "nothing found!" 'remove the filter??? '.AutoFilterMode = False Else Application.Goto OtherWks.Range("a1"), scroll:=True End If End With End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm A.S. wrote: Hello, Example data: BATCH ORDER# ASSIGNED NUMBERS From To 1 1 30 1 55 60 2 1 30 Let's say the the columns are full to 100 entries of this data. I want to be able to either by formula or macro have on another sheet...first to look at the Batch#..which in this case will find 1 then look at the next to cells which says to look at the data for number 1 through number 30 and find this on another sheet and place all the data listed under batch 1 number 1, batch 1 number 2....batch 1 number 30. I hope this makes sense. Thanks. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for multiple open files | Excel Discussion (Misc queries) | |||
macro loop | Excel Discussion (Misc queries) |