I'm having a little trouble with the following program. The program collects a fixed range from several sheets in a workbook, copies all of these ranges and pastes them one after another on one worksheet in a new (Destination) workbook. The problem is that all of the cells within a range contain fomulas. I want to paste the values only. I am aware of the pastespecial functions but every time i try to modify it i get a error saying out of range. I will show you the program which works but copies the formulas and the values to the destination. I would be very greatful if anyone there could modify the code so that the destination workbook shows only the values.
Regards Carl
------------------------------------------------------------
Sub PierCarl()
Dim SheetNames() As String
Dim SheetCount As Integer
Dim i As Integer
SheetCount = ActiveWorkbook.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) = ActiveWorkbook.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)
For i = 2 To SheetCount
'these are the 2 lines where im having the most grief!
ActiveWorkbook.Sheets(SheetNames(i)).Range("F23:S39".Copy _
Destination:=Worksheets("Sheet1".[A65336].End(xlUp).Offset(0, 0)
Next i
End Sub
------------------------------------------------------------
Sub GetAllWorkSheets()
'Gets all worksheets from selected files and adds
'them to the activeworkbook
Dim varFile As Variant
Dim strFile As Variant
Dim shtData As Variant
Dim wkbData As Workbook
Dim wkbCurrent As Workbook
Dim i As Integer
'set refernce to current workbook
Set wkbCurrent = ActiveWorkbook
'display open file dialog box to user
varFile = Application.GetOpenFilename(MultiSelect:=True)
i = 1
'loop through all files selected
For Each strFile In varFile
Set wkbData = Workbooks.Open(strFile)
'loop through all sheets in file
For Each shtData In ActiveWorkbook.Sheets
'copy sheet to orginal workbook
shtData.Copy After:=wkbCurrent.Sheets(wkbCurrent.Sheets.Count)
Next
'close file
wkbData.Close SaveChanges:=False
Next
End Sub
------------------------------------------------------------
Regards Carl
------------------------------------------------------------
Sub PierCarl()
Dim SheetNames() As String
Dim SheetCount As Integer
Dim i As Integer
SheetCount = ActiveWorkbook.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) = ActiveWorkbook.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)
For i = 2 To SheetCount
'these are the 2 lines where im having the most grief!
ActiveWorkbook.Sheets(SheetNames(i)).Range("F23:S39".Copy _
Destination:=Worksheets("Sheet1".[A65336].End(xlUp).Offset(0, 0)
Next i
End Sub
------------------------------------------------------------
Sub GetAllWorkSheets()
'Gets all worksheets from selected files and adds
'them to the activeworkbook
Dim varFile As Variant
Dim strFile As Variant
Dim shtData As Variant
Dim wkbData As Workbook
Dim wkbCurrent As Workbook
Dim i As Integer
'set refernce to current workbook
Set wkbCurrent = ActiveWorkbook
'display open file dialog box to user
varFile = Application.GetOpenFilename(MultiSelect:=True)
i = 1
'loop through all files selected
For Each strFile In varFile
Set wkbData = Workbooks.Open(strFile)
'loop through all sheets in file
For Each shtData In ActiveWorkbook.Sheets
'copy sheet to orginal workbook
shtData.Copy After:=wkbCurrent.Sheets(wkbCurrent.Sheets.Count)
Next
'close file
wkbData.Close SaveChanges:=False
Next
End Sub
------------------------------------------------------------