Hi everyone,
I am having an issue that I can't seem to fix. I tried looking everywhere for a solution, but all the code I found matched the code I already had. I think I know where the code is breaking down, but unsure why. My destination workbook opens, but the sheet does not copy. I am trying to copying a worksheet to another user specified workbook. The worksheet does contain formulas that will need to be pasted as values on the specified workbook so if a solution to that could be provided that would be much appreciated. My code is below.
Sub ExportComparison()
Application.ScreenUpdating = False
Dim Fname As String
Dim SrcSht As Worksheet
Dim DestWbk As Workbook
'Sheet that contains comparison
Set SrcSht = ThisWorkbook.Sheets("HMO Comparisons")
'Dialog box opens to select file where sheet will be copied to
Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Select RFP to copy comparison to...")
If Fname = "False" Then Exit Sub
Set DestWbk = Workbooks.Open(Fname)
'Copy and paste sheet from Comparison Tool to RFP
SrcSht.Copy After:=DestWbk.Sheets(DestWbk.Sheets.Count)
DestWbk.Close True
Application.ScreenUpdating = True
End Sub
Bookmarks