Macro to copy data from a master workbook to other workbooks in the same folder

[Tmini]

Hi Guys
I have been trying to work out how to write a script to be able to copy a price from my main spreadsheet to all of my other spreadsheets in the same folder. Tee data I want to change will only be one specific cell and all of the spreadsheets will have the exact same data just the spreadsheets relate to different items. Rather than open up each individual spreadsheet I thought a macro would be perfect for doing this. I have been trying to do this and to find a solution but I can never seem to find something specific. I am trying to learn how to create my own macros but I am finding that rather difficult too. I know I need a loop command but I am not sure how to get it to open the next spreadsheet and then alter the required data. I have linked 3 spreadsheets, (https://www.dropbox.com/sh/8qbow8ixw1lxrud/CHG2K-sU2g ), as an example of what I am trying to do. For example I would like to change cell E4 from $12.00 to $22.00 in all the workbooks in the same folder. All formatting is exactly the same and there is only one worksheet in each file that I would like to change which I have named pricing. Eventually though I will be adding extra worksheets to the workbooks and giving them different labels but for now I am only using the one worksheet in each workbook. My macro enabled worksheet has a macro which I have copied from elsewhere but I cant seem to figure out what changes I need to do to get it to work. Any help on this would be greatly appreciated. I am still trying to learn VBA coding and I have a really long way to go
Thanks for any help it is greatly appreciated

 

[dmt32]

Hi welcome to the board.

Firstly, I think Application.Filesearch is not available 2007> so code posted not much use to you.

See if code below helps. Place both procedures in your master worksbook Pricing code page (right click tab > View Code)

Code should after each entry in data range (A4 to G lastrow) run UpdateFilesInFolder procedure which hopefully will update all workbooks in the folder. You can change the specified folder where shown in RED.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myrange As Range
    Dim Lastrow As Long
    Dim sFolder As String
    
    'folder where workbooks are stored
     'change as required
    sFolder = "C:\Users\Tristan\Dropbox\excel testing\"
   
    Lastrow = Me.Cells(Me.Rows.Count, "A").End(xlUp).Row + 1
    If Lastrow < 4 Then Lastrow = 4
    Application.ScreenUpdating = False
    Set myrange = Me.Range("A4:G" & Lastrow)
    If Not Intersect(Target, myrange) Is Nothing Then
        UpdateFilesInFolder Target, sFolder
    End If
    Application.ScreenUpdating = True
End Sub

Sub UpdateFilesInFolder(ByVal Target As Range, ByVal sFolder As String)
    Dim strFile As String
    
    strFile = Dir(sFolder & "*.xlsx", vbNormal)

    Do While strFile <> ""
        If strFile <> ThisWorkbook.Name Then
        Set wb = Workbooks.Open(sFolder & strFile, ReadOnly:=False)
        
        With wb
            .Worksheets("Pricing").Range(Target.Address).Value = Target.Value
            .Close True
        End With
        End If
        strFile = Dir
    Loop
End Sub

Dave

 

[Tmini]

Thanks for the help Dave. I have tried running it but it doesn't like this line of code -

Lastrow = Me.Cells(Me.Rows.Count, "A").End(xlUp).Row + 1

. It says Invalid use of Me keyword. Maybe I have done it wrong I have put the code in a module. Is this correct? I tried user form and class module but with no luck either.

 

[dmt32]

Thanks for the help Dave. I have tried running it but it doesn't like this line of code -

Lastrow = Me.Cells(Me.Rows.Count, "A").End(xlUp).Row + 1

. It says Invalid use of Me keyword. Maybe I have done it wrong I have put the code in a module. Is this correct? I tried user form and class module but with no luck either.

Place both procedures in your master workbook Pricing code page (right click tab > View Code)

Both places incorrect - Guidance where to place code was clearly given.

Dave

 

[Tmini]

H Dave
Thanks for that. I am such a Noob at all this and didn't realise you didn't need to code into a module. Thank you for your help. I have got a long way to go to learn how to use VBA