0

I am using vba in a CAD program to export data, sort the data, and add data. The following macro is exactly what I want excel to do. However I believe I am limited to having the CAD program tell Excel what to do through VBA. This macro copyies a formula and pastes it to all the populated cells below it in the column.

MACRO CODE:

Range("B1").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste

iLogic Version of Code:

oBook.WorkSheets(1).Name = "Order List"
oBook.WorkSheets(2).Name = "Cut List"
wSheet1 = oBook.WorkSheets("Order List")
wSheet2 = oBook.WorkSheets("Cut List")

wSheet2.Activate
wSheet2.Range("B1").Select
wSheet2.Selection.Copy
wSheet2.Range(Selection, Selection.End(xlDown)).Select
wSheet2.Selection.Paste

Unfortunately I seem to be missing something to translate between Inventor and Excel, but I don't know enough to even know if that's the issue. Any advice is very much appreciated as I am still very new to VBA.

DisGruntledDraftsman
  • 1
  • 1
  • See [this post](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). You want to avoid `Select` and `Activate`. Also `Selection` probably means nothing to Inventor, and if it does then it doesn't mean the same thing as `Selection` does when hosted in Excel. You'll want to make that `wSheet2.Application.Selection`, or if you have a reference to `Excel.Application` (you should), then use that object variable to qualify everything that would be global in Excel. – Mathieu Guindon Nov 08 '19 at 19:27

2 Answers2

0

Ok. I had some code that I had copied off of a forum but didn't understand the functions going on. I believe this is what you are referring to "Application"?

wSheet2.Columns("G:G").select()
oExcelApp.Selection.cut()
wSheet2.Columns("B:B").Select()
oExcelApp.Selection.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlToRight)

Although I don't understand what the "(Microsoft.Office.Interop.Excel.XlDirection.xlToRight)" refers to. I understand the part about the direction and where it inserts just not whats before it.

DisGruntledDraftsman
  • 1
  • 1
-1

I do agree with @DisGruntledDraftsman about not using Select and Activate. However, if you are just trying to get some working code that gets the job done, some simple tweaks can be done. Of course this isn't ideal but, it should work though. 

row_Count = wSheet2.Range("B1048576").End(xlUp).Row
wSheet2.Activate
wSheet2.Range("B1").Select
Selection.AutoFill Destination:=wSheet2.Range("B1:B" & row_Count), Type:=xlFillDefault
Blake Daniel
  • 100
  • 7
  • `wSheet2.Range("B1").AutoFill ...` would "get the job done" just as well, without `Select` and `Activate`. There's hardly ever any reason whatsoever to work off the `Selection`.. which as my comment said above, means nothing to OP's host application. If a `Selection` exists in OP's host app, it does not refer to Excel's `Selection`. This code will not work. – Mathieu Guindon Nov 08 '19 at 20:53