Djani
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 _
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!
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!