CODEWITHSUNDEEP
vbaexcel
Yash agarwal
Yash agarwal

Reputation: 1

Copy specific columns from one sheet to another using reference of first cell of column

I am trying to copy columns of ws12 sheet in ws22 sheet based on the columns headings in ws22 sheet. 

Sub secondprogram()

     Dim i As Integer
     Dim j As Integer
     For i = 1 To 3
         MsgBox i
         For j = 1 To 3
                     If Worksheets("ws22").Cells(1, i).Value = Worksheets("ws12").Cells(1, j).Value Then
                    'Worksheets("ws12").Range(Cells(2, j), Cells(10000, j)).Copy Destination:=Worksheets("ws22").Range(Cells(2, i), Cells(10000, i))
                     Worksheets("ws22").Range(Cells(2, i), Cells(10000, i)).Value = Worksheets("ws12").Range(Cells(2, j), Cells(10000, j)).Value
              End If
         Next j
     Next i
End Sub

When I am using msgbox, right values are coming. but code is showing error run time error '1004' Application defined or object defined error in the both the lines 

 Worksheets("ws12").Range(Cells(2, j), Cells(10000, j)).Copy _
     Destination:=Worksheets("ws22").Range(Cells(2, i), Cells(10000, i))

 Worksheets("ws22").Range(Cells(2, i), Cells(10000, i)).Value = _
     Worksheets("ws12").Range(Cells(2, j), Cells(10000, j)).Value

Upvotes: 0

Views: 51

Answers (2)

Tim Williams
Tim Williams

Reputation: 166366

In a regular module or in ThisWorkBook, every use of Range() or Cells() which is not qualified with a worksheet will default to the ActiveSheet, so it's good practice to make sure you always qualify them.

So in the line below, even though you qualify Range(), the enclosed Cells() will each refer to the activesheet.

Worksheets("ws22").Range(Cells(2, i), Cells(10000, i)).Value = _
     Worksheets("ws12").Range(Cells(2, j), Cells(10000, j)).Value

To fix the problem you need to be more explicit:

Dim ws22 As WorkSheet
Dim ws12 As worksheet

Set ws22 = Worksheets("ws22")
Set ws12 = Worksheets("ws12")

'....

ws22.Range(ws22.Cells(2, i), ws22.Cells(10000, i)).Value = _
     ws12.Range(ws12.Cells(2, j), ws12.Cells(10000, j)).Value

Upvotes: 1

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

With that kind of reference to range object you need to repeat sheet reference. Try with this:

 Worksheets("ws12").Range(Worksheets("ws12").Cells(2, j), Worksheets("ws12").Cells(10000, j)).Copy _
     Destination:=Worksheets("ws22").Range(Worksheets("ws22").Cells(2, i), Worksheets("ws22").Cells(10000, i))

or a bit shorter:

With  Worksheets("ws12")
.Range(.Cells(2, j), .Cells(10000, j)).Copy _
     Destination:=Worksheets("ws22").Range(Worksheets("ws22").Cells(2, i), Worksheets("ws22").Cells(10000, i))
End with

Upvotes: 0

Related Questions