I need help with a macro please!
in [General]
|
Prev: IF Formula
Next: MAX IF
From: pm on 17 May 2010 18:23 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
From: Don Guillett on 17 May 2010 18:45 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(xlCellTypeBlanks).EntireRow.Delete ActiveWorkbook.Save Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "pm" <pm(a)discussions.microsoft.com> wrote in message news:6456AC58-6C30-4C40-82D4-D6CE802CD366(a)microsoft.com... > 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
From: pm on 19 May 2010 09:49 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(xlCellTypeBlanks).EntireRow.Delete > ActiveWorkbook.Save > > Don Guillett > Microsoft MVP Excel > SalesAid Software > dguillett(a)gmail.com > "pm" <pm(a)discussions.microsoft.com> wrote in message > news:6456AC58-6C30-4C40-82D4-D6CE802CD366(a)microsoft.com... > > 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 > > . >
From: pm on 19 May 2010 14:49 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(xlCellTypeBlanks).EntireRow.Delete > ActiveWorkbook.Save > > Don Guillett > Microsoft MVP Excel > SalesAid Software > dguillett(a)gmail.com > "pm" <pm(a)discussions.microsoft.com> wrote in message > news:6456AC58-6C30-4C40-82D4-D6CE802CD366(a)microsoft.com... > > 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 > > . >
From: pm on 19 May 2010 18:31
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(xlCellTypeBlanks).EntireRow.Delete > ActiveWorkbook.Save > > Don Guillett > Microsoft MVP Excel > SalesAid Software > dguillett(a)gmail.com > "pm" <pm(a)discussions.microsoft.com> wrote in message > news:6456AC58-6C30-4C40-82D4-D6CE802CD366(a)microsoft.com... > > 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 > > . > |