Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Error in vba line
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
ADK ADK is offline
external usenet poster
  
Posts: 89
Default 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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 156
Default 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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
ADK ADK is offline
external usenet poster
  
Posts: 89
Default 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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 156
Default 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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
ADK ADK is offline
external usenet poster
  
Posts: 89
Default 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



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 156
Default 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


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 156
Default 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


Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
ADK ADK is offline
external usenet poster
  
Posts: 89
Default 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




Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Getting Compile error - on this line Jim May Excel Discussion (Misc queries) 7 May 27th 08 10:39 PM
Line number in error handler cereldine[_36_] Excel Programming 1 June 9th 06 02:27 PM
knowing error line and module name x taol Excel Programming 3 December 28th 05 01:16 AM
Get Module line # for error trapping Geof Wyght[_2_] Excel Programming 5 October 7th 04 01:21 PM
Line continuation error Todd Huttenstine Excel Programming 4 July 29th 04 05:16 AM


All times are GMT +1. The time now is 12:11 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement