|
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 |
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 . |
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 . |
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 . |
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 |
| All times are GMT +1. The time now is 12:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com