|
Run-time error €˜1004
I have the following macro in a workbook and a user keeps getting the same
runtime error that I cannot duplicate. MACRO CODE--------------------------------- Sub Quote_Wrapup() 'To stop screen flicker Application.ScreenUpdating = False Range("quote_date").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("qdata5,qdata6").Font.ColorIndex = 2 'To delete delivery address lines if 1st line empty If IsEmpty(Range("deliver_line1")) _ Then Sheets(1).Range("deliver_rows").EntireRow.Delete 'No End If required as only one action as a result of the If Range("Item_Nos").SpecialCells(xlCellTypeBlanks).E ntireRow.Delete Call paste_values Sub paste_values() With Sheet1.Range("A:F") .Copy .PasteSpecial xlPasteValues End With Application.CutCopyMode = False End Sub ------------------------------------------------------ The user gets Run-time error €˜1004 The information cannot be pasted because the copy area and the paste area are not the same size and shape. and the when debugged the following line is highlighted. .................................................. ....................... .PasteSpecial xlPasteValues .................................................. ....................... As the copy range is ("A:F") .i.e. columns I don't understand how this can happen. Grateful for any advice on this puzzle. |
Run-time error €˜1004
I do not see anything that should generate that error. As a guess there is
more to this such as conversion to XL2007 or such. That being said to convert to values can be done in a single line something like this... Sheet1.Range("A:F").Value = Sheet1.Range("A:F").Value No copy and paste or such... -- HTH... Jim Thomlinson "Code Numpty" wrote: I have the following macro in a workbook and a user keeps getting the same runtime error that I cannot duplicate. MACRO CODE--------------------------------- Sub Quote_Wrapup() 'To stop screen flicker Application.ScreenUpdating = False Range("quote_date").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("qdata5,qdata6").Font.ColorIndex = 2 'To delete delivery address lines if 1st line empty If IsEmpty(Range("deliver_line1")) _ Then Sheets(1).Range("deliver_rows").EntireRow.Delete 'No End If required as only one action as a result of the If Range("Item_Nos").SpecialCells(xlCellTypeBlanks).E ntireRow.Delete Call paste_values Sub paste_values() With Sheet1.Range("A:F") .Copy .PasteSpecial xlPasteValues End With Application.CutCopyMode = False End Sub ------------------------------------------------------ The user gets Run-time error €˜1004 The information cannot be pasted because the copy area and the paste area are not the same size and shape. and the when debugged the following line is highlighted. .................................................. ...................... .PasteSpecial xlPasteValues .................................................. ...................... As the copy range is ("A:F") .i.e. columns I don't understand how this can happen. Grateful for any advice on this puzzle. |
Run-time error €˜1004
The may be a problem with sheet1 rreference in this line
With Sheet1.Range("A:F") Sheet1 is always defined. shouuld use With Sheets(1).Range("A:F") or better With Sheets("sheet1").Range("A:F") "Code Numpty" wrote: I have the following macro in a workbook and a user keeps getting the same runtime error that I cannot duplicate. MACRO CODE--------------------------------- Sub Quote_Wrapup() 'To stop screen flicker Application.ScreenUpdating = False Range("quote_date").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("qdata5,qdata6").Font.ColorIndex = 2 'To delete delivery address lines if 1st line empty If IsEmpty(Range("deliver_line1")) _ Then Sheets(1).Range("deliver_rows").EntireRow.Delete 'No End If required as only one action as a result of the If Range("Item_Nos").SpecialCells(xlCellTypeBlanks).E ntireRow.Delete Call paste_values Sub paste_values() With Sheet1.Range("A:F") .Copy .PasteSpecial xlPasteValues End With Application.CutCopyMode = False End Sub ------------------------------------------------------ The user gets Run-time error €˜1004 The information cannot be pasted because the copy area and the paste area are not the same size and shape. and the when debugged the following line is highlighted. .................................................. ...................... .PasteSpecial xlPasteValues .................................................. ...................... As the copy range is ("A:F") .i.e. columns I don't understand how this can happen. Grateful for any advice on this puzzle. |
Run-time error €˜1004
Why should they use
With Sheets(1).Range("A:F") if Sheet1 is the codename for the sheet? That doesn't make sense to me. Barb Reinhardt "Joel" wrote: The may be a problem with sheet1 rreference in this line With Sheet1.Range("A:F") Sheet1 is always defined. shouuld use With Sheets(1).Range("A:F") or better With Sheets("sheet1").Range("A:F") "Code Numpty" wrote: I have the following macro in a workbook and a user keeps getting the same runtime error that I cannot duplicate. MACRO CODE--------------------------------- Sub Quote_Wrapup() 'To stop screen flicker Application.ScreenUpdating = False Range("quote_date").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("qdata5,qdata6").Font.ColorIndex = 2 'To delete delivery address lines if 1st line empty If IsEmpty(Range("deliver_line1")) _ Then Sheets(1).Range("deliver_rows").EntireRow.Delete 'No End If required as only one action as a result of the If Range("Item_Nos").SpecialCells(xlCellTypeBlanks).E ntireRow.Delete Call paste_values Sub paste_values() With Sheet1.Range("A:F") .Copy .PasteSpecial xlPasteValues End With Application.CutCopyMode = False End Sub ------------------------------------------------------ The user gets Run-time error €˜1004 The information cannot be pasted because the copy area and the paste area are not the same size and shape. and the when debugged the following line is highlighted. .................................................. ...................... .PasteSpecial xlPasteValues .................................................. ...................... As the copy range is ("A:F") .i.e. columns I don't understand how this can happen. Grateful for any advice on this puzzle. |
Run-time error €˜1004
Barb: If you delete Sheet one you no longer have a sheet1 codename. And if
you rename sheet2 sheet1 then sheet1 has a code name sheet2. "Barb Reinhardt" wrote: Why should they use With Sheets(1).Range("A:F") if Sheet1 is the codename for the sheet? That doesn't make sense to me. Barb Reinhardt "Joel" wrote: The may be a problem with sheet1 rreference in this line With Sheet1.Range("A:F") Sheet1 is always defined. shouuld use With Sheets(1).Range("A:F") or better With Sheets("sheet1").Range("A:F") "Code Numpty" wrote: I have the following macro in a workbook and a user keeps getting the same runtime error that I cannot duplicate. MACRO CODE--------------------------------- Sub Quote_Wrapup() 'To stop screen flicker Application.ScreenUpdating = False Range("quote_date").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("qdata5,qdata6").Font.ColorIndex = 2 'To delete delivery address lines if 1st line empty If IsEmpty(Range("deliver_line1")) _ Then Sheets(1).Range("deliver_rows").EntireRow.Delete 'No End If required as only one action as a result of the If Range("Item_Nos").SpecialCells(xlCellTypeBlanks).E ntireRow.Delete Call paste_values Sub paste_values() With Sheet1.Range("A:F") .Copy .PasteSpecial xlPasteValues End With Application.CutCopyMode = False End Sub ------------------------------------------------------ The user gets Run-time error €˜1004 The information cannot be pasted because the copy area and the paste area are not the same size and shape. and the when debugged the following line is highlighted. .................................................. ...................... .PasteSpecial xlPasteValues .................................................. ...................... As the copy range is ("A:F") .i.e. columns I don't understand how this can happen. Grateful for any advice on this puzzle. |
Run-time error €˜1004
I too am not following you... If Sheet1 does not exist then you get a 424
Object Required error (or a compile error if you are using option explicit). Sheets(1) is about the worst code you can use as there are any number of things that can change the index numbers of the sheets, so there is no reliable way of knowing which sheet is at index number 1. Sheets("Sheet1") is also not great code as it is vulnerable to end user changes to the tab name... The most reliable code uses the code name of the sheet to get a direct handle to the object... What do you mean by "Sheet1 is always defined". If I delete the sheet with code name Sheet1, then it is not defined. The object is gone. It is definitely advisable to change the code names of the sheets. If I delete Sheet1 it is gone. But if I close the book and reopen it and add a new sheet it will be a new sheet with code name Sheet1... By changing the code name to something like shtMySheet I get around that potential problem and end up with foolproof code. -- HTH... Jim Thomlinson "Joel" wrote: Barb: If you delete Sheet one you no longer have a sheet1 codename. And if you rename sheet2 sheet1 then sheet1 has a code name sheet2. "Barb Reinhardt" wrote: Why should they use With Sheets(1).Range("A:F") if Sheet1 is the codename for the sheet? That doesn't make sense to me. Barb Reinhardt "Joel" wrote: The may be a problem with sheet1 rreference in this line With Sheet1.Range("A:F") Sheet1 is always defined. shouuld use With Sheets(1).Range("A:F") or better With Sheets("sheet1").Range("A:F") "Code Numpty" wrote: I have the following macro in a workbook and a user keeps getting the same runtime error that I cannot duplicate. MACRO CODE--------------------------------- Sub Quote_Wrapup() 'To stop screen flicker Application.ScreenUpdating = False Range("quote_date").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("qdata5,qdata6").Font.ColorIndex = 2 'To delete delivery address lines if 1st line empty If IsEmpty(Range("deliver_line1")) _ Then Sheets(1).Range("deliver_rows").EntireRow.Delete 'No End If required as only one action as a result of the If Range("Item_Nos").SpecialCells(xlCellTypeBlanks).E ntireRow.Delete Call paste_values Sub paste_values() With Sheet1.Range("A:F") .Copy .PasteSpecial xlPasteValues End With Application.CutCopyMode = False End Sub ------------------------------------------------------ The user gets Run-time error €˜1004 The information cannot be pasted because the copy area and the paste area are not the same size and shape. and the when debugged the following line is highlighted. .................................................. ...................... .PasteSpecial xlPasteValues .................................................. ...................... As the copy range is ("A:F") .i.e. columns I don't understand how this can happen. Grateful for any advice on this puzzle. |
Run-time error €˜1004
It is only fool proof if somebody doesn't delete the sheet. I like to have
the sheet names visible so I can easily see the problem and fix the problem when sheets are re-named, moved, or deleted. "Jim Thomlinson" wrote: I too am not following you... If Sheet1 does not exist then you get a 424 Object Required error (or a compile error if you are using option explicit). Sheets(1) is about the worst code you can use as there are any number of things that can change the index numbers of the sheets, so there is no reliable way of knowing which sheet is at index number 1. Sheets("Sheet1") is also not great code as it is vulnerable to end user changes to the tab name... The most reliable code uses the code name of the sheet to get a direct handle to the object... What do you mean by "Sheet1 is always defined". If I delete the sheet with code name Sheet1, then it is not defined. The object is gone. It is definitely advisable to change the code names of the sheets. If I delete Sheet1 it is gone. But if I close the book and reopen it and add a new sheet it will be a new sheet with code name Sheet1... By changing the code name to something like shtMySheet I get around that potential problem and end up with foolproof code. -- HTH... Jim Thomlinson "Joel" wrote: Barb: If you delete Sheet one you no longer have a sheet1 codename. And if you rename sheet2 sheet1 then sheet1 has a code name sheet2. "Barb Reinhardt" wrote: Why should they use With Sheets(1).Range("A:F") if Sheet1 is the codename for the sheet? That doesn't make sense to me. Barb Reinhardt "Joel" wrote: The may be a problem with sheet1 rreference in this line With Sheet1.Range("A:F") Sheet1 is always defined. shouuld use With Sheets(1).Range("A:F") or better With Sheets("sheet1").Range("A:F") "Code Numpty" wrote: I have the following macro in a workbook and a user keeps getting the same runtime error that I cannot duplicate. MACRO CODE--------------------------------- Sub Quote_Wrapup() 'To stop screen flicker Application.ScreenUpdating = False Range("quote_date").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("qdata5,qdata6").Font.ColorIndex = 2 'To delete delivery address lines if 1st line empty If IsEmpty(Range("deliver_line1")) _ Then Sheets(1).Range("deliver_rows").EntireRow.Delete 'No End If required as only one action as a result of the If Range("Item_Nos").SpecialCells(xlCellTypeBlanks).E ntireRow.Delete Call paste_values Sub paste_values() With Sheet1.Range("A:F") .Copy .PasteSpecial xlPasteValues End With Application.CutCopyMode = False End Sub ------------------------------------------------------ The user gets Run-time error €˜1004 The information cannot be pasted because the copy area and the paste area are not the same size and shape. and the when debugged the following line is highlighted. .................................................. ...................... .PasteSpecial xlPasteValues .................................................. ...................... As the copy range is ("A:F") .i.e. columns I don't understand how this can happen. Grateful for any advice on this puzzle. |
Run-time error €˜1004
To each his own... But your explanation still does not expain the error. If
the sheet does not exist then you get a 424 or compile error. -- HTH... Jim Thomlinson "Joel" wrote: It is only fool proof if somebody doesn't delete the sheet. I like to have the sheet names visible so I can easily see the problem and fix the problem when sheets are re-named, moved, or deleted. "Jim Thomlinson" wrote: I too am not following you... If Sheet1 does not exist then you get a 424 Object Required error (or a compile error if you are using option explicit). Sheets(1) is about the worst code you can use as there are any number of things that can change the index numbers of the sheets, so there is no reliable way of knowing which sheet is at index number 1. Sheets("Sheet1") is also not great code as it is vulnerable to end user changes to the tab name... The most reliable code uses the code name of the sheet to get a direct handle to the object... What do you mean by "Sheet1 is always defined". If I delete the sheet with code name Sheet1, then it is not defined. The object is gone. It is definitely advisable to change the code names of the sheets. If I delete Sheet1 it is gone. But if I close the book and reopen it and add a new sheet it will be a new sheet with code name Sheet1... By changing the code name to something like shtMySheet I get around that potential problem and end up with foolproof code. -- HTH... Jim Thomlinson "Joel" wrote: Barb: If you delete Sheet one you no longer have a sheet1 codename. And if you rename sheet2 sheet1 then sheet1 has a code name sheet2. "Barb Reinhardt" wrote: Why should they use With Sheets(1).Range("A:F") if Sheet1 is the codename for the sheet? That doesn't make sense to me. Barb Reinhardt "Joel" wrote: The may be a problem with sheet1 rreference in this line With Sheet1.Range("A:F") Sheet1 is always defined. shouuld use With Sheets(1).Range("A:F") or better With Sheets("sheet1").Range("A:F") "Code Numpty" wrote: I have the following macro in a workbook and a user keeps getting the same runtime error that I cannot duplicate. MACRO CODE--------------------------------- Sub Quote_Wrapup() 'To stop screen flicker Application.ScreenUpdating = False Range("quote_date").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("qdata5,qdata6").Font.ColorIndex = 2 'To delete delivery address lines if 1st line empty If IsEmpty(Range("deliver_line1")) _ Then Sheets(1).Range("deliver_rows").EntireRow.Delete 'No End If required as only one action as a result of the If Range("Item_Nos").SpecialCells(xlCellTypeBlanks).E ntireRow.Delete Call paste_values Sub paste_values() With Sheet1.Range("A:F") .Copy .PasteSpecial xlPasteValues End With Application.CutCopyMode = False End Sub ------------------------------------------------------ The user gets Run-time error €˜1004 The information cannot be pasted because the copy area and the paste area are not the same size and shape. and the when debugged the following line is highlighted. .................................................. ...................... .PasteSpecial xlPasteValues .................................................. ...................... As the copy range is ("A:F") .i.e. columns I don't understand how this can happen. Grateful for any advice on this puzzle. |
Run-time error €˜1004
Jim,
FWIW, I use worksheet code name all the time now. That way, if I (or a user) decides to change the worksheet name, the code still functions. And if the worksheet isn't there, I build in an error check for that as much as possible. Barb Reinhardt "Jim Thomlinson" wrote: To each his own... But your explanation still does not expain the error. If the sheet does not exist then you get a 424 or compile error. -- HTH... Jim Thomlinson "Joel" wrote: It is only fool proof if somebody doesn't delete the sheet. I like to have the sheet names visible so I can easily see the problem and fix the problem when sheets are re-named, moved, or deleted. "Jim Thomlinson" wrote: I too am not following you... If Sheet1 does not exist then you get a 424 Object Required error (or a compile error if you are using option explicit). Sheets(1) is about the worst code you can use as there are any number of things that can change the index numbers of the sheets, so there is no reliable way of knowing which sheet is at index number 1. Sheets("Sheet1") is also not great code as it is vulnerable to end user changes to the tab name... The most reliable code uses the code name of the sheet to get a direct handle to the object... What do you mean by "Sheet1 is always defined". If I delete the sheet with code name Sheet1, then it is not defined. The object is gone. It is definitely advisable to change the code names of the sheets. If I delete Sheet1 it is gone. But if I close the book and reopen it and add a new sheet it will be a new sheet with code name Sheet1... By changing the code name to something like shtMySheet I get around that potential problem and end up with foolproof code. -- HTH... Jim Thomlinson "Joel" wrote: Barb: If you delete Sheet one you no longer have a sheet1 codename. And if you rename sheet2 sheet1 then sheet1 has a code name sheet2. "Barb Reinhardt" wrote: Why should they use With Sheets(1).Range("A:F") if Sheet1 is the codename for the sheet? That doesn't make sense to me. Barb Reinhardt "Joel" wrote: The may be a problem with sheet1 rreference in this line With Sheet1.Range("A:F") Sheet1 is always defined. shouuld use With Sheets(1).Range("A:F") or better With Sheets("sheet1").Range("A:F") "Code Numpty" wrote: I have the following macro in a workbook and a user keeps getting the same runtime error that I cannot duplicate. MACRO CODE--------------------------------- Sub Quote_Wrapup() 'To stop screen flicker Application.ScreenUpdating = False Range("quote_date").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("qdata5,qdata6").Font.ColorIndex = 2 'To delete delivery address lines if 1st line empty If IsEmpty(Range("deliver_line1")) _ Then Sheets(1).Range("deliver_rows").EntireRow.Delete 'No End If required as only one action as a result of the If Range("Item_Nos").SpecialCells(xlCellTypeBlanks).E ntireRow.Delete Call paste_values Sub paste_values() With Sheet1.Range("A:F") .Copy .PasteSpecial xlPasteValues End With Application.CutCopyMode = False End Sub ------------------------------------------------------ The user gets Run-time error €˜1004 The information cannot be pasted because the copy area and the paste area are not the same size and shape. and the when debugged the following line is highlighted. .................................................. ...................... .PasteSpecial xlPasteValues .................................................. ...................... As the copy range is ("A:F") .i.e. columns I don't understand how this can happen. Grateful for any advice on this puzzle. |
Run-time error €˜1004
You and me both... BTW I have seen the quality of the code you have been
posting and I must say... Very Nice. A long way from your original efforts. At this point you can hold your own with the best of 'em... -- HTH... Jim Thomlinson "Barb Reinhardt" wrote: Jim, FWIW, I use worksheet code name all the time now. That way, if I (or a user) decides to change the worksheet name, the code still functions. And if the worksheet isn't there, I build in an error check for that as much as possible. Barb Reinhardt "Jim Thomlinson" wrote: To each his own... But your explanation still does not expain the error. If the sheet does not exist then you get a 424 or compile error. -- HTH... Jim Thomlinson "Joel" wrote: It is only fool proof if somebody doesn't delete the sheet. I like to have the sheet names visible so I can easily see the problem and fix the problem when sheets are re-named, moved, or deleted. "Jim Thomlinson" wrote: I too am not following you... If Sheet1 does not exist then you get a 424 Object Required error (or a compile error if you are using option explicit). Sheets(1) is about the worst code you can use as there are any number of things that can change the index numbers of the sheets, so there is no reliable way of knowing which sheet is at index number 1. Sheets("Sheet1") is also not great code as it is vulnerable to end user changes to the tab name... The most reliable code uses the code name of the sheet to get a direct handle to the object... What do you mean by "Sheet1 is always defined". If I delete the sheet with code name Sheet1, then it is not defined. The object is gone. It is definitely advisable to change the code names of the sheets. If I delete Sheet1 it is gone. But if I close the book and reopen it and add a new sheet it will be a new sheet with code name Sheet1... By changing the code name to something like shtMySheet I get around that potential problem and end up with foolproof code. -- HTH... Jim Thomlinson "Joel" wrote: Barb: If you delete Sheet one you no longer have a sheet1 codename. And if you rename sheet2 sheet1 then sheet1 has a code name sheet2. "Barb Reinhardt" wrote: Why should they use With Sheets(1).Range("A:F") if Sheet1 is the codename for the sheet? That doesn't make sense to me. Barb Reinhardt "Joel" wrote: The may be a problem with sheet1 rreference in this line With Sheet1.Range("A:F") Sheet1 is always defined. shouuld use With Sheets(1).Range("A:F") or better With Sheets("sheet1").Range("A:F") "Code Numpty" wrote: I have the following macro in a workbook and a user keeps getting the same runtime error that I cannot duplicate. MACRO CODE--------------------------------- Sub Quote_Wrapup() 'To stop screen flicker Application.ScreenUpdating = False Range("quote_date").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("qdata5,qdata6").Font.ColorIndex = 2 'To delete delivery address lines if 1st line empty If IsEmpty(Range("deliver_line1")) _ Then Sheets(1).Range("deliver_rows").EntireRow.Delete 'No End If required as only one action as a result of the If Range("Item_Nos").SpecialCells(xlCellTypeBlanks).E ntireRow.Delete Call paste_values Sub paste_values() With Sheet1.Range("A:F") .Copy .PasteSpecial xlPasteValues End With Application.CutCopyMode = False End Sub ------------------------------------------------------ The user gets Run-time error €˜1004 The information cannot be pasted because the copy area and the paste area are not the same size and shape. and the when debugged the following line is highlighted. .................................................. ...................... .PasteSpecial xlPasteValues .................................................. ...................... As the copy range is ("A:F") .i.e. columns I don't understand how this can happen. Grateful for any advice on this puzzle. |
Run-time error €˜1004
Thanks. I learned it all here (+ doing a lot of googling). I remember the
first piece of code I did. It took weeks. Now I could probably write it in a couple of hours and be close. Barb Reinhardt "Jim Thomlinson" wrote: You and me both... BTW I have seen the quality of the code you have been posting and I must say... Very Nice. A long way from your original efforts. At this point you can hold your own with the best of 'em... -- HTH... Jim Thomlinson "Barb Reinhardt" wrote: Jim, FWIW, I use worksheet code name all the time now. That way, if I (or a user) decides to change the worksheet name, the code still functions. And if the worksheet isn't there, I build in an error check for that as much as possible. Barb Reinhardt "Jim Thomlinson" wrote: To each his own... But your explanation still does not expain the error. If the sheet does not exist then you get a 424 or compile error. -- HTH... Jim Thomlinson "Joel" wrote: It is only fool proof if somebody doesn't delete the sheet. I like to have the sheet names visible so I can easily see the problem and fix the problem when sheets are re-named, moved, or deleted. "Jim Thomlinson" wrote: I too am not following you... If Sheet1 does not exist then you get a 424 Object Required error (or a compile error if you are using option explicit). Sheets(1) is about the worst code you can use as there are any number of things that can change the index numbers of the sheets, so there is no reliable way of knowing which sheet is at index number 1. Sheets("Sheet1") is also not great code as it is vulnerable to end user changes to the tab name... The most reliable code uses the code name of the sheet to get a direct handle to the object... What do you mean by "Sheet1 is always defined". If I delete the sheet with code name Sheet1, then it is not defined. The object is gone. It is definitely advisable to change the code names of the sheets. If I delete Sheet1 it is gone. But if I close the book and reopen it and add a new sheet it will be a new sheet with code name Sheet1... By changing the code name to something like shtMySheet I get around that potential problem and end up with foolproof code. -- HTH... Jim Thomlinson "Joel" wrote: Barb: If you delete Sheet one you no longer have a sheet1 codename. And if you rename sheet2 sheet1 then sheet1 has a code name sheet2. "Barb Reinhardt" wrote: Why should they use With Sheets(1).Range("A:F") if Sheet1 is the codename for the sheet? That doesn't make sense to me. Barb Reinhardt "Joel" wrote: The may be a problem with sheet1 rreference in this line With Sheet1.Range("A:F") Sheet1 is always defined. shouuld use With Sheets(1).Range("A:F") or better With Sheets("sheet1").Range("A:F") "Code Numpty" wrote: I have the following macro in a workbook and a user keeps getting the same runtime error that I cannot duplicate. MACRO CODE--------------------------------- Sub Quote_Wrapup() 'To stop screen flicker Application.ScreenUpdating = False Range("quote_date").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("qdata5,qdata6").Font.ColorIndex = 2 'To delete delivery address lines if 1st line empty If IsEmpty(Range("deliver_line1")) _ Then Sheets(1).Range("deliver_rows").EntireRow.Delete 'No End If required as only one action as a result of the If Range("Item_Nos").SpecialCells(xlCellTypeBlanks).E ntireRow.Delete Call paste_values Sub paste_values() With Sheet1.Range("A:F") .Copy .PasteSpecial xlPasteValues End With Application.CutCopyMode = False End Sub ------------------------------------------------------ The user gets Run-time error €˜1004 The information cannot be pasted because the copy area and the paste area are not the same size and shape. and the when debugged the following line is highlighted. .................................................. ...................... .PasteSpecial xlPasteValues .................................................. ...................... As the copy range is ("A:F") .i.e. columns I don't understand how this can happen. Grateful for any advice on this puzzle. |
Run-time error €˜1004
It is a little difficult as I cannot seem to replicate the problem and the
user is on Excel 2003 too. I can't remember why I went with my original method in the first place but having read all the replies and interesting discussion I have tried Jim's. Sheet1.Range("A:F").Value = Sheet1.Range("A:F").Value I await what happens when tested by the user who gets the error. |
| All times are GMT +1. The time now is 01:52 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com