VBA Copy & Paste values & Formats between workbooks

[Rollnation]

'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")

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

 

[Fluff]

Add

.Value

to the end of both those lines & removed the apostrophe

([COLOR=#ff0000]'[/COLOR]"CH8:DZ64")

 

[Rollnation]

Hmm, i missed that extra paranthesis - but removing the .value yielded the same result.

Blank cells in the destination workbook.

 

[Fluff]

Don't remove .value add it to the end of each of the 2 lines you highlighted