question

[derekc]

i have this code working perfectly:

Windows("whatever.xls").Activate
Sheets("sheet1").Select
Shipqty = Range("G23")
If Shipqty > 1 Then ActiveSheet.Range("A23:Q23").Select
Selection.Copy
ActiveWindow.ActivateNext
ActiveWindow.WindowState = xlNormal
ActiveSheet.Range("A23:Q23").Select
ActiveSheet.Paste


bascially its check to see if something is in G23, if it is it copie
the row and paste it on another sheet i have created in the same spot.

heres my problem. it paste it in the same row and everything. i have
sheet with 334 rows of parts, and im condensing this on another sheet
cause we dont select all the parts each week. so basically if i have
row i need to copy in row 18 and row 300, i can have it pasted in 1
and 300. i need them in the first emtpy row. so can i have it paste i
the first empty row? also is there a way i can make this continue t
check every row for the next 334 rows for the same this and paste th
same way in the first empty row? cause i dont feel like writing thi
code 334 times. is there a loop i can do or something, im still novic
so i dont know all of excels potential with this kinda stuff. any hel
will be nice. thank

 

[KKindle]

Try this ... does all at once without resorting to Looping

Windows("whatever.xls").Activate
Sheets("sheet1").Select
Cells.Select
'Filter on column G values greater than zero ... change if
necessary
Selection.AutoFilter Field:=7, Criteria1:=">0",
Operator:=xlAnd
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Selection.AutoFilter


HTH

-KKindle

 

[derekc]

gives me an error on range a1. heres the code in full. i didnt think i
would atter if i posted this section or not but the first part isn
pretty mucha nything, but if anything is effecting it. you can check
guess. i appreciate the help.

Range("A1:Q22").Select
Selection.Copy
Workbooks.Add
ActiveSheet.Range("A1:Q22").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone
SkipBlanks:=False _
, Transpose:=False
ActiveSheet.Paste
ActiveSheet.Columns("A:A").ColumnWidth = 5.86
ActiveSheet.Columns("B:B").ColumnWidth = 20.57
ActiveSheet.Columns("C:C").ColumnWidth = 34.14
ActiveSheet.Columns("D").ColumnWidth = 9.57
ActiveSheet.Columns("E:E").ColumnWidth = 7.86
ActiveSheet.Columns("F:F").ColumnWidth = 8.14
ActiveSheet.Columns("G:G").ColumnWidth = 9.71
ActiveSheet.Columns("H:H").ColumnWidth = 9
ActiveSheet.Columns("I:I").ColumnWidth = 6.29
ActiveSheet.Columns("J:J").ColumnWidth = 6
ActiveSheet.Columns("K:K").ColumnWidth = 7.29
ActiveSheet.Columns("L:L").ColumnWidth = 15.71
ActiveSheet.Columns("M:M").ColumnWidth = 7.29
ActiveSheet.Columns("N:N").ColumnWidth = 11
ActiveSheet.Columns("N:N").ColumnWidth = 10.71
ActiveSheet.Columns("O:O").ColumnWidth = 10.57
ActiveSheet.Columns("P").ColumnWidth = 10.14
ActiveSheet.Columns("Q:Q").ColumnWidth = 10.71
ActiveSheet.Rows("1:5").RowHeight = 12
ActiveSheet.Rows("6:6").RowHeight = 33
ActiveSheet.Rows("7:9").RowHeight = 19.5
ActiveSheet.Rows("10:14").RowHeight = 19.5
ActiveSheet.Rows("15:15").RowHeight = 20.25
ActiveSheet.Rows("16:18").RowHeight = 19.5
ActiveSheet.Rows("19:19").RowHeight = 18.75
ActiveSheet.Rows("20:20").RowHeight = 3.75
ActiveSheet.Rows("21:21").RowHeight = 13.5
ActiveSheet.Rows("22:22").RowHeight = 42
ActiveSheet.Rows("23:50").RowHeight = 19.5
ActiveWindow.Zoom = 85

'Windows("Dereks Doohickey.xls").Activate
'Sheets("sheet1").Select
'Shipqty = Range("G23").Select
'If Shipqty > 1 Then ActiveSheet.Range("A23:Q23").Select
'Selection.Copy
'ActiveWindow.ActivateNext
'ActiveWindow.WindowState = xlNormal
'ActiveSheet.Range("A23:Q23").Select
'ActiveSheet.Paste


thats pretty much my code i already posted at the end. yous wil
replace it. but yes, it gives an error on the range a1 select. i go
ird of that, and it kinda screwed sheet up. i think cause it lik
selected the least cell possible and also deleted some stuff. an
clues

 

[derekc]

anyone else have any ideas