CODEWITHSUNDEEP
vbaexcel
yinka
yinka

Reputation: 134

Copying excel row from one sheet to another

I am copying data from one sheet to another, from source range(Q5:AIxxx) to destination range(C6:Uxxx)

My VBA code starts with a cell and loops to the end of the column to finish copying that column's data: 

Set s = Worksheets("Source")
Set d = Worksheets("Destination")

Dim i As Integer, j As Integer

j = 6
For i = 5 To 1500
   If s.Cells(i, 1).Value = "a" Or s.Cells(i, 1).Value = "b" Then
      d.Cells(j, 3).Value = s.Cells(i, 17).Value       
      j = j + 1
   End If
Next i

I have > 20 columns to move, is there a way I can copy the row at once? something like this: 

d.Cells(j, 3:21).Value = s.Cells(i, 17:35).Value

At the moment, I'm having to specify each column:

 d.Cells(j, 3).Value = s.Cells(i, 17).Value    'column 1
 d.Cells(j, 4).Value = s.Cells(i, 18).Value    'column 2
 d.Cells(j, 5).Value = s.Cells(i, 19).Value    'column 3
 etc

Upvotes: 1

Views: 322

Answers (3)

Slai
Slai

Reputation: 22896

Using ranges instead can make it a bit easier:

Dim s As Range, d As Range
Set d = Worksheets("Destination").Cells(6, 15) ' notice the column 15

For Each s in Worksheets("Source").Range("A5:A1500")
   If s = "a" Or s = "b" Then
      d(,3) = s(,3)
      d(,4) = s(,4)
      ' or d(,3).Resize(,19) = s(,3).Resize(,19) ' .Value optional
      Set d = d.Offset(1) ' move to next row
   End If
Next

There are easier ways to do that without VBA. For example Power Query, PivotTable, Copy Link and Table Filter, Data tab > From Access, etc.

Upvotes: 1

ali srn
ali srn

Reputation: 573

You can do it by 

    d.range(d.cells(j,3), d.cells(j,21)).copy
 s.range(s.cells(i,17), s.cells(i,35)).pastespecial xlvalues

Upvotes: 1

Blenikos
Blenikos

Reputation: 743

You can use: 

.Range(<your_range>).copy

For example do:

Activesheet.Range("A1:B10").Copy

and thenjust paste anywhere you want with .Paste

ActiveSheet.Paste Destination:=Worksheets(2).Range("D1:D5")

It's a build in function in vba. See here also for copying Range:

https://msdn.microsoft.com/en-us/library/office/ff837760.aspx

And for Paste: https://msdn.microsoft.com/en-us/library/office/ff821951.aspx

Upvotes: 1

Related Questions