Copy-pasting extracted data in values to reference sheet (VBA)

[Djani]

Dear all,

I have a working VBA script, but it's missing some (for me important) steps. I would like to paste the data in values - without formatting - and I would like to have a reference sheet instead of "ActiveSheet". The reference sheet would be "DATABASE AB" in the masterfile "VIVA PPT CENTER". I have no idea of how to integrate these in the current script;

Sub test()

FileName = "CENTER C&D.xlsb"

Workbooks.Open "I:\R&E Internal\01 Reporting & Tools\05 Pricing\01 Monthly Topics\01 VIVA\01 PC\VIVA TOOL 2.0\CENTER\" & FileName
 
Dim destWS As Worksheet, LR As Long
Set destWS = ActiveSheet
LR = Workbooks(FileName).Worksheets("DATABASE").Range("A" & Rows.Count).End(xlUp).Row
'Copy data from Sheet DATABASE to activesheet
Workbooks(FileName).Worksheets("DATABASE").Range("A1:AE" & LR).Copy _
ThisWorkbook.ActiveSheet.Range("A1")
    Application.CutCopyMode = False
Workbooks(FileName).Close False
End Sub

Can any of you lend me your expertise and help me out?

Many thanks in advance!

 

[ptownbro985]

Change the following two lines

Workbooks(Filename).Worksheets("DATABASE").Range("A1:AE" & LR).Copy _
ThisWorkbook.ActiveSheet.Range ("A1")

To this instead:

Workbooks(Filename).Worksheets("DATABASE").Range("A1:AE" & LR).Copy
destWS.Range("A1").PasteSpecial xlPasteValues

The changes I made:
1. You don't need the "_" at the end of the first line. Not sure if that was a typo, but that was combining the second line with the first
2. You don't need to refer to the sheet as "ThisWorkbook.Activesheet" again. You already defined it as "destWS" from a previous line
3. The Method you're looking for is "PasteSpecail" with the argument "xlPasteValues". That method also has ability to just paste formats, comments, etc... Just like how the regular pastspecial works in Excel

 

[Djani]

Thanks for your answer. However, another related question to this topic:

When I want to paste my values, I receive the error: "Method Range of object _Worksheet failed".

Sub FillAB()
Dim ws As Worksheet
Set ws = ActiveWorkbook.Worksheets("Sheet1")
  
'Name of file and pathlink
Filename = "MOSYBASE A&B.xlsx"
Workbooks.Open "\\fr-nesas-nas-01.nissaneu.nmcorp.nissan.biz\Users\NE70090\My Documents\Afstuderen\Informatiestructuur Nissan Europe\Toolanalyse\VIVA\" & Filename
 
Dim destWS As Worksheet, LR As Long
Set destWS = ActiveSheet
LR = Workbooks(Filename).Worksheets("DATABASE").Range("A" & Rows.Count).End(xlUp).Row

'Copy data from Sheet DATABASE to Reference Sheet
Workbooks(Filename).Worksheets("DATABASE").Range("A2:AE" & LR).Copy _
Destination:=ws.Cells(Worksheets("Sheet1").Rows.Count, "E").End(xlUp).Offset(1, 0)

ws.Range("E1:AE").PasteSpecial xlPasteValues
            
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

    Application.CutCopyMode = False
Workbooks(Filename).Close False
End Sub

It fails at the piece of code:

ws.Range("E1:AE").PasteSpecial xlPasteValues

Rest is working fine.
Do you or any of you knows how to solve this?