VBA-Automation

Deleted the post.

Administrative Note:

We would very much like to help you with your query, however your post does not comply with our guidelines

Please take a moment to review how to add code tags. Posting code between [code] [/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Guideline #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here.

I have added the code tags for you today. Please take a few minutes to re-read our guidelines and rules that you agreed to when you joined and comply in the future.

Change to

' Get the current month name
currentMonth = Format(Date, "mmmm")

Deleted the post.

Place this macro in a regular module in your Master file. Re-save the file as macro-enabled workbook in the same folder as your source file. Run the macro.

Sub AppendMonthlyReportData()
    Application.ScreenUpdating = False
    Dim srcWB As Workbook, desWB As Workbook, desWS As Worksheet, lRow As Long, sPath As String
    sPath = ThisWorkbook.Path & "\Report_" & MonthName(Month(Date)) & ".xlsm"
    If Len(Dir(sPath)) = 0 Then
        MsgBox ("Source file " & sPath & " not found.")
        Exit Sub
    End If
    Set desWB = ThisWorkbook
    Set desWS = desWB.Sheets(1)
    desWS.UsedRange.Offset(1).ClearContents
    Set srcWB = Workbooks.Open(sPath)
    With Sheets(1)
        lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        If lRow > 1 Then
            .Range("A2:A" & lRow).Copy desWS.Range("A2")
            MsgBox ("Data appended from " & sPath & " to " & desWB.Name)
        Else
            MsgBox ("No data found in " & sPath)
        End If
    End With
    srcWB.Close False
    Application.ScreenUpdating = True
End Sub

Deleted the post.

Deleted the post.

You are very welcome.

Dir(filePath) <> "" Then

I don't see this in the code I posted in Post #5.

Deleted the post.

Have you tried the macro I suggested in Post #5?

It would be easier to help if you attach your file. See the yellow banner at the top of this page for instructions on how to attach a file.

Deleted the post.

Deleted the post.

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your "New" sheet in the Master and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a month in cell W2 and press the ENTER key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("W2")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim srcWB As Workbook, desWB As Workbook, desWS As Worksheet, lRow As Long, sPath As String
    sPath = ThisWorkbook.Path & "\Report_" & Target.Value & ".xlsx"
    If Len(Dir(sPath)) = 0 Then
        MsgBox ("Source file " & sPath & " not found.")
        Exit Sub
    End If
    Set desWB = ThisWorkbook
    Set desWS = desWB.Sheets(1)
    desWS.UsedRange.Offset(1).ClearContents
    Set srcWB = Workbooks.Open(sPath)
    With Sheets(1)
        lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        If lRow > 1 Then
            .UsedRange.Offset(1).Copy desWS.Range("A2")
            MsgBox ("Data appended from " & sPath & " to " & desWB.Name)
        Else
            MsgBox ("No data found in " & sPath)
        End If
    End With
    srcWB.Close False
    Application.ScreenUpdating = True
End Sub

Deleted the post.

This version of the macro will ask you if you want to copy all the data or just a selected range. Click 'Yes' or 'No' accordingly.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("W2")) Is Nothing Then Exit Sub
    Dim srcWB As Workbook, desWB As Workbook, desWS As Worksheet, lRow As Long, sPath As String, copyRng As Range
    sPath = ThisWorkbook.Path & "\Report_" & Target.Value & ".xlsx"
    If Len(Dir(sPath)) = 0 Then
        MsgBox ("Source file " & sPath & " not found.")
        Exit Sub
    End If
    Set desWB = ThisWorkbook
    Set desWS = desWB.Sheets(1)
    If MsgBox("Do you want to copy all the data from the source workbook or just a selected range?  Click 'Yes' for all data or 'No' to select a range.", vbYesNo) = vbYes Then
        Application.ScreenUpdating = False
        desWS.UsedRange.Offset(1).ClearContents
        Set srcWB = Workbooks.Open(sPath)
        With Sheets(1)
            lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            If lRow > 1 Then
                .UsedRange.Offset(1).Copy desWS.Range("A2")
                MsgBox ("Data appended from " & sPath & " to " & desWB.Name)
            Else
                MsgBox ("No data found in " & sPath)
            End If
        End With
        srcWB.Close False
        Application.ScreenUpdating = True
    Else
        Application.ScreenUpdating = False
        Set srcWB = Workbooks.Open(sPath)
        Application.ScreenUpdating = True
        Set copyRng = Application.InputBox(Prompt:="Select a range to copy.", Title:="Range Selection", Type:=8)
        Application.ScreenUpdating = False
        copyRng.Copy
        desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial
        srcWB.Close False
        Application.ScreenUpdating = True
    End If
End Sub

As far as VBA is concerned, it is an extremely powerful tool that can be used to automate tasks. I found that the best way to learn VBA on your own is to use on-line tutorials, searching the web for specific areas of interest and following forums such as this one. The volunteers on sites such as this one are invaluable at providing help. I would suggest you keep a file of codes that you find useful and in this way you can build up a library that you can refer to. A lot is also trail and error. The very first post in this Forum (highlighted in blue) is a good place to start. The following links may be of help:

http://www.snb-vba.eu/index_en.html
https://excelmacromastery.com/vba-dictionary/
https://excelmacromastery.com/excel-vba-array/
https://www.excelforum.com/excel-pro...materials.html

The more you practise, the better your get at it. Good luck with it!!!!

Deleted the post.

Replace this line of code

desWS.UsedRange.Offset(1).ClearContents

with this line

desWS.Range("A2", desWS.Range("U" & Rows.Count).End(xlUp)).ClearContents

Deleted the post.

Deleted the post.

Deleted.......................

Deleted the post.

@lakshaay
do not delete parts of threads or the original post. This is a data base that is intended to help you and any others having similar issues. If you continue to use this approach to your posts, you will find yourself with a time out and possible banishment from this forum. Follow the rules you agreed to when you joined and do not modify your posts by deleting sections of them. This thread is closed to future additions.