I have an input box that searches column 8. Ok, no problem. It pulls the data (copies it) and pastes it. Except that it's pasting some of the wrong data/rows.

For example, the input box is year but when I enter the year (e.g., 2007), it copies the rows with 2007 in column 8 but also brings me back other years as well (2006 rows gets mixed in). Is there a way to keep this from happening? it should only be searching row 8, not the other rows (and I can't see anything remotely close to these numbers in any other cells anyway). It's not that helpful if it keeps doing this (and it happens every single time I query).

(working.Cells(x,8).Value) = TheAnswer Then
working.Rows(x).EntireRow.Copy

Thanks.

Basically, is there a way to specifically exclude the other columns? I know it's only supposed to be searching column H (8), but it's obviously not. Would excluding the others specifically even help?

JonathanK1;1610807 Wrote:
I have an input box that searches column 8. Ok, no problem. It
pulls the data (copies it) and pastes it. Except that it's pasting
some of the wrong data/rows.

For example, the input box is year but when I enter the year (e.g.,
2007), it copies the rows with 2007 in column 8 but also brings me
back other years as well (2006 rows gets mixed in). Is there a way
to keep this from happening? it should only be searching row 8, not
the other rows (and I can't see anything remotely close to these
numbers in any other cells anyway). It's not that helpful if it
keeps doing this (and it happens every single time I query).

(working.Cells(x,8).Value) = TheAnswer Then
working.Rows(x).EntireRow.Copy

Thanks.

Basically, is there a way to specifically exclude the other columns?
I know it's only supposed to be searching column H (8), but it's
obviously not. Would excluding the others specifically even help?

This is doable! Please provide the code as asked for!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

How's this (below)? It opens perfectly in another workbook but when it pulls by the input box (column H/8), it's bringing in rows that shouldn't be there. For example, if I enter 2007 for the year, 50 or so rows will copy over that match my query....but a handful will be from 2006. Isthere a way to keep it from doing that? It's pulling the data I want along with some data I DON'T want and didn't ask for. Strange. If I can't fix this, it's not much good to me.

As always, I appreciate everyone's help.

---

Sub Button6_Click()

Dim TheAnswer As String

Dim working As Worksheet, dumping As Workbook

Set working = ActiveSheet

TheAnswer = LCase$(InputBox("Enter Year below"))

Set dumping = Workbooks.Add

For x = 1 To 17

working.Rows(x).EntireRow.Copy

dumping.Activate

ActiveSheet.Paste

ActiveCell.Offset(1).Select

Next

For x = 1 To working.Cells.SpecialCells(xlCellTypeLastCell).Row

If LCase$(working.Cells(x, 8).Value) = TheAnswer Then

working.Rows(x).EntireRow.Copy

dumping.Activate

ActiveSheet.Paste

ActiveCell.Offset(1).Select

End If

Next

Application.CutCopyMode = False

Cells.Sort Key1 etc. etc.

Ok, the 1st loop puts 17 rows (unconditionally) into the target wkb.
The 2nd loop puts only the rows that match your criteria in col8,
looping every row in the source wkb.

Paste this code into a standard module and call it from your
Button6_Click procedure like so...

Sub Button6_Click()
Call CopyYearData
End Sub

Sub CopyYearData()
Dim vData, vDataOut(), vAns, n&, j&, k#, lCols&, lNextRow&

vData = ActiveSheet.UsedRange: lCols = UBound(vData, 2)
vAns = Application.InputBox("Enter the year", Type:=1)
k = WorksheetFunction.CountIf(Columns(8), vAns)

ReDim vDataOut(1 To k, 1 To lCols) '//dimension the 2D output array
For n = LBound(vData) To UBound(vData)
If vData(n, 8) = vAns Then
lNextRow = lNextRow + 1
For j = 1 To lCols: vDataOut(lNextRow, j) = vData(n, j): Next 'j
End If 'vData(n, 8) = vAns
If lNextRow = k Then Exit For '//escape when done
Next 'n
Range("A10").Resize(UBound(vDataOut), UBound(vDataOut, 2)) = vDataOut
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Oops.., I forgot you want to put the data in a new wkb. Revise as shown
below...

Sub CopyYearData()
Dim vData, vDataOut(), vAns, n&, j&, k#, lCols&, lNextRow&

vData = ActiveSheet.UsedRange: lCols = UBound(vData, 2)
vAns = Application.InputBox("Enter the year", Type:=1)
k = WorksheetFunction.CountIf(Columns(8), vAns)

ReDim vDataOut(1 To k, 1 To lCols) '//dimension the 2D output array
For n = LBound(vData) To UBound(vData)
If vData(n, 8) = vAns Then
lNextRow = lNextRow + 1
For j = 1 To lCols: vDataOut(lNextRow, j) = vData(n, j): Next
'j
End If 'vData(n, 8) = vAns
If lNextRow = k Then Exit For '//escape when done
Next 'n
Workbooks.Add
Cells(1).Resize(UBound(vDataOut), UBound(vDataOut, 2)) = vDataOut
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Jonathan,

Am Mon, 1 Apr 2013 10:45:08 +0000 schrieb JonathanK1:

For example, the input box is year but when I enter the year (e.g.,
2007), it copies the rows with 2007 in column 8 but also brings me back
other years as well (2006 rows gets mixed in). Is there a way to keep
this from happening? it should only be searching row 8, not the other
rows (and I can't see anything remotely close to these numbers in any
other cells anyway). It's not that helpful if it keeps doing this (and
it happens every single time I query).

filter column 8 by the answer, copy the visible cells

Sub Filter()
Dim Answer As Integer
Dim LRow As Long

With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Answer = Application.InputBox("Select a year", "Year", Type:=1)
If Answer = False Then Exit Sub

With .Range("A1:K" & LRow)
.AutoFilter Field:=8, Criteria1:=Answer
.SpecialCells(xlCellTypeVisible).Copy _
Sheets("Sheet2").Range("A1")
End With
.AutoFilterMode = False
End With
End Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Firstly, the InputBox does not copy/paste!

Secondly, the code sample you provided doesn't fit your explanation of
what you're trying to do. Please provide more details and all the
code...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion