ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Error in vba line (https://www.excelbanter.com/excel-programming/372484-error-vba-line.html)

ADK
Error in vba line
 
Using Excel 2000:

I get a runtime error '1004' PasteSpecial method of Range class failed

Selection.PasteSpecial Paste:=xlDataValidation, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

Here is code:

Sub addrow()

Dim CellOne As Range

Set CellOne = ActiveCell

If CellOne.Value = 1 Then

MsgBox "You can not insert a row above Number 1", 64, "Invalid"

Exit Sub

End If
Range("A" & ActiveCell.Row).Select
ActiveCell.EntireRow.Insert
ActiveCell.Offset(-1, 0).Select
Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
Selection.FillDown
ActiveCell.Offset(-1, 2).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlDataValidation, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

End Sub



Sandy
Error in vba line
 
Switch xldatavalidation with xlPasteValidation



ADK wrote:
Using Excel 2000:

I get a runtime error '1004' PasteSpecial method of Range class failed

Selection.PasteSpecial Paste:=xlDataValidation, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

Here is code:

Sub addrow()

Dim CellOne As Range

Set CellOne = ActiveCell

If CellOne.Value = 1 Then

MsgBox "You can not insert a row above Number 1", 64, "Invalid"

Exit Sub

End If
Range("A" & ActiveCell.Row).Select
ActiveCell.EntireRow.Insert
ActiveCell.Offset(-1, 0).Select
Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
Selection.FillDown
ActiveCell.Offset(-1, 2).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlDataValidation, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

End Sub

ADK
Error in vba line
 
Not sure what you mean.....so what would the full code line be?


"Sandy" wrote in message
oups.com...
Switch xldatavalidation with xlPasteValidation



ADK wrote:
Using Excel 2000:

I get a runtime error '1004' PasteSpecial method of Range class failed

Selection.PasteSpecial Paste:=xlDataValidation, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

Here is code:

Sub addrow()

Dim CellOne As Range

Set CellOne = ActiveCell

If CellOne.Value = 1 Then

MsgBox "You can not insert a row above Number 1", 64, "Invalid"

Exit Sub

End If
Range("A" & ActiveCell.Row).Select
ActiveCell.EntireRow.Insert
ActiveCell.Offset(-1, 0).Select
Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
Selection.FillDown
ActiveCell.Offset(-1, 2).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlDataValidation, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

End Sub

Sandy
Error in vba line
 
Selection.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False


Sandy wrote:
Switch xldatavalidation with xlPasteValidation



ADK wrote:
Using Excel 2000:

I get a runtime error '1004' PasteSpecial method of Range class failed

Selection.PasteSpecial Paste:=xlDataValidation, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

Here is code:

Sub addrow()

Dim CellOne As Range

Set CellOne = ActiveCell

If CellOne.Value = 1 Then

MsgBox "You can not insert a row above Number 1", 64, "Invalid"

Exit Sub

End If
Range("A" & ActiveCell.Row).Select
ActiveCell.EntireRow.Insert
ActiveCell.Offset(-1, 0).Select
Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
Selection.FillDown
ActiveCell.Offset(-1, 2).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlDataValidation, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

End Sub

ADK
Error in vba line
 
Still doesn't work.......btw, I am using excel 2000


"Sandy" wrote in message
oups.com...
Selection.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False


Sandy wrote:
Switch xldatavalidation with xlPasteValidation



ADK wrote:
Using Excel 2000:

I get a runtime error '1004' PasteSpecial method of Range class failed

Selection.PasteSpecial Paste:=xlDataValidation, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

Here is code:

Sub addrow()

Dim CellOne As Range

Set CellOne = ActiveCell

If CellOne.Value = 1 Then

MsgBox "You can not insert a row above Number 1", 64, "Invalid"

Exit Sub

End If
Range("A" & ActiveCell.Row).Select
ActiveCell.EntireRow.Insert
ActiveCell.Offset(-1, 0).Select
Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
Selection.FillDown
ActiveCell.Offset(-1, 2).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlDataValidation, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

End Sub

Sandy
Error in vba line
 
Here is complete rework of you code. It isn't giving me any errors when
I run it, and since I don't know what you're trying to do with it I
can't tell if it's doing what you want it to do. Anyways, make sure
that if you are copying and pasting this directly form the post that
you are careful about how the code is placed into your editor.
Sometimes the code if it's too long for the post it will wrap it and
finish the string on the next line which will give you an error unless
you use " _". If you have any other questions send me an email.

Sub addrow()
Dim CellOne As Range
Set CellOne = ActiveCell
If CellOne.Value = 1 Then
MsgBox "You can not insert a row above Number 1", 64, "Invalid"
Exit Sub
End If
Range("A" & ActiveCell.Row).Select
ActiveCell.EntireRow.Insert
ActiveCell.Offset(-1, 0).Select
Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
Selection.FillDown
ActiveCell.Offset(-1, 2).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValidation, _ 'continue on
next line
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

Good luck
ADK wrote:
Still doesn't work.......btw, I am using excel 2000


"Sandy" wrote in message
oups.com...
Selection.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False


Sandy wrote:
Switch xldatavalidation with xlPasteValidation



ADK wrote:
Using Excel 2000:

I get a runtime error '1004' PasteSpecial method of Range class failed

Selection.PasteSpecial Paste:=xlDataValidation, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

Here is code:

Sub addrow()

Dim CellOne As Range

Set CellOne = ActiveCell

If CellOne.Value = 1 Then

MsgBox "You can not insert a row above Number 1", 64, "Invalid"

Exit Sub

End If
Range("A" & ActiveCell.Row).Select
ActiveCell.EntireRow.Insert
ActiveCell.Offset(-1, 0).Select
Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
Selection.FillDown
ActiveCell.Offset(-1, 2).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlDataValidation, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

End Sub

Sandy
Error in vba line
 
once you've pasted this remove the " 'continue on next line"

Sandy wrote:
Here is complete rework of you code. It isn't giving me any errors when
I run it, and since I don't know what you're trying to do with it I
can't tell if it's doing what you want it to do. Anyways, make sure
that if you are copying and pasting this directly form the post that
you are careful about how the code is placed into your editor.
Sometimes the code if it's too long for the post it will wrap it and
finish the string on the next line which will give you an error unless
you use " _". If you have any other questions send me an email.

Sub addrow()
Dim CellOne As Range
Set CellOne = ActiveCell
If CellOne.Value = 1 Then
MsgBox "You can not insert a row above Number 1", 64, "Invalid"
Exit Sub
End If
Range("A" & ActiveCell.Row).Select
ActiveCell.EntireRow.Insert
ActiveCell.Offset(-1, 0).Select
Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
Selection.FillDown
ActiveCell.Offset(-1, 2).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValidation, _ 'continue on
next line
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

Good luck
ADK wrote:
Still doesn't work.......btw, I am using excel 2000


"Sandy" wrote in message
oups.com...
Selection.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False


Sandy wrote:
Switch xldatavalidation with xlPasteValidation



ADK wrote:
Using Excel 2000:

I get a runtime error '1004' PasteSpecial method of Range class failed

Selection.PasteSpecial Paste:=xlDataValidation, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

Here is code:

Sub addrow()

Dim CellOne As Range

Set CellOne = ActiveCell

If CellOne.Value = 1 Then

MsgBox "You can not insert a row above Number 1", 64, "Invalid"

Exit Sub

End If
Range("A" & ActiveCell.Row).Select
ActiveCell.EntireRow.Insert
ActiveCell.Offset(-1, 0).Select
Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
Selection.FillDown
ActiveCell.Offset(-1, 2).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlDataValidation, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

End Sub

ADK
Error in vba line
 
thanks

"Sandy" wrote in message
oups.com...
once you've pasted this remove the " 'continue on next line"

Sandy wrote:
Here is complete rework of you code. It isn't giving me any errors when
I run it, and since I don't know what you're trying to do with it I
can't tell if it's doing what you want it to do. Anyways, make sure
that if you are copying and pasting this directly form the post that
you are careful about how the code is placed into your editor.
Sometimes the code if it's too long for the post it will wrap it and
finish the string on the next line which will give you an error unless
you use " _". If you have any other questions send me an email.

Sub addrow()
Dim CellOne As Range
Set CellOne = ActiveCell
If CellOne.Value = 1 Then
MsgBox "You can not insert a row above Number 1", 64, "Invalid"
Exit Sub
End If
Range("A" & ActiveCell.Row).Select
ActiveCell.EntireRow.Insert
ActiveCell.Offset(-1, 0).Select
Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
Selection.FillDown
ActiveCell.Offset(-1, 2).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValidation, _ 'continue on
next line
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

Good luck
ADK wrote:
Still doesn't work.......btw, I am using excel 2000


"Sandy" wrote in message
oups.com...
Selection.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False


Sandy wrote:
Switch xldatavalidation with xlPasteValidation



ADK wrote:
Using Excel 2000:

I get a runtime error '1004' PasteSpecial method of Range class
failed

Selection.PasteSpecial Paste:=xlDataValidation, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

Here is code:

Sub addrow()

Dim CellOne As Range

Set CellOne = ActiveCell

If CellOne.Value = 1 Then

MsgBox "You can not insert a row above Number 1", 64,
"Invalid"

Exit Sub

End If
Range("A" & ActiveCell.Row).Select
ActiveCell.EntireRow.Insert
ActiveCell.Offset(-1, 0).Select
Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
Selection.FillDown
ActiveCell.Offset(-1, 2).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlDataValidation,
Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

End Sub


All times are GMT +1. The time now is 09:50 PM.

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