Hi Guys
I have a macro which I have found and it works really well except I have 2 issues
1 - The code only copies formulas and not just plain values
2 - I cant work out how to make it copy from specific cells
Where it says
lngrow = lngrow + 1
ws.Rows(2).Copy ws1.Cells(lngrow, "A")
I want to change that to copy the values from c65 & c67. I have tried altering the lines to try and copy a range but whatever I do it doesnt work. I am new to VBA and have also been trying to find definitions of VBA commands and codes but those commands do not seem to be in anything I have read. I understand lng is there because VBA recognises row as a command but other than that my knowledge is very limited. Any help on this would be greatly appreciated thank you
The code I am using is:
Sub ConFiles()
Dim Wbname As String
Dim Wb As Workbook
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim lngCalc As Long
Dim lngrow As Long
With Application
.ScreenUpdating = False
.EnableEvents = False
lngCalc = .CalculationState
.Calculation = xlCalculationManual
End With
Set ws1 = ThisWorkbook.Sheets.Add
'change folder path here
FolderName = "C:\Users\James\Dropbox\Excel\Testing\VBA Testing\"
Wbname = Dir(FolderName & "\" & "*.xls*")
'ThisWorkbook.Sheets(1).UsedRange.ClearContents
Do While Len(Wbname) > 0
Set Wb = Workbooks.Open(FolderName & "\" & Wbname)
Set ws = Nothing
On Error Resume Next
'change sheet name here
Set ws = Wb.Sheets("Total Quantities")
On Error GoTo 0
If Not ws Is Nothing Then
lngrow = lngrow + 1
ws.Rows(2).Copy ws1.Cells(lngrow, "A")
End If
Wb.Close False
Wbname = Dir
Loop
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = lngCalc
End With
End Sub
Bookmarks