0

I'm having a heck of a time with this... I've written a few small macros that all get tied together and run via button in the user's sheet. My IT group just upgraded me from 2010 to 2016, and I mention that because a lot of what I've been seeing online has to do with that... but none of the solutions are working. This is NOT a protected workbook either. All this sheet does is take table data from JDA Space Planning, user dumps the raw data to Position and CutDown. Cutdown1 does compiles data from both and gets filtered to where one column = 1. Then pasted as values over to JDA. It's saved out as CSV, where JDA will pick it back up and execute automation scripts against it. These macros aren't nearly as complicated as I've written before in other Workbooks, but this 400 error is stopping me dead in my tracks. Here is my code, thank you all in advance:

Sub Set_Formats()
Sheets("CutDown").Activate
Worksheets("CutDown").ShowAllData
Sheets("Position").Activate
Range("A:H").NumberFormat = "@"
Sheets("CutDown").Activate
Range("A:H").NumberFormat = "@"
Sheets("CutDown1").Activate
Range("A:H").NumberFormat = "@"
End Sub
Sub Filter_To_Cutdowns()
Sheets("Cutdown").Activate
ActiveSheet.Range("$G:$G").AutoFilter Field:=8, Criteria1:=1, _
    Operator:=xlAnd
End Sub

Sub Copy_Over_Cutdowns()
Sheets("CutDown1").Activate
ActiveSheet.UsedRange.Select
Selection.ClearContents

Sheets("CutDown").Activate
ActiveSheet.UsedRange.Copy
Sheets("CutDown1").Activate
Range("A1").PasteSpecial xlPasteValues
End Sub

Sub Trim_Columns()
Sheets("JDA").Activate
ActiveSheet.UsedRange.Select
Selection.ClearContents
Sheets("CutDown1").Activate
ActiveSheet.UsedRange.Select
ActiveSheet.UsedRange.Copy
Sheets("JDA").Activate
Range("A1").PasteSpecial xlPasteValues

Range("A:A,C:C,E:E,F:F,G:G,H:H").Delete
End Sub

Sub Export_CSV()
Application.DisplayAlerts = False
Sheets("JDA").Activate
ActiveWorkbook.SaveAs "C:\Temp\JDA\CutDowns.csv", FileFormat:=xlCSV,    CreateBackup:=True
Application.DisplayAlerts = True
End Sub

Sub Run_Macros()
Application.Run "Set_Formats"
Application.Run "Filter_To_Cutdowns"
Application.Run "Copy_Over_CutDowns"
Application.Run "Trim_Columns"
Application.Run "Export_CSV"
Workbooks("CutDOwn1.xlsm").Close
End Sub
Kevin G
  • 13
  • 5
  • One thing to note... when I run all of these straight out of the VBA console, no errors at all. When I Click the assigned button to Sub Run_MAcros, I get the error. – Kevin G May 25 '17 at 14:48
  • Refactor the code to [avoid select and activate, etc.](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) and assign parentage and I 99% gaurantee the code will work. For example `Sheets("Positions").Range("A:H").NumberFormat = "@"` or `Sheets("CutDown").UsedRange.Value = Sheets("CutDown").UsedRange.Value` (instead of copy and paste values) – Scott Holtzman May 25 '17 at 14:49
  • Scott, thanks for the quick reply, do you have a small example of what you mean by that? I don't know that I can think of any other way to toggle in between sheets as well as define ranges to take action against... – Kevin G May 25 '17 at 14:51
  • Read the information in the link for examples. Or see my edits. – Scott Holtzman May 25 '17 at 14:52
  • Sorry, didn't realize you linked an article... let me check it out and re-write the code, more to come.. – Kevin G May 25 '17 at 14:52
  • @KevinG - Go read the **Best Practices** section of [Excel VBA - Documentation](https://stackoverflow.com/documentation/excel-vba/topics) and [How to avoid using Select in Excel VBA macros](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). –  May 25 '17 at 14:55
  • Thank you both, I've taken a look at the documentation that Scott provided, and after such deleted all activate and select statements. Here is an example of the code: Sub Trim_Columns() Sheets("JDA").UsedRange.ClearContents Sheets("CutDown1").UsedRange.Copy Sheets("JDA").Range("A1").PasteSpecial xlPasteValues Sheets("JDA").Range("A:A,C:C,E:E,F:F,G:G,H:H").Delete End Sub – Kevin G May 25 '17 at 15:09
  • Sorry again, still a n00b to this site and can't remember how to post as code.... All of my code is working again when I'm running it from the VBA editor, but when I click on the button, I still get error 400. When I run the Application.Run script, I get Run-Time Error '1004': Application-defined or object-defined error. I'm looking that error up right now – Kevin G May 25 '17 at 15:11

0 Answers0