copy range from each worksheet to seperate workbook

K

Kieran1028

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:eek:12 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... :-
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top