0

I've edited my original question because my Source cells contain Hyperlinks which I need copied over to the Target cells, so I just realized that solutions using .value and .resize won't work for me.

Using VBA I need to copy a range of cells from one sheet to another in the same workbook using the Cells(R,C) method (instead of Range("A1:A10") method) because my Rows/Columns are variables that I'm detecting with code. Therefore I cannot hard code the Row numbers as the last row will vary.

Originally I was pursuing using the .value property instead of the .value property but that was before I realized that my Hyperlinks would not get copied over.

I learned from the responses here that the Cells(r,c) reference assumes the sheet is the ActiveSheet (whereas Range() doesn't) and since both the Source Sheet & Target Sheet cannot both be ActiveSheet at the same time, this creates a problem.

This code was suggested in a response to my original question but unfortunately this created a 400 runtime error.

Sub CopyCells()
  Const TargetSheet = "Sheet1"
  Const SourceSheet = "Sheet2"
  'Goal is to copy the cells Sheets("Sheet2").Range("B10:B19") to Sheets("Sheet1").Range("F1:F10")
  'Below line of code gives 400 error
  Sheets(SourceSheet).Range(Cells(10, 2), Cells(19, 2)).Copy (Sheets(TargetSheet).Cells(1, 6))
End Sub

Can someone let me know what I'm doing wrong?

Thrashercharged
  • 3
  • 3
  • maybe `Worksheets(Sheet1Name).Range(Cells(100, 1), Cells(110, 1)).Value = Range(Worksheets(Sheet2Name).Cells(2, 1), Worksheets(Sheet2Name).Cells(12, 1)).Value`. If your Sheet2Name is active, this kind of line will throw you an error `Worksheets(Sheet1Name).Range(Cells(1000, 1000), Cells(1100, 1000)).Value = "test"`, so it should be : `range(Worksheets(Sheet1Name).Cells(1000, 1000), Worksheets(Sheet1Name).Cells(1100, 1000)).Value = "test"` to work while Sheet2Name active. – karma Apr 15 '23 at 08:42
  • In short, your unqualified `Cells` references do not automatically assume you mean the same sheet from the beginning of the line. They assume `ActiveSheet` instead, causing the problem. – braX Apr 15 '23 at 09:00
  • Thanks for the responses. I tried the suggested change and I still get a 400 error. I'm going to rephrase my question and edit it for clarity. – Thrashercharged Apr 16 '23 at 13:52
  • 1
    You have `Cells()` without a worksheet reference so it will default to the Activesheet which it it isn't SourceSheet will error. Use a With-EndWith block eg `With Sheets(SourceSheet): .Range(.Cells(10, 1), .Cells(20, 1)).Copy Sheets(TargetSheet).Cells(1, 1): End With` The dot on `.Cells()` associates it with the SourceSheet. – CDP1802 Apr 16 '23 at 14:33
  • On top of fixing the unqualified `Cells`, you should also remove the parentheses around the `(Sheets(TargetSheet).Cells(1, 6))`. Otherwise you are trying to copy into `Sheets(TargetSheet).Cells(1, 6).Value` instead of the `Sheets(TargetSheet).Cells(1, 6)`. – GSerg Apr 17 '23 at 17:46

2 Answers2

0

Calculate the number of rows and use .Resize

Option Explicit

Sub CopyValues()

      Const TargetSheet = "Sheet1"
      Const SourceSheet = "Sheet2"
      
      Dim wsSource As Worksheet, wsTarget As Worksheet
      With ThisWorkbook
         Set wsTarget = .Sheets(TargetSheet)
         Set wsSource = .Sheets(SourceSheet)
      End With
    
      Dim r1 As Long, r2 As Long, n As Long
      r1 = 10
      r2 = 20
      n = r2 - r1 + 1
      wsSource.Cells(r1, 1).Resize(n).Copy wsTarget.Cells(1, 1)
  
 End Sub
CDP1802
  • 13,871
  • 2
  • 7
  • 17
  • This answer using .Resize does not give 400 errors, but unfortunately I really do need to copy the cells and not just set the values to be equal because my source cells contain Hyperlinks that I need copied to the target cells, so I really do need a solution that copies using the clipboard. – Thrashercharged Apr 17 '23 at 12:47
-1

You can use the Range.Copy method instead of =

Worksheets(Sheet1Name).Range(Cells(100, 1), Cells(110, 1)).Value = Worksheets(Sheet2Name).Range(Cells(2, 1), Cells(12, 1)).Value

can become

Sheets(sheet1Name).Range(Cells(100, 1), Cells(110, 1)).Copy (Sheets(sheet2Name).Cells(2, 1)

Unfortunately I'm pretty new to using VBA so I'm not sure about the syntax when using the = sign...
Hope my answer works well enough for what you need :)

Ertersy
  • 41
  • 5
  • It is almost never preferable to use the .copy method as opposed to the .value property. Using .copy is slower, and uses the clipboard. – Jim Becker Apr 16 '23 at 13:36
  • Thanks for the suggestion, but that code also gives a 400 error. Note I think you left an ending ) off the end of the line but after fixing that syntex I still get a 400 code. – Thrashercharged Apr 16 '23 at 13:37
  • While I agree with Jim Becker in principle, my source cells contain Hyperlinks which I need copied over to the target cells in another sheet and Ertersy's .copy method still gives me a 400 code. Could someone provide a .copy method that works? – Thrashercharged Apr 17 '23 at 12:49