Trying to copy and paste grids not working

[vbanovice123]

I have used similar logic to copy and paste a range and it has worked. The only difference is that the below has the data in Grids, but I ensured that the cell size is the same.

Any help is appreciated.

This is the main file that calls another macro - File A

Private Sub CommandButton2_Click()
Call CopyTo13MHardCopy
End Sub

Sub CopyTo13MHardCopy()

Workbooks.Open Filename:= _
"F:\Focus\TestCopy\OnePagersTemplates_CopyPasteProject\OnePagers_Templates\OnePagersGrid_Template.xlsm"
Application.Run "OnePagersGrid_Template.xlsm!Sheet1.CopyToPrimeGridHardCopy"

End Sub


This is the called macro - File B

Sub CopyToPrimeGridHardCopy()
'copy_range_sheet_qtr(sheet, rngname)
copy_range_sheet_grid "Agency", "AgencyGrid"
copy_range_sheet_grid "RefiPlus", "RefiPlusGrid"
copy_range_sheet_grid "DU_Refi", "DU_RefiGrid"
copy_range_sheet_grid "NonHARP", "NonHARPGrid"
copy_range_sheet_grid "FHAVA", "FHAVAGrid"
copy_range_sheet_grid "FHA", "FHAGrid"
copy_range_sheet_grid "VA", "VAGrid"
copy_range_sheet_grid "Rural", "RuralGrid"
copy_range_sheet_grid "HFI", "HFIGrid"
copy_range_sheet_grid "AWM", "AWMGrid"

Application.ScreenUpdating = True
End Sub
Private Sub CommandButton2_Click()
Call CopyToPrimeGridHardCopy
End Sub


Private Sub CommandButton2_Click()
Call CopyToPrimeGridHardCopy
End Sub



This is the copy range function. I stepped through the code and it does not like the below:

Set rngDest = ActiveWorkbook.Sheets(sheet).Range("A1")


Public Sub copy_range_sheet_grid(sheet As String, rngname As String)
Dim rngCopy As Range
Dim rngDest As Range
On Error Resume Next
Set rngCopy = Sheets(sheet).Range(rngname)
On Error GoTo 0
' Make sure the range is valid
If rngCopy Is Nothing Then
MsgBox "Please select a worksheet range and try again.", vbExclamation, _
"No Range Selected"
Else
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"F:\Focus\TestCopy\OnePagersTemplates_CopyPasteProject\OnePagers_Templates\Prime_Grid_HardCopyV2.xlsx"
Set rngDest = ActiveWorkbook.Sheets(sheet).Range("A1")
'.Resize(rngCopy.Rows.Count, rngCopy.Columns.Count)
rngCopy.Copy
rngDest.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
rngDest.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
rngDest.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveWorkbook.Close SaveChanges:=True
Application.CutCopyMode = True 'Clear clipboard
Application.ScreenUpdating = True
End If

End Sub

 

[vbanovice123]

I narrowed it down to where the problem is: What is wrong here?

rngDest.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

 

[vbanovice123]

I got it to work!

rngDest.PasteSpecial Paste:=xlPasteValuesAndNumberFormats