View Full Version : Copy-paste extracted data in values to reference sheet (VBA)

04-06-2016, 07:09 AM
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 _
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!

04-06-2016, 01:06 PM
crossposted: http://www.helpmij.nl/forum/showthread.php/888871-Automatisch-copy-paste-met-combinatie-name-manager-amp-VBA-(na-autofilter!)