Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Error in Copying and pasting data
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 18
Default Error in Copying and pasting data
HI,

I have written following piece of code

Private Sub Workbook_Open()
Dim spr, x, val As Integer
x = 5

Worksheets("Sheet1").Range("C4:I205").Copy
'Worksheets("Sheet2").Range("C4").PasteSpecial Operation:=xlPasteValues, _
SkipBlanks:=True

While (x <= 200)
Debug.Print x
val1 = Worksheets("Sheet2").Range("C" + Str(x + 1)).Value
If IsEmpty(Worksheets("Sheet 2").Range("C" + Str(x))) Then
val = val1
Worksheets("Sheet2").Range("C" + Str(x)) = val
Worksheets("Sheet2").Range("C" + Str(x + 1)) = ""
End If
x = x + 1
Wend

End Sub

==============================

I am continuolsy getting Run time Error '1004' at line
Worksheets("Sheet2").Range("C4").PasteSpecial Operation:=xlPasteValues, _
SkipBlanks:=True
On changing this line to
Worksheets("Sheet2").Paste (Range("C4")), I am still getting the same error.

I commented these two lines and now I am getting the same error(Run tiem
error '1004') at

val1 = Worksheets("Sheet2").Range("C" + Str(x + 1)).Value

I am just not able to understand why am I getting this error all the time !

Please help.
TIA
Shilps



Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default Error in Copying and pasting data
Will this do it?

Sub Test()

Worksheets("Sheet1").Range("C4:I205").Copy
Worksheets("Sheet2").Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=True

End Sub


Best reg.
Erik B

"Shilps" wrote in message
...
HI,

I have written following piece of code

Private Sub Workbook_Open()
Dim spr, x, val As Integer
x = 5

Worksheets("Sheet1").Range("C4:I205").Copy
'Worksheets("Sheet2").Range("C4").PasteSpecial Operation:=xlPasteValues, _
SkipBlanks:=True

While (x <= 200)
Debug.Print x
val1 = Worksheets("Sheet2").Range("C" + Str(x + 1)).Value
If IsEmpty(Worksheets("Sheet 2").Range("C" + Str(x))) Then
val = val1
Worksheets("Sheet2").Range("C" + Str(x)) = val
Worksheets("Sheet2").Range("C" + Str(x + 1)) = ""
End If
x = x + 1
Wend

End Sub

==============================

I am continuolsy getting Run time Error '1004' at line
Worksheets("Sheet2").Range("C4").PasteSpecial Operation:=xlPasteValues, _
SkipBlanks:=True
On changing this line to
Worksheets("Sheet2").Paste (Range("C4")), I am still getting the same
error.

I commented these two lines and now I am getting the same error(Run tiem
error '1004') at

val1 = Worksheets("Sheet2").Range("C" + Str(x + 1)).Value

I am just not able to understand why am I getting this error all the time
!

Please help.
TIA
Shilps





Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 18
Default Error in Copying and pasting data
Thanks. But what about the error at line
val1 = Worksheets("Sheet2").Range("C" + Str(x + 1)).Value.

Also I would like to know why was I still getting the error when I changed
the code to following
'Worksheets("Sheet2").Range("C4").Selection
Selection.PasteSpecial Operation:=xlPasteValues, SkipBlanks:=True

"Erik BZ" wrote:

Will this do it?

Sub Test()

Worksheets("Sheet1").Range("C4:I205").Copy
Worksheets("Sheet2").Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=True

End Sub


Best reg.
Erik B

"Shilps" wrote in message
...
HI,

I have written following piece of code

Private Sub Workbook_Open()
Dim spr, x, val As Integer
x = 5

Worksheets("Sheet1").Range("C4:I205").Copy
'Worksheets("Sheet2").Range("C4").PasteSpecial Operation:=xlPasteValues, _
SkipBlanks:=True

While (x <= 200)
Debug.Print x
val1 = Worksheets("Sheet2").Range("C" + Str(x + 1)).Value
If IsEmpty(Worksheets("Sheet 2").Range("C" + Str(x))) Then
val = val1
Worksheets("Sheet2").Range("C" + Str(x)) = val
Worksheets("Sheet2").Range("C" + Str(x + 1)) = ""
End If
x = x + 1
Wend

End Sub

==============================

I am continuolsy getting Run time Error '1004' at line
Worksheets("Sheet2").Range("C4").PasteSpecial Operation:=xlPasteValues, _
SkipBlanks:=True
On changing this line to
Worksheets("Sheet2").Paste (Range("C4")), I am still getting the same
error.

I commented these two lines and now I am getting the same error(Run tiem
error '1004') at

val1 = Worksheets("Sheet2").Range("C" + Str(x + 1)).Value

I am just not able to understand why am I getting this error all the time
!

Please help.
TIA
Shilps






Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default Error in Copying and pasting data
Hi,

'THIS CODE HAS AN ERROR:
'val1 = Worksheets("Sheet2").Range("C" + Str(x + 1)).Value

'CORRECT_
x = 2 'range("C3")
val1 = Worksheets("Sheet2").Range("C" + CStr(x + 1))

'THIS CODE HAS AN ERROR:
'Worksheets("Sheet2").Range("C4").Selection
'Selection.PasteSpecial Operation:=xlPasteValues, SkipBlanks:=True

'CORRECT:
'(...copy somthing first and then ... )
ActiveWorkbook.Sheets("Sheet2").Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=True

Best reg

Erik B

"Shilps" wrote in message
...
Thanks. But what about the error at line
val1 = Worksheets("Sheet2").Range("C" + Str(x + 1)).Value.

Also I would like to know why was I still getting the error when I changed
the code to following
'Worksheets("Sheet2").Range("C4").Selection
Selection.PasteSpecial Operation:=xlPasteValues, SkipBlanks:=True

"Erik BZ" wrote:

Will this do it?

Sub Test()

Worksheets("Sheet1").Range("C4:I205").Copy
Worksheets("Sheet2").Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=True

End Sub


Best reg.
Erik B

"Shilps" wrote in message
...
HI,

I have written following piece of code

Private Sub Workbook_Open()
Dim spr, x, val As Integer
x = 5

Worksheets("Sheet1").Range("C4:I205").Copy
'Worksheets("Sheet2").Range("C4").PasteSpecial
Operation:=xlPasteValues, _
SkipBlanks:=True

While (x <= 200)
Debug.Print x
val1 = Worksheets("Sheet2").Range("C" + Str(x + 1)).Value
If IsEmpty(Worksheets("Sheet 2").Range("C" + Str(x))) Then
val = val1
Worksheets("Sheet2").Range("C" + Str(x)) = val
Worksheets("Sheet2").Range("C" + Str(x + 1)) = ""
End If
x = x + 1
Wend

End Sub

==============================

I am continuolsy getting Run time Error '1004' at line
Worksheets("Sheet2").Range("C4").PasteSpecial Operation:=xlPasteValues,
_
SkipBlanks:=True
On changing this line to
Worksheets("Sheet2").Paste (Range("C4")), I am still getting the same
error.

I commented these two lines and now I am getting the same error(Run
tiem
error '1004') at

val1 = Worksheets("Sheet2").Range("C" + Str(x + 1)).Value

I am just not able to understand why am I getting this error all the
time
!

Please help.
TIA
Shilps








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
Filtered Mode Copying and pasting as value yield as error? aligahk06 Excel Discussion (Misc queries) 1 March 15th 10 03:30 PM
Copying data from 1 worksheet and pasting to another skipping blan Concertgoer Excel Discussion (Misc queries) 1 October 1st 08 12:30 AM
Copying and pasting tabular text data fk Excel Discussion (Misc queries) 0 July 22nd 08 11:06 PM
Copying and pasting data muiltiple times dalsx1 Excel Discussion (Misc queries) 1 April 1st 06 08:07 AM
Copying and pasting data muiltiple times dalsx1 Excel Discussion (Misc queries) 1 March 31st 06 04:25 PM


All times are GMT +1. The time now is 03:04 PM.

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