CODEWITHSUNDEEP
excelloopsrangewith-statementvba
Tyler
Tyler

Reputation: 616

Set range only produces first row of data

I'm carrying out the function of looping through a folder and collecting a range of data accordingly from each workbook. My code is able to run but why is it only copy the first row of range selected.

Range to be copied: I80:I83. Only copy I80

enter image description here

Code is below

Dim lastRow As Range, rng1 As Range, lastrow2 As Range, rng2 As Range

With ThisWorkbook.Sheets(1)
Set rng1 = Worksheets(2).Range("I80:I83")
Set rng2 = Worksheets(2).Range("I1")

Set lastRow = .Range("B" & .Rows.Count).End(xlUp)
lastRow.Offset(1, 0).Resize(rng1.Columns.Count, rng1.Rows.Count) = rng1.Value


Set lastrow2 = ThisWorkbook.Worksheets("Sheet1").Cells(ThisWorkbook.Worksheets("Sheet1").Rows.Count, "A").End(xlUp)
lastrow2.Offset(1, 0).Resize(rng2.Rows.Count, rng2.Columns.Count) = rng2.Value
End With

Output of code enter image description here

Thanks in advance (:

Upvotes: 1

Views: 55

Answers (1)

user4039065
user4039065

Reputation:

Your row and column resize is backwards.

lastRow.Offset(1, 0).Resize(rng1.Rows.Count, rng1.Columns.Count) = rng1.Value

If your intention was to write 4 rows of data into 4 columns then you need to transpose the array.

lastRow.Offset(1, 0).Resize(rng1.Columns.Count, rng1.Rows.Count) = application.transpose(rng1.Value)

You also might want to put multiple values into column A.

lastrow2.Offset(1, 0).Resize(rng1.Rows.Count, rng1.Columns.Count) = rng2.Value

Upvotes: 1

Related Questions