ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   PasteSpecial method for range class failed (https://www.excelbanter.com/excel-programming/399401-pastespecial-method-range-class-failed.html)

Naga Kiran

PasteSpecial method for range class failed
 
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(Works heets.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



Naga Kiran[_2_]

PasteSpecial method for range class failed
 
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(Works heets.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



--
Naga Kiran


"Naga Kiran" wrote:

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(Works heets.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




All times are GMT +1. The time now is 03:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com