Code for find then replace text within worksheets

[ipbr21054]

Evening All,

I have the code in use supplied below.
The workbook is callled ACCOUNTS.
The code is on various worksheets all within the ACCOUNTS workbook.
The code is always on COMMAND BUTTON 2

As you see in the code there is the part 2018 - 2019
When the time comes to change this i can see it will take some time to complete hence this post.

Is it possible to have a search & replace code where the code would look for 2018 - 2019 in this open workbook then replace it with 2019 - 2020

The following year i would then just change the dates again to suit.

This would only apply for the current open workbook as i also have past year workbook also called ACCOUNTS

Thanks very much.

Private Sub CommandButton2_Click()    Dim Answer As Long, wb As Workbook
    Answer = MsgBox("Transfer Values To Summary Sheet ?", vbYesNo + vbInformation, "End Of Month Accounts")
    If Answer = vbYes Then
        Set wb = Workbooks.Open(Filename:="C:\Users\Ian\Desktop\EBAY\ACCOUNTS\PREVIOUS ACCOUNTS\2018 - 2019\SUMMARY 2018 - 2019.xlsm")
        Workbooks("ACCOUNTS").Sheets("MILEAGE").Range("C32").Copy
        wb.Sheets("Sheet1").Range("I35").PasteSpecial xlPasteValues


        wb.Close True


        End If
        Workbooks("ACCOUNTS").Sheets("MILEAGE").Range("A3").Select
        Application.CutCopyMode = False
        MsgBox "Summary Transfer Completed", vbInformation, "SUCCESSFUL MESSAGE"
        ActiveWorkbook.Save
End Sub

 

[offthelip]

it is possible to write code that will modify a vba project, but it is not totally straight forward. I personally don't think that is s good solution to your problem. The way i would solve it is to declare public constant in one of your modules with a string which consists of the string which changes . e.g "2018 - 2019", then change your code so that you use this constant surrounded by double quotes and ampersands everywhere that you current have "2018 - 2019" This may be slightly more work this year but is really easy thereafter. You can at least do it with a global replace command

 

[ipbr21054]

Sorry but i cant follow that so i will need to do it the long way then thanks.

 

[offthelip]

I hope this explains it better:
in a standard module ( not the command button code) put this:

Public Const yeartxt = "2018 - 2019"

Sub test()
end sub

Note I have put the sub test just to show you that it goes above any other subroutines

the in your command button code replace the lien which set the pathto the workbook with this:

Set wb = Workbooks.Open(Filename:="C:\Users\Ian\Desktop\EBAY\ACCOUNTS\PREVIOUS ACCOUNTS\" & yeartxt & "\SUMMARY " & yeartxt & ".xlsm")

The all you have to do is change the text in yeartxt once a year

Dim wb As Workbook
Set wb = Workbooks.Open(Filename:="C:\Users\Ian\Desktop\EBAY\ACCOUNTS\PREVIOUS ACCOUNTS" & yeartxt & "\SUMMARY " & yeartxt & ".xlsm")

 

[ipbr21054]

Thanks,
Thats a lot easier for me to understand.

have a nice day