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 |
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