• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Run time error '1004'

T

Thomas Kuriakose

Active Member
Respected Sirs,

I tried a macro to copy specific columns from one sheet (Sheet1) to another (Sheet2). I am using the below code -

Code: 
Sub Copy()

    Dim src As Worksheet
    Dim trg As Worksheet
    Dim LastRow As Long


    Set src = ThisWorkbook.Worksheets("Sheet1")
    Set trg = ThisWorkbook.Worksheets("Sheet2")




    src.Range("B4:B2000").Copy Destination:=trg.Range("A5").PasteSpecial
    src.Range("GN4:GN2000").Copy Destination:=trg.Range("B5").PasteSpecial
    src.Range("HD4:HD2000").Copy Destination:=trg.Range("C5").PasteSpecial
    src.Range("HE4:HE2000").Copy Destination:=trg.Range("D5").PasteSpecial
    src.Range("HB4:HB2000").Copy Destination:=trg.Range("E5").PasteSpecial
    src.Range("HC4:HC2000").Copy Destination:=trg.Range("F5").PasteSpecial
    src.Range("DC4:DC2000").Copy Destination:=trg.Range("G5").PasteSpecial
    src.Range("DD4:DD2000").Copy Destination:=trg.Range("H5").PasteSpecial
    src.Range("DG4:DG2000").Copy Destination:=trg.Range("I5").PasteSpecial
    src.Range("EN4:EN2000").Copy Destination:=trg.Range("J5").PasteSpecial
    src.Range("DN4:DN2000").Copy Destination:=trg.Range("K5").PasteSpecial
    src.Range("EU4:EU2000").Copy Destination:=trg.Range("L5").PasteSpecial
    src.Range("DJ4:DJ2000").Copy Destination:=trg.Range("N5").PasteSpecial
   
    Application.CutCopyMode = False
    Sheet2.Columns().AutoFit
    End Sub

The requirement is to paste special values in Sheet2, but I get the below run time error in line
src.Range("B4:B2000").Copy Destination:=trg.Range("A5").PasteSpecial

upload_2018-10-28_21-45-57.png

Kindly guide on how to write the correct code to copy and paste special value in this case.

Thank you very much for you support always,

much appreciated,

with regards,
thomas
 
Respected Sirs,

Thank you very much for your support on this query. I changed the code, but I get the below error -

upload_2018-10-29_8-50-0.png

Kindly guide where I am going wrong,

Thank you very much,

with regards,
thomas
 
For pastespecial, instead of
Code: 
Destination:=YourDestination.pastespecial xlpasteAll
on the same line as the copy, I usually place the Destination on a separate line

Code: 
YourDestination.pastespecial xlPasteAll
 
Thomas Kuriakose
as written:
... modify all those .PasteSpecials away.
Means as below ...
Code: 
    src.Range("B4:B2000").Copy Destination:=trg.Range("A5")
    src.Range("GN4:GN2000").Copy Destination:=trg.Range("B5")
    src.Range("HD4:HD2000").Copy Destination:=trg.Range("C5")
    src.Range("HE4:HE2000").Copy Destination:=trg.Range("D5")
    src.Range("HB4:HB2000").Copy Destination:=trg.Range("E5")
    src.Range("HC4:HC2000").Copy Destination:=trg.Range("F5")
    src.Range("DC4:DC2000").Copy Destination:=trg.Range("G5")
    src.Range("DD4:DD2000").Copy Destination:=trg.Range("H5")
    src.Range("DG4:DG2000").Copy Destination:=trg.Range("I5")
    src.Range("EN4:EN2000").Copy Destination:=trg.Range("J5")
    src.Range("DN4:DN2000").Copy Destination:=trg.Range("K5")
    src.Range("EU4:EU2000").Copy Destination:=trg.Range("L5")
    src.Range("DJ4:DJ2000").Copy Destination:=trg.Range("N5")
Compare to Your original code.
 
Respected Sirs,

Thank you very much once again,

I am still getting this error.

upload_2018-10-29_12-13-11.png

My apologies if I did not get this correct code change,

with regards,
thomas
 
Respected Sir,

Thank you very much for this code and you kind support.

This is copying data, but Sheet1 has two columns with formula lookup from another sheet and when pasting results in #Ref error in Sheet 2.

We need to paste special the values in Sheet2 to get the values.

Thank you very much,

with regards,
thomas
 
Respected Sir,

Thank you very much for your guidance. The below columns HB,HC,HD and HE have lookup formulas.

Code: 
src.Range("HD4:HD2000").Copy Destination:=trg.Range("C5")
  Range("C5").PasteSpecial xlPasteAll
  src.Range("HE4:HE2000").Copy Destination:=trg.Range("D5")
  Range("D5").PasteSpecial xlPasteAll
  src.Range("HB4:HB2000").Copy Destination:=trg.Range("E5")
  Range("E5").PasteSpecial xlPasteAll
  src.Range("HC4:HC2000").Copy Destination:=trg.Range("F5")
  Range("F5").PasteSpecial xlPasteAll

Thank you very much once again,

with regards,
thomas
 
but Sheet1 has two columns with formula lookup ...

Code: 
    src.Range("HD4:HD2000").Copy
    trg.Range("C5").PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False[/SIZE]
    src.Range("HE4:HE2000").Copy
    trg.Range("D5").PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    src.Range("HB4:HB2000").Copy
    trg.Range("E5").PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    src.Range("HC4:HC2000").Copy
    trg.Range("F5").PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
 
I have a similar error 1004, but it is with filtering in a pivot table. Any idea how I can fix it? I've been trying for over a week. I've tried posting the code on here, but it says it contains too many characters to upload. Here is screenshot, I can upload a .txt file as well.
 

Attachments

  • excel pivot filter error.JPG
    excel pivot filter error.JPG
    151 KB · Views: 2
Burnie
Do You have challenge with a .txt-file?
If 'Yes' then txt-file, otherwise Excel-file.
... if only any code then there would be challenge to test any modification.

Your case seems to be a new case, then You should open a new case.
 
Thanks for the response Vletm, I posted the question last Wednesday on here, with the text file and my excel files.
 
You must log in or register to reply here.
Back
Top