Excel VBA file name changes

Question

I have to 2 Excel workbooks to work with: Book1october & Book2. Book1october18 is an import file, meaning that it changes monthly, along with the name (next month it will be Book1november18). I have to copy some data from Book1october to Book2 automatically through VBA code.

This is the code that I've written:

Windows("Book1october18").Activate
Sheets("Sheet1").Activate
Range("B2:AQ5").Select
Selection.Copy
Windows("Book2").Activate
Sheets("Sheet1").Activate
Range("R2:BG5").Select
ActiveSheet.Paste

My problem is that I don't know how to write the code in order to make the actions that I want whenever the month's name changes and also the year. (I have to make it for all the months and 2019)

[How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Pᴇʜ, Oct 29 '18 at 14:08
You could also have a dialog box asking the user to select the file they want to work on. (`Application.FileDialog(msoFileDialogFilePicker)`) — cybernetic.nomad, Oct 29 '18 at 14:14

score 4 · Answer 1 · answered Oct 29 '18 at 14:09

4

You can automatically update your workbook name using the Date() function and Format()

Dim sWbName As String
sWbName = "Book1" & LCase(Format(Date, "mmmmyy"))

Debug.Print sWbName
'Prints Book1october18

answered Oct 29 '18 at 14:09

K.Dᴀᴠɪs

9,945
11
33
43

score 3 · Answer 2 · answered Oct 29 '18 at 14:48

The name/path of the workbook doesn't need to matter. Use K.Davis's code to come up with a filename, or prompt the user for a path/file to open - get that string into some sourceBookPath variable, then have the macro open the workbook. Now you can hold a reference to that Workbook object:

Dim sourceBook As Workbook
Set sourceBook = Application.Workbooks.Open(sourceBookPath)

Now, the worksheet.

Dim sourceSheet As Worksheet

If the sheet is always going to be named "Sheet1", then you can do this:

Set sourceSheet = sourceBook.Worksheets("Sheet1")

Or, if the sheet is always going to be the first sheet in the book (regardless of its name), you can do this:

Set sourceSheet = sourceBook.Worksheets(1)

Once you have a Worksheet object, you can get the Range you need - but first you need your target. Again if "book2" is opened by the macro, things are much simpler:

Dim targetBook As Workbook
Set targetBook = Application.Workbooks.Open(targetBookPath)

Or is it created by the macro?

Set targetBook = Application.Workbooks.Add

Anyway, we want the first sheet:

Dim targetSheet As Worksheet
Set targetSheet = targetBook.Worksheets(1)

And now we can copy from the source, and paste to the target:

sourceSheet.Range("B2:AQ5").Copy targetSheet.Range("R2:BG5")

And not once did we ever need to .Select or .Activate anything, and we never needed to care for any Window.

Nice visual on the avoidance of `.Select` – K.Dᴀᴠɪs Oct 29 '18 at 14:52 — K.Dᴀᴠɪs, Oct 29 '18 at 14:52

score 0 · Answer 3 · answered Oct 29 '18 at 14:10

0

Replace:

Windows("Book1october18").Activate

with:

s = LCase(Format(Now, "mmmm")) & Right(Year(Now), 2)
Windows(s).Activate

answered Oct 29 '18 at 14:10

Gary's Student

95,722
10
59
99

Dy.Lee · Answer 4 · 2018-10-29T15:11:38.037

Try this.

This is a recognition of the next month's document, assuming you have opened two documents.

Sub test()
    Dim Wb1 As Workbook, wb2 As Workbook
    Dim Wb As Workbook

    For Each Wb In Workbooks
        If InStr(Wb.Name, "Book1") Then
            Set Wb1 = Wb
        ElseIf InStr(Wb.Name, "Book2") Then
            Set wb2 = Wb
        End If
    Next Wb

    Wb1.Sheets("Sheet1").Range("B2:AQ5").Copy wb2.Sheets("Sheet1").Range("r2")
End Sub

Excel VBA file name changes

4 Answers4

Linked