Home

Auto Run a macro in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-771197%22%20slang%3D%22en-US%22%3EAuto%20Run%20a%20macro%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-771197%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BCan%20anybody%20help%20me%2C%20I%20have%20a%20macro%20that%20I%20run%20from%20my%20personal%20macro%20workbook%20that%20opens%20up%20about%2010%20different%20spreadsheets%20copies%20data%20and%20pastes%20it%20into%20my%20macro%20enabled%20workbook%2C%20works%20fine%20but%20I%20want%20to%20automate%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20created%20a%20new%20macro%20enabled%20worksheet%20setup%20a%20autorun%20macro%20and%20copied%20the%20code%20from%20my%20old%20macro%20below%20it%2C%20the%20issue%20I%20seem%20to%20be%20having%20is%20it%20does%20not%20seem%20to%20select%20my%20main%20worksheet%20(once%20its%20has%20opened%20it)%20as%20the%20active%20one%20so%20errors%20when%20the%20code%20tries%20paste%20copied%20data%20from%20another%20workbook%20into%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20inserted%20a%20snipped%20of%20the%20code%20below%20and%20made%20the%20text%20bold%20where%20it%20is%20bombing%20out%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CFONT%3EPrivate%20Sub%20Workbook_Open()%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CFONT%3E'%20Macro1%20Macro%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%3E'%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%20Application.DisplayAlerts%20%3D%20False%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Workbooks.Open%20Filename%3A%3D%22U%3A%5CMonthEnd%5CExeter%5Ccustomer_balances_credit.xlsx%22%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Range(%22A1%22).Select%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Range(Selection%2C%20Selection.End(xlToRight)).Select%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Range(Selection%2C%20Selection.End(xlDown)).Select%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Selection.Copy%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ChDir%20%22C%3A%5CMonthEnd%5CExeter%22%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Workbooks.Open%20Filename%3A%3D%22C%3A%5CMonthEnd%5CExeter%5CDay_End1.xlsx%22%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ActiveWindow.ScrollWorkbookTabs%20Sheets%3A%3D1%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ActiveWindow.ScrollWorkbookTabs%20Sheets%3A%3D1%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ActiveWindow.ScrollWorkbookTabs%20Sheets%3A%3D1%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ActiveWindow.ScrollWorkbookTabs%20Sheets%3A%3D1%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3CEM%3E%3CSTRONG%3ESheets(%22Customer%20Credit%22).Select%3C%2FSTRONG%3E%3C%2FEM%3E%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Range(%22A2%22).Select%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ActiveSheet.Paste%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Windows(%22customer_balances_credit.xlsx%22).Activate%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ActiveWindow.Close%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Workbooks.Open%20Filename%3A%3D%22U%3A%5CMonthEnd%5CExeter%5Ccustomer_balances_debit.xlsx%22%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Range(%22A1%22).Select%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Range(Selection%2C%20Selection.End(xlToRight)).Select%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Range(Selection%2C%20Selection.End(xlDown)).Select%3C%2FFONT%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-771197%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-771636%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20Run%20a%20macro%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-771636%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F381306%22%20target%3D%22_blank%22%3E%40eposlink%3C%2FA%3EThere%20is%20no%20need%20to%20select%20things%20in%20VBA.%20This%20bit%3A%3C%2FP%3E%0A%3CPRE%3ESheets(%22Customer%20Credit%22).Select%0A%20%20%20%20Range(%22A2%22).Select%0A%20%20%20%20ActiveSheet.Paste%0A%3C%2FPRE%3E%0A%3CP%3Ecan%20be%20replaced%20with%3A%3C%2FP%3E%0A%3CPRE%3EThisWorkbook.Sheets(%22Customer%20Credit%22).Range(%22A2%22).Paste%3C%2FPRE%3E%0A%3CP%3EThisWorkbook%20is%20a%20predefined%20variable%20which%20means%20%22The%20workbook%20which%20contains%20the%20code%20that%20currently%20runs%22%3C%2FP%3E%3C%2FLINGO-BODY%3E
eposlink
Occasional Visitor

Hi 

 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.

 

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.

 

I have inserted a snipped of the code below and made the text bold where it is bombing out, 

 

Private Sub Workbook_Open()
 
' Macro1 Macro
'
   Application.DisplayAlerts = False
    Workbooks.Open Filename:="U:\MonthEnd\Exeter\customer_balances_credit.xlsx"
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
        ChDir "C:\MonthEnd\Exeter"
    Workbooks.Open Filename:="C:\MonthEnd\Exeter\Day_End1.xlsx"
   
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    Sheets("Customer Credit").Select
    Range("A2").Select
    ActiveSheet.Paste
    Windows("customer_balances_credit.xlsx").Activate
    ActiveWindow.Close
    Workbooks.Open Filename:="U:\MonthEnd\Exeter\customer_balances_debit.xlsx"
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
1 Reply

@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"