VBA to Open Prior Month File and Copy and Paste Info into Current Month

[pman37]

Hello,

I have a monthly file that we have. I have code that asks the user to open the prior month's file, but I'm not sure what to do after the file is open. The file name will change every month (name of month is in the file name) and each month there is a YTD, MTD and QTD. But essentially, I need to copy over a tab called "Cover" and just the first the rows from the prior month file to the current month.

Here is the code I have to open the file for the users:

Sub test_fileopen()Dim filename
MsgBox "Please open prior month FRP File"
filename = Application.GetOpenFilename
If filename <> False Then
Workbooks.Open (filename)
MsgBox "Continue"
End If
End Sub

So I know that:

    Rows("1:3").Select    Selection.Copy
    Windows("FRP Test.xlsm").Activate
        Rows("1:1").Select
    ActiveSheet.Paste

will sort of do it, but how do I make it dynamic no matter what the file name is for the prior month?

Thank you!!

 

[JLGWhiz]

You would need to use the constant part of file name and coerce the month part of it into by concatenating the current month minus 28 as a formatted date. Assume 'Report' is the constant part of the filename..

Sub t()
Dim dt As String, filename As String
dt = Format(Date - 28, "mmmm")
filename = "Report-" & dt & ".xlsx"
Workbooks.Open filename
End Sub

This would give you a file name of "Report-August.xlsx". If you have the month and year the your Format function would look like

Format(Date - 28, "mmmm, yyyy")

The 28 is used so that in March it would not jump back to January. (In case you were wondering)

 

[pman37]

The problem with that is that there is a different folder for each month, and then each year. Is there no way to just use the code I have to open a file and then copy and paste pieces of it since those would always be the same?

 

[JLGWhiz]

Maybe this will help you.

Sub test_fileopen()
Dim wb As Workbook, filename As String 'Declare variables
MsgBox "Please open prior month FRP File"
filename = Application.GetOpenFilename 'initialize file name variable
    If filename <> False Then
        Set wb = Workbooks.Open (filename) 'initialize workbook variable
        wb.Sheets("Cover").Rows(1:3).Copy Workbooks("FRP Test.xlsm").Sheets(1).Range("A1") 'copy and paste
    End If
End Sub

 

[pman37]

The second I run that, I get "Compile error: Syntax error" and it highlights the sub name.

Also, this section of code is in red when i put it in there:

        wb.Sheets("Cover").Rows(1:3).Copy Workbooks("FRP Test.xlsm").Sheets(1).Range("A1") 'copy and paste

 

[JLGWhiz]

Cleaned up a little. I didn't know you were going to attempt to run the code, I was just trying to show how to use the variables to do what you want. See if this will run. Make sure the sheet references are correct.

Sub test_fileopen2()
Dim wb As Workbook, filename As String 'Declare variables
MsgBox "Please open prior month FRP File"
filename = Application.GetOpenFilename 'initialize file name variable
    On Error GoTo Handl:
    If filename <> "False" Then
        Set wb = Workbooks.Open(filename)  'initialize workbook variable
        wb.Sheets("Cover").Rows("1:3").Copy Workbooks("FRP Test.xlsm").Sheets(1).Range("A1") 'copy and paste
    End If
Handl:
    If Err.Number > 0 Then
        MsgBox Err.Number & ":  " & Err.Description
        On Error GoTo 0
        Err.Clear
    End If
End Sub

 

[pman37]

Oh I'm sorry!

Thank you so much. When I run the code you provided there, I get: 9: Subscript out of range

 

[JLGWhiz]

Subscript out of range means it cannot find the object the code is telling it to look for. Be sure your sheet names are correct, exactly as shown on the sheet name tab, and spelling of any other object is correct. Since you did not specify which line of code the error occured on, I can't be of much more help.