VBA_Help

HI Excel Expert,

I am seeking your help

#1 I have a master_report file, where I keep on appending the selected rows from time to time, not montly but whenever there is a requirement. Now I am facing some issue. I will need excel experts help to help me out with this issues.

I am attaching the master_report file there in Module 1, I have codes to append either the full data by overwriting or to select only few rows and append after the last row. Now until n unless I put the code in sheet 1 it does not work, so how should I modify the codes so that the codes in Module 1 work for sheet 1, also the code in Module 1 works when I type the Month name in ##w2 cell and it find the file in the same folder, here after appeding the file the Month name doesn't disapper, what should I do to make it disapper once the data has been copied or appened.

#2 In module 2 in the same master_report file, I have codes to do saveas the file , but those are not working properly. Basically I want to do a saveas by changing the file extension to csv and saving it in destination folder and save only from Column A to Column U till the last rows(Not the Information Box which I have put in column X) and link the SaveAS codes to ActiveX control(Aslo this Active X should not be save into csv after doing SaveAs).

Data in the tables is only for testing purpose doesnot resemble anything/anyone.

Take the worksheet _change event out of the module and paste it into the worksheet module - this will then work after putting in an entry into 'W2'

I tried doing that it is still not working. Attaching the file again to with the changes I made as suggested by you.

## Code in Sheet 1

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
End Sub

## Code in Module 1

Sub LinkedToSheet1()
    Dim srcWB As Workbook, desWB As Workbook, desWS As Worksheet, lRow As Long, sPath As String, copyRng As Range
    sPath = ThisWorkbook.Path & "\This_" & "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

Can someone please help me on this,will be really grateful to you. I just need code correction rest everything is working

For #1.
I have done changes in codes.
For #2
Code is available in module 2. I have put button in "New" sheet. Make suitable changes regarding path and file name. Assign macro " cmdSave" to the button.
The creates a new blank file and copied data from current sheet and paste it new file, saves as .csv file.

WhatsApp Image 2024-06-17 at 19.22.54_a8cb6de1.jpg

Hi I have enabled macros in the above file but still getting the issue. Please help as I face this issue quite a few time

Sub cmdSave()
    Dim sFileName As String
    Dim WB As Workbook
    Dim LastRow As Long
    
    Application.DisplayAlerts = False

    sFileName = "MyFileName.csv"
    LastRow = ws.UsedRange.Rows.Count
    'Copy the contents of required sheet ready to paste into the new CSV
    Sheets(1).Range("A1:U" & LastRow).CurrentRegion.Copy 'Define your own range if required

    'Open a new XLS workbook, save it as the file name
    Set WB = Workbooks.Add
    With WB
        .Title = "MyTitle"
        .Subject = "MySubject"
        .Sheets(1).Select
        ActiveSheet.Paste
        .Saveas "D:\Downloads\Excel" & sFileName, xlCSV
        .Close
    End With

    Application.DisplayAlerts = True
End Sub

For SaveAs query which is attached in the Module 2, I tried changing the range but it is showing me debug. Please help

Do not change the line I have given. It selects all data from column A to U. Last row not required.

Sheets(1).Range("A1").CurrentRegion.Copy 'Define your own range if required

I am not having problem.
Change this line

.Saveas "D:\Downloads\Excel" & sFileName, xlCSV

as

.Saveas "D:\Downloads\Excel\" & sFileName, xlCSV

Originally Posted by Nyay

Can someone please help me on this,will be really grateful to you. I just need code correction rest everything is working

Originally Posted by Nyay

Can someone please help me with this.

Administrative Note:

Welcome to the forum.

Unfortunately your post does not comply with Guideline #7 of our Forum RULES:

Do not bump a thread until 24 hours after it is posted.

It may take time for someone who can help to look at your thread.

Thanks it is sorted. Thanks for your help. Just one more help

sometime I don't get the option for adding the module/code in the button what to do in that case.

WhatsApp Image 2024-06-17 at 21.25.10_7731b59c.jpg

Do not use active X control buttons for assigning macro. Use only form control buttons.

Originally Posted by kvsrinivasamurthy

Do not use active X control buttons for assigning macro. Use only form control buttons.

I have been using ActiveX buttons for about 20 years without issues. Why are you giving this rather absolute advice?