run a macro refering to many names in a row

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
3,194
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi Everyone,

I have this code to open one document and pull a bit a data from it,
I want it to run on lots of Documents,

so hears what I need have

I work from a workbook called "Head Office Reports New.xlsm" would prefer "Thisworkbook if possible"
I have a sheet called "Names" and a range of A2:A20

in each cell I have a name

A2 = Lon_Canary_Wharf
A3 = Lon_Baker_Street

etc.

now this name refers tio the name of a excel workbook and the folder its in so for

A2 = Workbook = "New Document Lon_Canary_Wharf.xlsm"

A3 = Workbook = "New Document Lon_Baker_Street.xlsm"

Workbook is always "New Document" & cell ref & ".xlsm"

folder is always S:\Lon_Canary_Wharf\New Documents Folder\New Document Lon_Canary_Wharf.xlsm



Now the code below runs just for one store,

Id like to adjust the macro so it runs for every store in the sheet "Names" Column A so I don't have to right the code over and over,

however I need it to do one more thing

Before it does this "Workbooks("Head Office Reports New.xlsm").Sheets("Lon_Canary_Wharf").Range("C4").Value
I would like it to check that the workbook has a sheet named "Lon_Canary_Wharf" and if not create one.

Code:
Sub Lon_Baker_Street()
   
Application.ScreenUpdating = False
    Workbooks.Open ("S:\Lon_Canary_Wharf\New Documents Folder\New Document Lon_Canary_Wharf.xlsm")


    Workbooks("Head Office Reports New.xlsm").Sheets("Lon_Canary_Wharf").Range("C3").Value = Workbooks("New Ops Dash Document Lon_Canary_Wharf.xlsm").Sheets("This years Data").Range("B3200").Value
    Workbooks("Head Office Reports New.xlsm").Sheets("Lon_Canary_Wharf").Range("C4").Value = Workbooks("New Ops Dash Document Lon_Canary_Wharf.xlsm").Sheets("This years Data").Range("A3200").Value
    
 
    Workbooks("New Document Lon_Canary_Wharf.xlsm").Close savechanges:=False
End Sub


please help if you can

Tony
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Hi Tony,
here some untested code to get you started:
Code:
Sub Lon_Baker_Street()
   
Application.ScreenUpdating = False
BaseDir = "S:\Lon_Canary_Wharf\New Documents Folder\"

Set Wb = ActiveWorkbook
Set Sht = Worksheets("Names")

For Each cl In Sht.Range("A2:A20").Cells
    If cl.Value <> "" Then
        'Cell is not empty
        FlNm = "New Document " & cl.Value & ".xlsm"
        If Dir(BaseDir & FlNm) <> "" Then
            'File exists
            
            Workbooks.Open (BaseDir & FlNm)
            
            Wb.Sheets(cl.Value).Range("C3").Value = Workbooks(FlNm).Sheets("This years Data").Range("B3200").Value
            Wb.Sheets(cl.Value).Range("C4").Value = Workbooks(FlNm).Sheets("This years Data").Range("A3200").Value
            
            Workbooks(FlNm).Close savechanges:=False
            
        End If
    End If

Next cl


Application.ScreenUpdating = True

End Sub
Some good sites to get deeper into VBA with loops etc: Excel VBA Programming - a free course for complete beginners and Free VBA Training Course
Cheers,
Koen
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top