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

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

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

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