Copy Special Values (VBA)

[JoeExcelHelp]

Hi Everyone,

I have this code that works fine in that it copies data from 'import_sheet' to another WB and saves it to my desktop
My problem is,
It copies everything including a row filter I have applied to 'import_sheet'
I need to just copy the data over that is visible on the 'import_sheet'

Any help is always apprciated TY

Sub GenerateCSV()
Dim Path As String
ThisWorkbook.Sheets("import_csv").Copy
Path = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\"
ActiveWorkbook.SaveAs Path & "rosterapps_BOS_" & Format(Now, "yyyymmddhhmm ") & ".xlsx"
End Sub

[JoeExcelHelp]

I should add that I want to copy the values from 'import_csv' and paste the values and value formats

Thanks again

[HansV]

Below the line

ThisWorkbook.Sheets("import_csv").Copy

insert

Code: Select all

    If ActiveSheet.FilterMode Then
        ActiveSheet.ShowAllData
    End If
    With ActiveSheet.UsedRange
        .Value = .Value
    End With

[JoeExcelHelp]

Thanks Hans

It still shows an activated filter in the destination WB
also with this adjustment, it now copies all data from 'import_csv' to the destination WB

Apologies if I wasnt clear
Im only trying to copy visible values from the origin WB 'import_csv' to the destination WB

[HansV]

How about this:

Code: Select all

Sub GenerateCSV()
    Dim Path As String
    Dim wbk As Workbook
    Dim wsh As Worksheet
    Set wbk = Workbooks.Add(xlWBATWorksheet)
    Set wsh = wbk.Worksheets(1)
    ThisWorkbook.Sheets("import_csv").UsedRange.Copy
    wsh.Range("A1").PasteSpecial Paste:=xlPasteValues
    wsh.Range("A1").PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
    Path = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\"
    wbk.SaveAs Path & "rosterapps_BOS_" & Format(Now, "yyyymmddhhmm ") & ".xlsx"
End Sub

[JoeExcelHelp]

Perfect Hans and Thank You

[JoeExcelHelp]

Hans,

I get an error '400' automatic update of links have been disabled.. which compromises the macro
I found the following line whicj=h im not certain how to apply
I'm sure you have a better way

Workbooks.Open FileName:="C:\VBA Code\Book1.xls", UpdateLinks:=False

[JoeExcelHelp]

Disregard Hans I got it to work

[JoeExcelHelp]

Hans,

The code work great in that it saves a .csv to my desktop as it should
the data within the .csv appears fine when I open the WB but scrambled in my file exlporer window (see attached)

Sub GenerateCSV()
Dim Path As String
Dim wbk As Workbook
Dim wsh As Worksheet
Set wbk = Workbooks.Add(xlWBATWorksheet)
Set wsh = wbk.Worksheets(1)
ThisWorkbook.Sheets("import_csv").UsedRange.Copy
wsh.Range("A1").PasteSpecial Paste:=xlPasteValues
wsh.Range("A1").PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Path = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\"
wbk.SaveAs Path & "rosterapps_BOS_" & Format(Now, "yyyymmddhhmm ") & ".csv"
wbk.close true
End Sub

[JoeExcelHelp]

I think I git it
appended - FileFormat:=xlCSV