Auto Run a macro in Excel

Question

Hi&nbsp;&nbsp;Can anybody help me, I have a macro that I run from my personal macro workbook that opens up about 10 different spreadsheets copies data and pastes it into my macro enabled workbook, works fine but I want to automate it.&nbsp;I have created a new macro enabled worksheet setup a autorun macro and copied the code from my old macro below it, the issue I seem to be having is it does not seem to select my main worksheet (once its has opened it) as the active one so errors when the code tries paste copied data from another workbook into it.&nbsp;I have inserted a snipped of the code below and made the text bold where it is bombing out,&nbsp;&nbsp;Private Sub Workbook_Open()&nbsp;' Macro1 Macro'&nbsp;&nbsp; Application.DisplayAlerts = False&nbsp;&nbsp;&nbsp; Workbooks.Open Filename:="U:\MonthEnd\Exeter\customer_balances_credit.xlsx"&nbsp;&nbsp;&nbsp; Range("A1").Select&nbsp;&nbsp;&nbsp; Range(Selection, Selection.End(xlToRight)).Select&nbsp;&nbsp;&nbsp; Range(Selection, Selection.End(xlDown)).Select&nbsp;&nbsp;&nbsp; Selection.Copy&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ChDir "C:\MonthEnd\Exeter"&nbsp;&nbsp;&nbsp; Workbooks.Open Filename:="C:\MonthEnd\Exeter\Day_End1.xlsx"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ActiveWindow.ScrollWorkbookTabs Sheets:=1&nbsp;&nbsp;&nbsp; ActiveWindow.ScrollWorkbookTabs Sheets:=1&nbsp;&nbsp;&nbsp; ActiveWindow.ScrollWorkbookTabs Sheets:=1&nbsp;&nbsp;&nbsp; ActiveWindow.ScrollWorkbookTabs Sheets:=1&nbsp;&nbsp;&nbsp; Sheets("Customer Credit").Select&nbsp;&nbsp;&nbsp; Range("A2").Select&nbsp;&nbsp;&nbsp; ActiveSheet.Paste&nbsp;&nbsp;&nbsp; Windows("customer_balances_credit.xlsx").Activate&nbsp;&nbsp;&nbsp; ActiveWindow.Close&nbsp;&nbsp;&nbsp; Workbooks.Open Filename:="U:\MonthEnd\Exeter\customer_balances_debit.xlsx"&nbsp;&nbsp;&nbsp; Range("A1").Select&nbsp;&nbsp;&nbsp; Range(Selection, Selection.End(xlToRight)).Select&nbsp;&nbsp;&nbsp; Range(Selection, Selection.End(xlDown)).Select

jkpieterse · Answer

eposlink There is no need to select things in VBA. This bit:
Sheets("Customer Credit").Select
    Range("A2").Select
    ActiveSheet.Paste

can be replaced with:
ThisWorkbook.Sheets("Customer Credit").Range("A2").Paste
ThisWorkbook is a predefined variable which means "The workbook which contains the code that currently runs"

Forum Discussion

Auto Run a macro in Excel

1 Reply