Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract data to new sheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract data to new sheet
Hi pgarcia
You can use EasyFilter http://www.rondebruin.nl/easyfilter.htm If you want code you must use AdvancedFilter -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pgarcia" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract data to new sheet
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 "Ron de Bruin" wrote: Hi pgarcia You can use EasyFilter http://www.rondebruin.nl/easyfilter.htm If you want code you must use AdvancedFilter -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pgarcia" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract data to new sheet
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 "pgarcia" wrote: 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 "Ron de Bruin" wrote: Hi pgarcia You can use EasyFilter http://www.rondebruin.nl/easyfilter.htm If you want code you must use AdvancedFilter -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pgarcia" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract data to new sheet
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pgarcia" wrote in message ... 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 "Ron de Bruin" wrote: Hi pgarcia You can use EasyFilter http://www.rondebruin.nl/easyfilter.htm If you want code you must use AdvancedFilter -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "pgarcia" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how could i extract data from another sheet like list box | Excel Worksheet Functions | |||
extract data from range,and place in the same sheet | Excel Discussion (Misc queries) | |||
Macro to extract data and paste to a new sheet | Excel Programming | |||
Help me, compare 2 sheet and extract the match data into the new sheet. | Excel Discussion (Misc queries) | |||
Best way to extract data on one sheet & populate it on another sheet?????? | Excel Programming |