copy range from each worksheet to seperate workbook
 

I can't seem to get this code to work.
I want to copy a range J12:012 from each worksheet in one workbook, an
paste special (values only) into a DIFFERENT workbook, on the next ro
down from the previous paste...

Sub combine()
'On Error GoTo LASTSHEET
Application.ScreenUpdating = False
Dim Wkbk As Workbook
Dim wksht As Worksheet
Dim destWks As Worksheet
Dim destCell As Range
Dim drow As Integer
Set Wkbk = Workbooks("ajx.xls")
Set destWks = Workbooks("combined.xls").Worksheets("sheet1")
drow = 1
For Each wksht In Wkbk.Worksheets
With destWks
Set destCell = .Cells(drow, 1)
End With
Range("J12:O12").Select
Selection.Copy
destCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
drow = drow + 1
Next
'LASTSHEET:
End Sub

I don't get an error, it just doesn't copy what I want it to. I
copies the blank cells J12:o12 in the destination book, to column A.
So, it runs for a while as it copies nothing to nothing, countin
through the sheets which it ignores... :-

--
Kieran102
-----------------------------------------------------------------------
Kieran1028's Profile: http://www.excelforum.com/member.php...fo&userid=1567
View this thread: http://www.excelforum.com/showthread.php?threadid=27727