CODEWITHSUNDEEP
vbaexcel
Louie Lozano
Louie Lozano

Reputation: 1

VBA on new Excel file

I receive reports almost daily in spreadsheet form. I have a macro code that will take out certain portions of the spreadsheet and put it into a new spreadsheet. I want to know if there's a way to run that macro without having to manually copy paste it into each new file I receive.

Sub CopyItOver()
  Dim newbook As Workbook
  Set newbook = Workbooks.Add
  ThisWorkbook.Worksheets("sheet1").Range("fe1:fh1").Copy Destination:=newbook.Worksheets("Sheet1").Range("A1:D1")
  ThisWorkbook.Worksheets("sheet1").Range("IZ1:JI1").Copy Destination:=newbook.Worksheets("Sheet1").Range("E1")
  ThisWorkbook.Worksheets("sheet1").Range("JK1:JL1").Copy Destination:=newbook.Worksheets("Sheet1").Range("O1")
  ThisWorkbook.Worksheets("sheet1").Range("KA1:KJ1, Kl1, KR1, KT1").Copy Destination:=newbook.Worksheets("Sheet1").Range("Q1")
  ThisWorkbook.Worksheets("sheet1").Range("fe328:fh328").Copy Destination:=newbook.Worksheets("Sheet1").Range("A2")
  ThisWorkbook.Worksheets("sheet1").Range("IZ328:JI711").Copy Destination:=newbook.Worksheets("Sheet1").Range("E2")
  ThisWorkbook.Worksheets("sheet1").Range("JK328:JL711").Copy Destination:=newbook.Worksheets("Sheet1").Range("O2")
  ThisWorkbook.Worksheets("sheet1").Range("KA328:KJ711, KL328:KL711, KR328:KR711, KT328:KT711").Copy Destination:=newbook.Worksheets("Sheet1").Range("Q2")
  Columns("E").ColumnWidth = 15
  Columns("Q").ColumnWidth = 15
End Sub

Upvotes: 0

Views: 106

Answers (3)

hughg
hughg

Reputation: 201

Like schwack said, you can put the macro in your personal macro workbook and it will always be available, or put it in a workbook that you have open alongside the report workbooks you are receiving. But you will need to change the macro code so it doesn't use the 'ThisWorkbook' object. You could simply use 'ActiveWorkbook' instead.

Upvotes: 0

schwack
schwack

Reputation: 88

When you create a macro and select the option to save to your personal macro workbook it is available every time you start Excel. Or create a custom toolbar button and attach the macro to it, it will always be there...

Upvotes: 1

ManishChristian
ManishChristian

Reputation: 3784

You can create small .vbs file which you can run (by double click) and it will run your macro automatically on your spreadsheet.

First of all export your working macro to some location. And then copy paste below lines in a text file and save it as .vbs file. 

Dim fso, xlApp, xlBook

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False

Set xlBook = xlApp.Workbooks.Open(fileToUse)
xlApp.VBE.ActiveVBProject.VBComponents.Import "Path to your .bas file"
xlApp.Run "Name of your Sub"
xlBook.Save
xlBook.Close
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing
Set fso = Nothing

Just change the path in the file.

Upvotes: 0

Related Questions