none
Run time error 13 - type mismatch RRS feed

  • Question

  • I have a range called HS which is just cells A1:D1 containing the numbers 1,11,21 and 31

    I wanted to copy this range to worksheet 2 

    here is my code:

    Sub Copy()
    Dim sourceRange As Range
    Dim sourceRowRangeVariant As Variant
    Dim rangeFilledWithTransposedData As Range
    
    'HS  = a1: d1
    Set sourceRange = Range("HS")
    sourceRowRangeVariant = sourceRange.Value2
    Set rangeFilledWithTransposedData = Worksheets(Sheet1).Range("A1:D1")  'ERROR
    rangeFilledWithTransposedData.Value = sourceRowRangeVariant
    
    End Sub

    why does this error appear?

    More importantly however, what is the best way to try and copy this range over to worksheet 2?

    Thanks

    Thursday, December 22, 2016 5:12 PM

Answers

  • Because Sheet1 is not a variable with a String or Long value (that is what the Worksheets collection expect), it's a WorkSheet object:

    Set rangeFilledWithTransposedData = Worksheets(Sheet1).Range("A1:D1")  'ERROR

    Andreas.

    • Marked as answer by VBNovice01 Wednesday, December 28, 2016 1:20 PM
    Thursday, December 22, 2016 7:55 PM
  • Hi VBNovice01,

    This would be the easiest.

    Worksheets("Sheet2").Range("A1:D1").Value = _

    Worksheets("Sheet1").Range("A1:D1").Value

    Regards,
    Ashidacchi

    • Marked as answer by VBNovice01 Wednesday, December 28, 2016 1:20 PM
    Friday, December 23, 2016 12:39 AM

All replies

  • The (best?) way to do:

    Sub CopyRange()
        Worksheets("Sheet1").Range("A1:D1") = Range("HS").Value
    End Sub

    Jan

    Thursday, December 22, 2016 6:42 PM
  • why is the error appearing?
    Thursday, December 22, 2016 7:50 PM
  • Because Sheet1 is not a variable with a String or Long value (that is what the Worksheets collection expect), it's a WorkSheet object:

    Set rangeFilledWithTransposedData = Worksheets(Sheet1).Range("A1:D1")  'ERROR

    Andreas.

    • Marked as answer by VBNovice01 Wednesday, December 28, 2016 1:20 PM
    Thursday, December 22, 2016 7:55 PM
  • Hi VBNovice01,

    This would be the easiest.

    Worksheets("Sheet2").Range("A1:D1").Value = _

    Worksheets("Sheet1").Range("A1:D1").Value

    Regards,
    Ashidacchi

    • Marked as answer by VBNovice01 Wednesday, December 28, 2016 1:20 PM
    Friday, December 23, 2016 12:39 AM