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"'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".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
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"'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".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