Copying from one workbook to another VBA paste values only

[orangebloss]

I have 2 workbooks, DTR Index and DTR Template.

When the DTR index is completed I need to open the DTR template and paste the values from one range to another, then save as a new file name.

I was partially there but it doesn't seem to want to work - I get a Run time error 91: Object variable or with block variable not set

Sub Sample()
Dim DTRINDEX As Workbook
Dim DTRTEMPLATE As Workbook
Dim path As String
Dim filename As String
path = "H:\Peter Test\"
filename = Range("F3")


Set DTRTEMPLATE = Workbooks.Open("T:\Server\Group\Two\PM\Peter\DTR_Template.xlsx")

'Copy the range to the new worksheet
DTRTEMPLATE.Sheets("DTR Form").Range("B2:F13").copy
'Paste special values to new worksheet
DTRINDEX.Sheets("DTR Form").Range("B2:F13").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
'Save the workbook with specified value as name
ActiveWorkbook.SaveAs filename:=path & filename & ".xlsx", FileFormat:=51

End Sub

Any help much appreciated

 

[Fluff]

You haven't set DTRINDEX therefore it's "Nothing"

 

[orangebloss]

Thanks - another go... but now it sticks on the DTRTEMPLATE.Sheets("DTR Form").Range("B2") etc with an automation error?

Sub Sample()
Dim DTRINDEX As Workbook
Dim DTRTEMPLATE As Workbook
Dim path As String
Dim filename As String
path = "H:\Peter Test\"
filename = Range("F3")
Set DTRINDEX = ActiveWorkbook

DTRINDEX.Sheets("DTR Form").Range("B2:F13").copy
Set DTRTEMPLATE = Workbooks.Open("T:\Guildford File Server No1\Group\NewProdDev\PMO\Peter\DTR_Template.xlsx")

'DTRINDEX.Range("B2:F13").Value = DTRTEMPLATE.Range("B2:F13").Value

DTRTEMPLATE.Sheets("DTR Form").Range("B2").PasteSpecial Paste:=xlPasteValues
 
DTRTEMPLATE.SaveAs filename:=path & filename & ".xlsx", FileFormat:=51

End Sub

 

[Fluff]

Do you have a sheet called "DTR Form" (check for any leading/trailing spaces) & is that sheet protected?

 

[orangebloss]

Name wise it is fine but it does have merged cells?

 

[Fluff]

Are the merged cells in the range B2:F13?

 

[orangebloss]

Yup current work around is to paste it on a hidden sheet where there are no merged cells and pull it through with formulas. Not ideal but....

 

[Fluff]

Merged cells are a major problem both within xl & VBA.
Best to avoid them like the plague.