0

I wrote a sub to copy one range to another, but it results in error '1004' no matter what I try.

I also Googled for similar problems and searched in stackOverflow, to no avail. quite frustrating.

Hope someone can help me. Here is the code.

Sub CopyColData(rSource As Range, rDest As Range)
    ' copy 'rSource' to 'rDest'
    ' NOTE: will force 'rDest' to fit the size of 'rSource'
    Set rDest = rDest.Resize(rSource.Rows.Count, rSource.Columns.Count)
    rSource.Copy (rDest)
End Sub
Sub CopyColDataTEST()
    Sheets.Add.name = "sheet1"
    Range("A1").Value = "1AA"
    Range("A2").Value = "1BB"
    Range("A3").Value = "1CC"
    Range("A4").Value = "1DD"
    Sheets.Add.name = "sheet2"
    CopyColData Worksheets("sheet1").Range("A1:A4"), Worksheets("sheet2").Range("B1")
End Sub

I also tried to copy sheet1 to sheet1, and got the same error.

braX
  • 11,506
  • 5
  • 20
  • 33
Yuan Liu
  • 11
  • 2
  • Sheets.Add.Name = "sheet1" attempts to add a new name for a 'defined range', and it fails because the name sheet1 is internally used to represent the first worksheet. What did you intend to achieve with that first line of code? Add a new sheet? and call it ???? – tinazmu Feb 09 '22 at 02:37
  • You're getting bit by parentheses: `rSource.Copy rDest`. Also you can simplify `CopyColData` to just `rSource.Copy rDest.Cells(1)`, no need to `Resize`. – BigBen Feb 09 '22 at 03:30
  • Alternately: `rSource.Copy Destination:=rDest`. – BigBen Feb 09 '22 at 03:43
  • In a nutshell, the procedure is useless. You don't need a procedure to do `Worksheets("sheet1").Range("A1:A4").Copy Worksheets("sheet2").Range("B1")`. **Unless** you want to copy only values most efficiently (fastest) when you would replace `rSource.Copy (rDest)` with `rDest.Value = rSource.Value`. You could then rename the procedure more appropriately to e.g. `CopyRangeValues`. – VBasic2008 Feb 09 '22 at 04:21
  • 2
    Thank you all! 1. userMT, Sheets.Add.Name actually adds a new worksheet with designated name. And, I am using Traditional Chinese version, so there is no name conflict. 2. BigBen, yes, rSource.Copy rDest does work. It also works with the Destination argument. I used Resize because of a failed previous attempt. 3. VBasic2008, rDest.Value = rSource.Value works like a charm, and I will use this solution. Thanks for the suggested procedure name. It was chosen for a background reason not evident here. Again, THANK YOU ALL! – Yuan Liu Feb 09 '22 at 06:10

0 Answers0