VBA Copy & Paste values & Formats between workbooks

Rollnation

New Member
Joined
Jan 17, 2017
Messages
17
'My code is running through without error all the way to Msgbox.
However the values are cleared from "WbRevDash" workbook and new values are not pasted in. The ranges are just blank.

Any suggestions are helpful! i think it has to do with this part of the code but im a rookie and rely on the pro's around here.

Thanks to all who help around here!



'Now, transfer values from ebRevForecast to wbRevRash:
wbRevDash.Sheets("Data").Range("A8:AS64").Value = wbRevForecast.Sheets("FinalExport").Range("A8:AS64")
wbRevDash.Sheets("Data").Range("CH8:DZ64").Value = wbRevForecast.Sheets("FinalExport").Range("AU8:AS64")




Code:
Option Explicit


Sub FinalExport()


    Dim directory As String
    Dim curDirectory As String
    Dim fileName As Variant
    Dim wbRevForecast As Workbook
    Dim wbRevDash As Workbook
   
    Set wbRevForecast = ThisWorkbook
    
'******************************************SETTINGS***********************************************************
    
    directory = "K:\CODE 150\COST\RevForecastTool"
    fileName = Dir(directory & "*.xl??")
    
'*************************************************************************************************************
    
    'Changes drive and directory
    ChDrive directory
    ChDir directory
    
    'Open filepath,selected Revenue Dashboard
    fileName = Application.GetOpenFilename(MultiSelect:=False)
    
    'if user cancels
    'If fileName = False Then GoTo exitsub


    
'Turn off screen updating and display alerts
    With Application
        .ScreenUpdating = False: .DisplayAlerts = False
    End With
    
        'On Error GoTo exitsub
    Set wbRevDash = Workbooks.Open(fileName, False, True)
    
    With wbRevDash
        .Sheets("Data").Range("A8:AS64").ClearContents
        .Sheets("Data").Range("CH8:DZ64").ClearContents
    End With


'Now, transfer values from wbRevForecast to wbRevRash:
wbRevDash.Sheets("Data").Range("A8:AS64").Value = wbRevForecast.Sheets("FinalExport").Range("A8:AS64")
wbRevDash.Sheets("Data").Range('"CH8:DZ64").Value = wbRevForecast.Sheets("FinalExport").Range("AU8:AS64")
    
'Turn on screen updating and display alerts
    With Application
        .ScreenUpdating = True: .DisplayAlerts = True


'Selects Revenue dash worksheet
    wbRevDash.Sheets("Dash").Select
    
    End With
    
MsgBox "Data Export is complete. Validate data in dashboard"


End Sub
 
Last edited by a moderator:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,690
Office Version
365
Platform
Windows
Add
Code:
.Value
to the end of both those lines & removed the apostrophe
Code:
([COLOR=#ff0000]'[/COLOR]"CH8:DZ64")
 

Rollnation

New Member
Joined
Jan 17, 2017
Messages
17
Hmm, i missed that extra paranthesis - but removing the .value yielded the same result.

Blank cells in the destination workbook.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,690
Office Version
365
Platform
Windows
Don't remove .value add it to the end of each of the 2 lines you highlighted
 

Forum statistics

Threads
1,085,375
Messages
5,383,278
Members
401,821
Latest member
gb199

Some videos you may like

This Week's Hot Topics

Top