ExcelBanter - View Single Post - Macro or Formula that copies and moves data?

Dave Peterson

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