CODEWITHSUNDEEP
excelvba
David Mountain
David Mountain

Reputation: 9

Change Multiple Cells

I want to change multiple cells in a workbook but it only changes the first one: (context is I want to select a bunch of workbooks and change multiple cells like "A1" should be "Steve" and "A5" should be "Mike"

Sub ChangeCellValues()
        Dim sheet As Worksheet
        Dim total As Integer
        Dim intChoice As Integer
        Dim strPath As String
        Dim i As Integer
        'Dim wbNew As Workbook
        Dim wbSource As Workbook
        'Set wbNew = Workbooks.Add


        'allow the user to select multiple files
        Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = True
        'make the file dialog visible to the user
        intChoice = Application.FileDialog(msoFileDialogOpen).Show

        Application.ScreenUpdating = False
        Application.DisplayAlerts = False

        'determine what choice the user made
        If intChoice <> 0 Then
            'get the file path selected by the user
            For i = 1 To Application.FileDialog(msoFileDialogOpen).SelectedItems.Count
                strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(i)

                Set wbSource = Workbooks.Open(strPath)
                'Sheets("Jobcard").Select
                Sheets("Sheet1").Select
                Range("D10").Value = "8888"
                Range("D14").Value = "9999"


                wbSource.Close
            Next i
        End If

End Sub```

Upvotes: 0

Views: 78

Answers (2)

Abel Wong
Abel Wong

Reputation: 201

I tested your code, it seems to work. I added these lines at the end of the code otherwise when the workbook closes the changes will not be saved.

wbSource.Save
wbSource.Close

You can use wbSource.Close SaveChanges:=True too.

I made two workbooks, both updated with 8888 and 9999. Do all your workbooks contain "Jobcard"?

Upvotes: 1

David Mountain
David Mountain

Reputation: 9

OK I think I found the problem: Because Autosave was on it must be triggered to autosave on move to different cell or something. Because I was not manually saving it was saving the first change when moving to the second cell but not saving the second change.. There may be more to it than that but Able's wbSource.Save seems to have fixed it

Upvotes: 0

Related Questions