PDA

View Full Version : Code; Why Will it not work on any other sheet?



luke1438
11-11-2007, 10:58 AM
I have the following code:

Sub Macro_1()
Dim iCol As Long
With Worksheets("Sheet 2")
iCol = Application.Match(.Range("C7").Value, _
Worksheets("Sheet 3").Rows(6), 0)
Worksheets("Sheet 1").Range("B10:B100").Copy
Worksheets("Sheet 3").Cells(10, iCol).Select
Selection.PasteSpecial Paste:=xlPasteValues
End With
End Sub

Why will it work from "Sheet 3" and no other sheet? I have tried everything. What am I missing?

Thanks
Luke

Bob Phillips
11-11-2007, 11:38 AM
Because YOU are specifically telling it where to get data from, copy it to, in terms of the sheets.

Which bit do you not want to use Sheet3? Then change that bit.

Bob Phillips
11-11-2007, 11:42 AM
this bit is also suspect



Worksheets("Sheet 1").Range("B10:B100").Copy
Worksheets("Sheet 3").Cells(10, iCol).Select
Selection.PasteSpecial Paste:=xlPasteValues


try


Worksheets("Sheet 1").Range("B10:B100").Copy _
Worksheets("Sheet 3").Cells(10, iCol)
Worksheets("Sheet 3").Cells(10, iCol).Value = Worksheets("Sheet 3").Cells(10, iCol).Value