CODEWITHSUNDEEP
excelvba
hoghog
hoghog

Reputation: 1

macro range of object global failed runtime error 1004 - copy selected cells

i am trying to copy selected cells rows , together with the header over to another cell. however, the most i can copy is up to 4 rows, else i will receive the range of object global failed error message. may i know why i am unable to select 5 rows and above? thank you in advance.

Sub CopyPaste()
Dim NumRowSelected As Integer
Dim i As Integer
Dim currentCell As Range
Dim bottomCell As Range
Dim ToSelect As Range
Dim k As Integer
Dim selectedString As String


Windows("Book1.xlsx").Activate
Sheets("working").Select
NumRowSelected = Selection.Rows.Count
selectedString = "A1,B1,C1,D1,E1,F1,G1,H1,I1,J1,K1,L1,M1,N1,O1"

k = 2
i = 0
Set currentCell = Range("A2")
Set bottomCell = Range("A2").End(xlDown)
    
Do While k <= bottomCell.Row
    
    For Each cell In Selection
        If currentCell = cell Then
        selectedString = selectedString & ",A" & k & ",B" & k & ",C" & k & ",D" & k & ",E" & k & ",F" & k & ",G" & k & ",H" & k & ",I" & k & ",J" & k & ",K" & k & ",L" & k & ",M" & k & ",N" & k & ",O" & k
            
            i = i + 1
            If i = NumRowSelected Then
                Exit Do
            End If
            Exit For
        End If
    Next cell
    k = k + 1
    Set currentCell = Range("A" & k)
Loop

Set a = Range(selectedString)'error code shows here
a.Select
Range("A1").Activate
Selection.Copy
Range("Q1").Select
ActiveSheet.Paste
Selection.Copy
End Sub

Upvotes: 0

Views: 37

Answers (1)

Rory
Rory

Reputation: 34045

The address you pass to the Range property is limited to 255 characters, which you will easily bypass with your method. You can condense it quite a lot since your cells are contiguous within a row by using:

selectedString = selectedString & ",A" & k & ":O" & k

and start with:

selectedString = "A1:O1"

but it would be safer to use a Range object with Union:

If a is Nothing then
Set a = Range("A" & k).Resize(1, 15)
else
set a = Union(a, Range("A" & k).Resize(1, 15))
end if

Upvotes: 1

Related Questions