Run-Time error '1004':

[geoub]

Hi,

I am trying to copy a value from one cell to another cell in another worksheet and get this error.
application-defined or object-defined error

Here is my code: (basically I create a macro and made a few changes to it)
Sub CopyAndPaste() 'copy and paste service code number
Sheets("data").Select
Range("A1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
Sheets("CopyPaste").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(3, 0).Select
Do
Sheets("Data").Select
Selection.Copy
ActiveCell.Offset(1, 0).Select

Sheets("CopyPaste").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(3, 0).Select
Application.CutCopyMode = False
Sheets("data").Select
Loop Until IsEmpty(ActiveCell)

End Sub

Any help would be appreciated.

Thanks!

 

[MrKowz]

What line is it erroring on?

Also, what are you wanting to do with this code?

 

[geoub]

Sorry

Its the second line:
Range("A1").select

I am trying to copy a cell from worksheet "data" and paste it into worksheet "copypaste".

thanks

 

[MrKowz]

Given I followed your code properly, try (btw, welcome to the forums!)

Sub CopyAndPaste() 'copy and paste service code number
Dim i       As Long, _
    LR      As Long, _
    rowx    As Long
 
rowx = 4
LR = Sheets("Data").Range("A" & rows.Count).End(xlUp).row
Application.ScreenUpdating = False
For i = 1 To LR
    Sheets("Data").Range("A" & i).Copy
    Sheets("CopyPaste").Range("C" & rowx).PasteSpecial Paste:=xlPasteValues
    rowx = rowx + 3
Next i
Application.ScreenUpdating = True
End Sub

 

[geoub]

works great, thanks a lot!

couple of questions:
1. why was I getting the above error?
2. how can I tweak your code to add more cells? (I copy and pasted the code multiples times for every cell I wanted to do it for)

 

[geoub]

nevermind I figured it out.

thanks again!

 

[MrKowz]

Honestly, I have no idea why it was giving you an error on that line.

As far as including cells, it all depends what cells you want to include and where you want them pasted to. If they are contiguous, that makes reworking the code incredibly easy. However, if they are noncontiguous, it might requires a bit of recoding.

 

[Johnny C]

Might be because you did 'sheets().select', sometimes it needs 'sheets().activate', or sometimes if likes an activeworkbook there too if there's a select.