Just Copy Value From a Worksheet to another Workbook

[kuldeepjainesl]

This Code open a Workbook and Copy a data from a worksheet to paste this to another Sheet. I just need to paste as values while the current version is pasting all.

Sub ImportDataCN()

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim Sheet As Worksheet
Dim PasteStart As Range

Set wb1 = ActiveWorkbook
Set PasteStart = [CNDATA!A1]

FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a Report to Parse", _
FileFilter:="Report Files *.xl* (*.xl*),")

If FileToOpen = False Then
    MsgBox "No File Specified.", vbExclamation, "ERROR"
    Exit Sub
Else
    Set wb2 = Workbooks.Open(Filename:=FileToOpen)

    ActiveWorkbook.Sheets("Charts").Select
    ActiveWindow.SelectedSheets.Delete
    For Each Sheet In wb2.Sheets
        With Sheet.UsedRange
            .Copy PasteStart
            Set PasteStart = PasteStart.Offset(.Rows.Count)
            End With
    Next Sheet

End If

wb2.Close

  'Sheets("RRimport").Select
  'Rows(1).Delete
  Sheets("SR Allocation").Select

End Sub

 

[Smallman]

Hi Kuldeep

Change

.copy PasteStart

to something like

.copy
PasteStart.pastespecial xlpastevalues

That should ensure just the values are transferred.

Take care

Smallman

 

[kuldeepjainesl]

Hello Smallman,

"Object Does not support this property or method" was popup while tried your suggestion.

 

[Luke M]

Make sure Smallman's code is on two lines, and not 1 line as before. Since you're doing a PasteSpecial, we don't want to use the Copy method's destination argument.

 

[kuldeepjainesl]

Oh, I did the same mistake my magician....

 

[Smallman]

A simple cut and paste over the top of this line

.copy PasteStart

with the two lines I provided would have made your code work flawlessly.

Take care

Smallman