PasteSpecial method for range class failed

[Guest]

Hello Gurus,

Attached is a code snippet where I get an error for copying and pasting. I
am trying to open different workbooks and copy certain data and paste it into
a single workbook. however for the first workbook I dont get an error in
pasting values, but for the second workbook excel does copy the values but
cannot paste it. Is there something I am missing in the code.

thank you in advance

For count = LBound(ID) To UBound(ID)
Set qWb = Workbooks.Open(FileName:=workingDir &
Application.PathSeparator & ncases(ID(count)), UpdateLinks:=False)
qWb.Sheets("FF_Forecast").Range("A4:AX60000").Copy

Set NewWorkbook = Workbooks.Open(FileName:=WorkbookName,
UpdateLinks:=False)
NewWorkbook.Worksheets.Add(after:=Worksheets(Worksheets.count)).Name
= "Forecast" & CStr(count + 1)
NewWorkbook.Sheets("Forecast" & CStr(count +
1)).Range("A4:AX60000").PasteSpecial Paste:=xlPasteValues
NewWorkbook.Names.Add Name:="Start" & CStr(count + 1),
RefersTo:="=Forecast" & CStr(count + 1) & "!$A$6"
NewWorkbook.Sheets("Forecast" & CStr(count + 1)).Range("A1").Select

'call the function here and do all the calculations

qWb.Save
qWb.Close
NewWorkbook.Save
Next count

 

[Guest]

Thanks Guys... I found the solution.. I need to close the workbook before I
can open again.

For count = LBound(ID) To UBound(ID)
Set qWb = Workbooks.Open(FileName:=workingDir &
Application.PathSeparator & ncases(ID(count)), UpdateLinks:=False)
qWb.Sheets("FF_Forecast").Range("A4:AX60000").Copy

Set NewWorkbook = Workbooks.Open(FileName:=WorkbookName,
UpdateLinks:=False)
NewWorkbook.Worksheets.Add(after:=Worksheets(Worksheets.count)).Name
= "Forecast" & CStr(count + 1)
NewWorkbook.Sheets("Forecast" & CStr(count +
1)).Range("A4:AX60000").PasteSpecial Paste:=xlPasteValues
NewWorkbook.Names.Add Name:="Start" & CStr(count + 1),
RefersTo:="=Forecast" & CStr(count + 1) & "!$A$6"
NewWorkbook.Sheets("Forecast" & CStr(count + 1)).Range("A1").Select

'call the function here and do all the calculations

qWb.Save
qWb.Close
NewWorkbook.Save
#### NewWorkbook.Close
Next count