Extracting data and not formulas when using VBA

[shakeregg]

Hey all,

Mumps kindly provided the below which enabled me to extract information to a Worksheet called 'Raw Data' from various sources which contained a worksheet named Sheet1. I've developed the sources worksheets in that the cells where the information is pulled through from now contain basic formulas which produces the text.

Is there a way of pulling through the data and not the formula to the Worksheet "Raw Data"..................... any help will be really appreciated!

Sub CopyRange()
Application.ScreenUpdating = False
Dim wkbSource As Workbook, wsDest As Worksheet
Set wsDest = ThisWorkbook.Sheets("Raw Data")
Dim lColumn As Long
lColumn = 2
Const strPath As String = "C:\Users\Laura\OneDrive\Documents\Database"
ChDir strPath
strextension = Dir(strPath & "*.xlsm")
Do While strextension <> ""
If strextension <> ThisWorkbook.Name Then
Set wkbSource = Workbooks.Open(strPath & strextension)
With wkbSource
.Sheets("Sheet1").Range("A4:A52").Copy wsDest.Cells(4, lColumn)
lColumn = lColumn + 1
.Close savechanges:=False
End With
End If
strextension = Dir
Loop
Application.ScreenUpdating = True
End Sub

 

[Fluff]

How about

With wkbSource
   wsDest.Cells(4, lColumn).Resize(49).Value = .Sheets("Sheet1").Range("A4:A52").Value
   lColumn = lColumn + 1
   .Close savechanges:=False
End With

 

[shakeregg]

Works perfectly........................ thank you so much!

 

[Fluff]

You're welcome