Problem in copying cells from another workbook.

[yemnirmal]

Hi all,

Kindly help me with the following error.

I am trying to copy one value from sh1 and paste it in a common sheet (sh2) for every value in sh2.I am repeating the process for all the value in sh1. Sh1,sh2,sh3 are defined as worksheets and open.


"k = sh1.Range("A1", sh1.Range("A1").End(xlDown)).Rows.Count

l = sh2.Range("J1", sh2.Range("J1").End(xlDown)).Rows.Count


For i = 2 To k

For j = 2 To l




sh1.Cells(i, 1).Copy Destination:=sh3.Cells(x, 1).Paste 'won,k,i
sh2.Cells(j, 1).Copy Destination:=sh3.Cells(x, 2).Paste 'month,j,l
x = x + 1

Next j

Next i"


But it is throwing a Runtime error:object defined or application defined error.

Kindly help me to find out whats wrong.

[JOHN H. DAVIS]

One thing - x is not defined. Is there more too the code? Where are you're other variable defined?

[yemnirmal]

I have defined

"
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim sh3 As Worksheet
Dim x As Integer, i As Integer, j As Integer

Set sh1 = Workbooks(Master).Sheets("Sheet1")
Set sh2 = ThisWorkbook.Sheets("Sheet2")
Set sh3 = ThisWorkbook.Sheets("Sheet1")
"

Should i define anything else?
John H.davis

[yemnirmal]

I have defined k and l also as integer

[yemnirmal]

I am getting "object doesnot support this property or method " after defining values....

[JOHN H. DAVIS]

Try defining x, i, & k as long. BTW x needs a beginning value outside of the loops.

[yemnirmal]

The complete code looks like this

" Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim sh3 As Worksheet
Dim x As Long, i As Long, j As Integer, k As Long, l As Integer

Set sh1 = Workbooks(Master).Sheets("Sheet1")
Set sh2 = ThisWorkbook.Sheets("Sheet2")
Set sh3 = ThisWorkbook.Sheets("Sheet1")

k = sh1.Range("A1", sh1.Range("A1").End(xlDown)).Rows.Count

l = sh2.Range("J1", sh2.Range("J1").End(xlDown)).Rows.Count

x = 2

For i = 2 To k

For j = 2 To l

sh1.Cells(i, 1).Copy Destination:=sh3.Cells(x, 1).Paste
sh2.Cells(j, 1).Copy Destination:=sh3.Cells(x, 2).Paste
x = x + 1

Next j

Next i"


Complete code looks like this and i get an error at


" sh1.Cells(i, 1).Copy Destination:=sh3.Cells(x, 1).Paste
sh2.Cells(j, 1).Copy Destination:=sh3.Cells(x, 2).Paste "

Error: Object doesnt support this property or method
. Kindly help....

[JOHN H. DAVIS]

OK, I think this

Please Login or Register  to view this content.

Should be

Please Login or Register  to view this content.

[yemnirmal]

Even thats not working can you give me someother code to copy from sh1(another workbook) to sh3.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.