Macro to "Save As" to User's Desktop

corcelle · May 31st 2016

Hello,
I recorded the below macro to hide certain information in a workbook, then create a copy of that view and save it. When I recorded it, I saved it to my desktop; what I want it to do is save to the desktop of the active user.

Is there a way to change to code below (Starting with the ActiveWorkbook.SaveAs) command to make this work for any computer?

Thank you!
Corcelle

Code

Sub ExportMFTasks()
'
' ExportMFTasks Macro
'
'
BeginRow = 36
    EndRow = 400
    ChkCol = 3
    
    Application.ScreenUpdating = False
    
    Cells.Select
    Selection.EntireRow.Hidden = False
    Range("A1").Select
    For RowCnt = BeginRow To EndRow
         If Cells(RowCnt, ChkCol).Value = "COE" Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
         If Cells(RowCnt, ChkCol).Value = "COE Strategy" Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
        If Cells(RowCnt, ChkCol).Value = "COE Delivery" Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
        If Cells(RowCnt, ChkCol).Value = "PCM" Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
        If Cells(RowCnt, ChkCol).Value = "RIO" Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
        If Cells(RowCnt, ChkCol).Value = "Quality Manager" Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
        If Cells(RowCnt, ChkCol).Value = "Vendor/COE" Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
        If Cells(RowCnt, ChkCol).Value = "All" Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
         If Cells(RowCnt, ChkCol).Value = "" Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
    Next RowCnt
    
    Application.ScreenUpdating = True
    Sheets("CAMPAIGN").Select
    Sheets("CAMPAIGN").Copy
    ChDir "C:\Users\c014933\Desktop"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\c014933\Desktop\MF Task Export.xlsx", FileFormat:=xlOpenXMLWorkbook _
        , CreateBackup:=False
    Windows("EMOB CHECKLIST TEMPLATE_May Update.xlsm").Activate
    
    Cells.Select
    Selection.EntireRow.Hidden = False
    Range("A1").Select
End Sub

Display More

**S O** · May 31st 2016

Re: Macro to "Save As" to User's Desktop

This should do it (Assuming it's on Windows)

Code

ActiveWorkbook.SaveAs Filename:= _ 
    Environ$("USERPROFILE") & "\Desktop\MF Task Export.xlsx", FileFormat:=xlOpenXMLWorkbook _ 
    , CreateBackup:=False

corcelle · Jun 1st 2016

Re: Macro to "Save As" to User's Desktop

Hi!

Huge thanks, this worked like a charm. But, now for some reason it's not hiding the rows I need before saving the new document. My ultimate goal is to hide all rows where Column C is not MF, then do the save us function. Any thoughts?

Macro to "Save As" to User's Desktop

Participate now!

Share

Users Viewing This Thread