| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
I need help with a macro please!
This bombs at the end - with a no cells were found message.
My objective is to copy a file to another file and delete the blank rows - where company is blank. - Thanks for any help.. Here's an example of my file: COMPANY VENDOR INV DESCRPT INV_AMT 3042 40315-3042 04/10 Sales Tax 0 0 1 3087 40315-3087 04/10 Sales Tax 806.48 806.48 3201 40315-3201 04/10 Sales Tax 0 0 1 3275 40315-3275 04/10 Sales Tax 27371.44 27371.44 1 3468 40315-3468 04/10 Sales Tax 155.62 155.62 Range("A1").Select Sheets("Inv_Load to Lawson").Select Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Workbooks.Open Filename:= _ "X:\Legal to Accounting Check Requests\Sales Tax\slsTax dbapcvi.csv" Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWorkbook.SaveAs Filename:= _ "X:\Legal to Accounting Check Requests\Sales Tax\slsTax dbapcvi.csv", FileFormat _ :=xlCSV, CreateBackup:=False Set r = Range("A2:A50") Set rr = r.SpecialCells(xlCellTypeBlanks) rr.EntireRow.Delete |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I need help with a macro please!
Don,
Now I get an application defined or object defined error on the activesheet.pastSpecial statement..... "Don Guillett" wrote: Try it this way. Sheets("Inv_Load to Lawson").Range("A1").End(xlToRight)).End(xlDown)). Copy Workbooks.Open Filename:= _ "X:\Legal to Accounting Check Requests\Sales Tax\slsTax dbapcvi.csv" activesheet.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False activesheet.Range("A2:A50").SpecialCells(xlCellTyp eBlanks).EntireRow.Delete ActiveWorkbook.Save Don Guillett Microsoft MVP Excel SalesAid Software "pm" wrote in message ... This bombs at the end - with a no cells were found message. My objective is to copy a file to another file and delete the blank rows - where company is blank. - Thanks for any help.. Here's an example of my file: COMPANY VENDOR INV DESCRPT INV_AMT 3042 40315-3042 04/10 Sales Tax 0 0 1 3087 40315-3087 04/10 Sales Tax 806.48 806.48 3201 40315-3201 04/10 Sales Tax 0 0 1 3275 40315-3275 04/10 Sales Tax 27371.44 27371.44 1 3468 40315-3468 04/10 Sales Tax 155.62 155.62 Range("A1").Select Sheets("Inv_Load to Lawson").Select Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Workbooks.Open Filename:= _ "X:\Legal to Accounting Check Requests\Sales Tax\slsTax dbapcvi.csv" Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWorkbook.SaveAs Filename:= _ "X:\Legal to Accounting Check Requests\Sales Tax\slsTax dbapcvi.csv", FileFormat _ :=xlCSV, CreateBackup:=False Set r = Range("A2:A50") Set rr = r.SpecialCells(xlCellTypeBlanks) rr.EntireRow.Delete . |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I need help with a macro please!
Hi Don,
I had to take out the second set of parenthesis in the first statement (xlToRight)) and (xlDown)) - to be able to run the macro; however it's not copying the data to the other file... "Don Guillett" wrote: Try it this way. Sheets("Inv_Load to Lawson").Range("A1").End(xlToRight)).End(xlDown)). Copy Workbooks.Open Filename:= _ "X:\Legal to Accounting Check Requests\Sales Tax\slsTax dbapcvi.csv" activesheet.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False activesheet.Range("A2:A50").SpecialCells(xlCellTyp eBlanks).EntireRow.Delete ActiveWorkbook.Save Don Guillett Microsoft MVP Excel SalesAid Software "pm" wrote in message ... This bombs at the end - with a no cells were found message. My objective is to copy a file to another file and delete the blank rows - where company is blank. - Thanks for any help.. Here's an example of my file: COMPANY VENDOR INV DESCRPT INV_AMT 3042 40315-3042 04/10 Sales Tax 0 0 1 3087 40315-3087 04/10 Sales Tax 806.48 806.48 3201 40315-3201 04/10 Sales Tax 0 0 1 3275 40315-3275 04/10 Sales Tax 27371.44 27371.44 1 3468 40315-3468 04/10 Sales Tax 155.62 155.62 Range("A1").Select Sheets("Inv_Load to Lawson").Select Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Workbooks.Open Filename:= _ "X:\Legal to Accounting Check Requests\Sales Tax\slsTax dbapcvi.csv" Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWorkbook.SaveAs Filename:= _ "X:\Legal to Accounting Check Requests\Sales Tax\slsTax dbapcvi.csv", FileFormat _ :=xlCSV, CreateBackup:=False Set r = Range("A2:A50") Set rr = r.SpecialCells(xlCellTypeBlanks) rr.EntireRow.Delete . |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I need help with a macro please!
Don,
This would work perfectly if I could get the copy to the CSV file...any other suggestions? Thanks. "Don Guillett" wrote: Try it this way. Sheets("Inv_Load to Lawson").Range("A1").End(xlToRight)).End(xlDown)). Copy Workbooks.Open Filename:= _ "X:\Legal to Accounting Check Requests\Sales Tax\slsTax dbapcvi.csv" activesheet.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False activesheet.Range("A2:A50").SpecialCells(xlCellTyp eBlanks).EntireRow.Delete ActiveWorkbook.Save Don Guillett Microsoft MVP Excel SalesAid Software "pm" wrote in message ... This bombs at the end - with a no cells were found message. My objective is to copy a file to another file and delete the blank rows - where company is blank. - Thanks for any help.. Here's an example of my file: COMPANY VENDOR INV DESCRPT INV_AMT 3042 40315-3042 04/10 Sales Tax 0 0 1 3087 40315-3087 04/10 Sales Tax 806.48 806.48 3201 40315-3201 04/10 Sales Tax 0 0 1 3275 40315-3275 04/10 Sales Tax 27371.44 27371.44 1 3468 40315-3468 04/10 Sales Tax 155.62 155.62 Range("A1").Select Sheets("Inv_Load to Lawson").Select Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Workbooks.Open Filename:= _ "X:\Legal to Accounting Check Requests\Sales Tax\slsTax dbapcvi.csv" Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWorkbook.SaveAs Filename:= _ "X:\Legal to Accounting Check Requests\Sales Tax\slsTax dbapcvi.csv", FileFormat _ :=xlCSV, CreateBackup:=False Set r = Range("A2:A50") Set rr = r.SpecialCells(xlCellTypeBlanks) rr.EntireRow.Delete . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I need help with a macro please!
I thought your other thread got you to that point.
pm wrote: Don, This would work perfectly if I could get the copy to the CSV file...any other suggestions? Thanks. "Don Guillett" wrote: Try it this way. Sheets("Inv_Load to Lawson").Range("A1").End(xlToRight)).End(xlDown)). Copy Workbooks.Open Filename:= _ "X:\Legal to Accounting Check Requests\Sales Tax\slsTax dbapcvi.csv" activesheet.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False activesheet.Range("A2:A50").SpecialCells(xlCellTyp eBlanks).EntireRow.Delete ActiveWorkbook.Save Don Guillett Microsoft MVP Excel SalesAid Software "pm" wrote in message ... This bombs at the end - with a no cells were found message. My objective is to copy a file to another file and delete the blank rows - where company is blank. - Thanks for any help.. Here's an example of my file: COMPANY VENDOR INV DESCRPT INV_AMT 3042 40315-3042 04/10 Sales Tax 0 0 1 3087 40315-3087 04/10 Sales Tax 806.48 806.48 3201 40315-3201 04/10 Sales Tax 0 0 1 3275 40315-3275 04/10 Sales Tax 27371.44 27371.44 1 3468 40315-3468 04/10 Sales Tax 155.62 155.62 Range("A1").Select Sheets("Inv_Load to Lawson").Select Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Workbooks.Open Filename:= _ "X:\Legal to Accounting Check Requests\Sales Tax\slsTax dbapcvi.csv" Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWorkbook.SaveAs Filename:= _ "X:\Legal to Accounting Check Requests\Sales Tax\slsTax dbapcvi.csv", FileFormat _ :=xlCSV, CreateBackup:=False Set r = Range("A2:A50") Set rr = r.SpecialCells(xlCellTypeBlanks) rr.EntireRow.Delete . -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
| need help to update macro to office 2007 macro enabled workbook | Excel Discussion (Misc queries) | |||
| Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
| Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
Linear Mode
