pastespecial problems

[langer1]

I have a workbook with 15 worksheets and i am copying a fixed range one after the other to a new workbook.I only wish to copy the values only as cell within the range
contain formulas. The code below keeps giving me run time errors mainly "type mismatch" . i would be very greatful if anyone would have any suggestions on the code below. The error occurs at the pastespecial line.
regards
Carl



Sub cpyandpste()

Dim SheetNames() As String
Dim SheetCount As Integer
Dim i As Integer
Dim wbk As Workbook
Dim wsh As Worksheet
Dim rngDest As Range
Dim rngCopy As Range
Dim fDoneFirst As Boolean
fDoneFirst = False

Set wbk = ThisWorkbook



Set wsh = Worksheets("LK008&quot'1st sht to cpy range from
SheetCount = wbk.Sheets.Count 'count the number of sheets
ReDim SheetNames(1 To SheetCount)

'Put a value to each sheets

For i = 2 To SheetCount
SheetNames(i) = wbk.Sheets(i).Name
Next i

'Paste each values one after the other for every sheets in sheet1
'The offset set the start to put values (row,column)

Application.ScreenUpdating = False
For i = 2 To SheetCount

ActiveWorkbook.Sheets(SheetNames(i)).Range("F23:S39&quot.Copy

Set rngDest = Sheets(1).Cells(1, 1)
'Its the line below where i get the problem

rngDest.PasteSpecial ([paste as XlPasteType= xlPasteValues,transpose =true])

'reset destination range to one cell below currentregion
Set rngDest = rngDest.End(xlDown).Offset(1, 0)

Next i
Application.ScreenUpdating = True

End Sub

 

[Rissolo]

Hi Carl,
The problem isn't in your definition of the pastespecial line. Since you are switching between workbooks, you need to activate the destination workbook before attempting to paste the cell values into the defined range.

You may wish to define a Workbook variable for the destination workbook (wbkDestWbk), and then, after you have copied from the source workbook, use "wbkDestWbk.activate"

Since you have this in a loop, you will also have to put in a line to activate the source workbook before you copy the data. The code won't do anything if you are activating an active workbook on the first pass, but after you paste your data, the destination workbook will still be active and if you don't change the focus to the source workbook, you will get another error.

Let me know if this solves your problem.

Rissolo

 

[langer1]

Thanks Rissolo for replying
i understand about activating the destination workbook but i still get run time error of type mismatch. I have tried to define a workbook variable for source and destination a few times but i could not come up with suitable code. I was wondering if you would have any idea of how i could modify the code so that this method would be possible. The main reason i found this method difficult was that i have to copy from multiple workbooks but the range to be copyied stays the same and there is only one workbook to be pasted to. The code above only deals with copying from one workbook to another but i was trying to fix one problem at a time.
if you have any ideas i would be very greatful,

Regards Carl

 

[Rissolo]

Looking further into your code, I am a little confused as to what you are trying to do.

I think you are trying to copy from multiple worksheets in a source workbook and transfer (and transpose) that data into consecutive areas on the same worksheet in a compilation workbook. After you have transferred the data, you want to find the row beyond the last place you pasted for your next paste operation.

Is this correct?

 

[langer1]

Sorry Rissolo for not making my question clearer. What you have just said is correct.
I have one source workbook containing multiple worksheets and a fixed range (F23:S30)from each worksheet is to be pasted values only to a destination workbook containing one worksheet and the ranges are to be pasted one after the other on this sheet starting with cells(1,1).
If you can spot any faults with the code or give me some advice on how to correctly activate the workbooks within the code I would be very greatful
Thanks again Carl

 

[SkipVought]

Hi,

Tyr this...

Sub CpyAndPaste()
    Dim rngDest As Range, wsh As Worksheet, ws As Worksheet, NextRow As Long
    Set wsh = Worksheets("LK008") '1st sht to cpy range from
    Application.ScreenUpdating = False
    For Each ws In activesorkbook.Worksheets
        If ws.Name <> wsh.Name Then
            If wsh.Cells(1, 1).CurrentRegion.Rows.Count = 1 Then
                NextRow = 2
            Else
                NextRow = wsh.Cells(1, 1).End(xlDown).Offset(1, 0).Row
            End If
            Set rngDest = wsh.Cells(NextRow, 1)
            ws.Range(&quot;F23:S39&quot;).Copy
            rngDest.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, False, True
        End If
    Next
    Application.ScreenUpdating = True
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com