Paste values only

[Victtor]

Here is a macro I recorded. I need this to paste values only in the new sheet. The blank cells must truly be blank and not with a formula in them. The online webpage I upload this to will return an error if there is data in the blank cells. Thanks for your help


Sub MBMexport()
'
' MBMexport Macro
'
' Keyboard Shortcut: Ctrl+Shift+V
'
Range("D5").Select
Sheets("export").Select
Sheets("export").Copy Before:=Workbooks("MBMexportfile.csv").Sheets(1)
ActiveWorkbook.Save
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

 

[Fluff]

How about

Sub MBMexport()
'
' MBMexport Macro
'
' Keyboard Shortcut: Ctrl+Shift+V
'
Range("D5").Select
Sheets("export").Select
Sheets("export").Copy Before:=Workbooks("MBMexportfile.csv").Sheets(1)
With ActiveSheet.UsedRange
   .Value = .Value
End With
ActiveWorkbook.Save
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

 

[Victtor]

How about

Sub MBMexport()
'
' MBMexport Macro
'
' Keyboard Shortcut: Ctrl+Shift+V
'
Range("D5").Select
Sheets("export").Select
Sheets("export").Copy Before:=Workbooks("MBMexportfile.csv").Sheets(1)
With ActiveSheet.UsedRange
   .Value = .Value
End With
ActiveWorkbook.Save
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

Something is still off. It gave me the error message again and showed every line after the last line with data in it as having a non numeric value in it.

I always end up copying the cells in columns A and B that have numeric values in them to columns C and D and then just deleting columns A and B. This workaround fixes the problem, I just need a macro that can eliminate this manual step

 

[Fluff]

What formulae do those columns contain?

 

[Victtor]

What formulae do those columns contain?

Cells A1:A187 in column A of the source file contain the following:
=IFERROR(INDEX(Source!$C$2:$C$187,AGGREGATE(15,6,ROW($C$2:$C$187)/((Source!$I$2:$I$187>0)),ROWS(A$2:A2))-(2-1),1),"")

Cells B1:B187 in column b of the source file contain the following:
=IFERROR(INDEX(Source!$I$2:$I$187,AGGREGATE(15,6,ROW($C$2:$C$187)/((Source!$I$2:$I$187>0)),ROWS(A$2:A2))-(2-1),1),"")

So the following Macro in the first post copies all cells with data in them and pastes it into the destination MBMExport.csv file

Sub MBMexport()
'
' MBMexport Macro
'
' Keyboard Shortcut: Ctrl+Shift+V
'
Range("D5").Select
Sheets("export").Select
Sheets("export").Copy Before:=Workbooks("MBMexportfile.csv").Sheets(1)
ActiveWorkbook.Save
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

 

[MARK858]

@Victtor, what happens with the macro below?
Make sure you have a backup of the data before running as it is deleting columns and rows.

Sub MBMexport()
    '
    ' MBMexport Macro
    '
    ' Keyboard Shortcut: Ctrl+Shift+V
    '

    Dim LastRow As Long, LastCol As Long
    Application.ScreenUpdating = False

    With Workbooks("MBMexportfile.csv")
        Sheets("export").Copy Before:=.Sheets(1)
        .Sheets(1).UsedRange.Value = .Sheets(1).UsedRange.Value

        On Error Resume Next

        With .Sheets(1)
            LastRow = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                                  LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            LastCol = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                                  LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            .Range(.Cells(1, LastCol + 1), .Cells(Rows.Count, Columns.Count)).Delete
            .Range(.Cells(LastRow + 1, 1), .Cells(Rows.Count, Columns.Count)).Delete
        End With

        On Error GoTo 0

    End With


    Application.ScreenUpdating = True

    '    ActiveWorkbook.Save
    '    ActiveWorkbook.Save
    '    ActiveWindow.Close
End Sub