run a macro refering to many names in a row

[tonywatsonhelp]

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.

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

 

[Rijnsent]

Hi Tony,
here some untested code to get you started:

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

 

[tonywatsonhelp]

Thanks Rijnsent,
this is great

Thank you for your help

Tony