Extract data to new sheet

[pgarcia]

Hello all,
1. I need to filter the follow: GDL, MTY, QRO and SLW from a sheet.
2. Copy only the filtered data to clipboard (columns A-H).
3. Delete the rows with the filtered data from that sheet.
Then
1. Open new sheet
2. Paste data to that sheet.
3. Delete rows (in column C) that have no data.

Thanks (the Naives) Office 2000

 

[Ron de Bruin]

Hi pgarcia

You can use EasyFilter
http://www.rondebruin.nl/easyfilter.htm

If you want code you must use AdvancedFilter

 

[pgarcia]

Nice!!!
How do you make "Add-In's"?

One thing, when using the filter and delete row, it only filters the row, it
does not delete it. Is that correct?

One last question for the day, I need to go to the last cell in column C
with data, go down one cell, select that row and all the rows below and
delete them.

Thanks and thank for the Add-In

 

[pgarcia]

I just saw some thing strange. The Add-In copys over the colums A-C fine, but
then the other colums have incorrect data. Thanks.

Did I mention that I was added another VB code to this? It's not done, but I
just want the use to just run on VB code. That's why I was looking to
1. I need to filter the follow: GDL, MTY, QRO and SLW from a sheet.
2. Copy only the filtered data to clipboard (columns A-H).
3. Delete the rows with the filtered data from that sheet.
Then
1. Open new sheet
2. Paste data to that sheet.
3. Delete rows (in column C) that have no data.
Then run the following:
(I aslo need help with the end part)
Sub A_1600()

Range("A1").Select
Rows("1:1").Select
Selection.ClearContents
Columns("I:N").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
ActiveCell.FormulaR1C1 = "ORG"
Range("B1").Select
ActiveCell.FormulaR1C1 = "DSTN"
Range("C1").Select
ActiveCell.FormulaR1C1 = "BAX P O/N"
Range("D1").Select
ActiveCell.FormulaR1C1 = "227 kgs"
Range("E1").Select
ActiveCell.FormulaR1C1 = "454 kgs"
Range("F1").Select
ActiveCell.FormulaR1C1 = "BAX O/N"
Range("G1").Select
ActiveCell.FormulaR1C1 = "227 kgs"
Range("H1").Select
ActiveCell.FormulaR1C1 = "454 kgs"
Range("I1").Select
Selection.NumberFormat = "0.00"
ActiveCell.FormulaR1C1 = "BAX 2 Day"
Range("J1").Select
Selection.NumberFormat = "0.00"
ActiveCell.FormulaR1C1 = "227 kgs"
Range("K1").Select
Selection.NumberFormat = "0.00"
ActiveCell.FormulaR1C1 = "454 kgs"
Range("L1").Select
Selection.NumberFormat = "0.00"
ActiveCell.FormulaR1C1 = "BAX Ground"
Range("M1").Select
Selection.NumberFormat = "0.00"
ActiveCell.FormulaR1C1 = "227 kgs"
Range("N1").Select
Selection.NumberFormat = "0.00"
ActiveCell.FormulaR1C1 = "454 kgs"
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Font.ColorIndex = 2
With Selection.Interior
.ColorIndex = 55
.Pattern = xlSolid
End With
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False

Dim r As Long
r = Cells.Find(What:="Grand Total", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
With Range("A" & r & ":W" & r).Font
.Bold = True
.Size = 12
.ColorIndex = 5
End With



End With
Range("O2").Select
ActiveCell.FormulaR1C1 = "2.2046"
Range("O3").Select
ActiveCell.FormulaR1C1 = "100"
Range("O2").Select
Selection.Copy
Range("C:H", Range ("C:H").End (xlDown)).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlMultiply,
SkipBlanks _
:=False, Transpose:=False
Range("O3").Select
Selection.Copy
Range("C:H", Range ("C:H").End (xlDown)).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlDivide, SkipBlanks _
:=False, Transpose:=False
Range("C:H", Range ("C:H").End (xlDown)).Select
Selection.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(*
""-""??_);_(@_)"
Range("A2").Select
End Sub

 

[Ron de Bruin]

How do you make "Add-In's"?
See
http://www.jkp-ads.com/Articles/DistributeMacro00.htm

One thing, when using the filter and delete row, it only filters the row, it
does not delete it. Is that correct?

Must see the workbook

One last question for the day, I need to go to the last cell in column C
with data, go down one cell, select that row and all the rows below and
delete them.

Try this one

Sub test()
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
.Rows(LastRow + 1 & ":" & .Rows.Count).Delete
End With
End Sub