Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PasteSpecial method of Range class failed | Excel Programming | |||
PasteSpecial Method of Range Class Failed Error a Different Versio | Excel Programming | |||
PasteSpecial Method of Range Class Failed Error | Excel Programming | |||
PasteSpecial method of Range class failed | Excel Programming | |||
Run-time error '1004' PasteSpecial Method of Range Class Failed | Excel Programming |