monkey_arms
New Member
- Joined
- Feb 23, 2020
- Messages
- 3
- Office Version
- 2016
- Platform
- Windows
Hi everyone!
I got some vba code from here to copy the same 2 cells from multiple workbooks and paste them in one workbook. It works, however I need to paste only the values, as the cells I'm copying from have formulas in them. I tried adding some different ".PasteSpecial..." at the end of the copy statement from snippets I found online, but I get a variety of compile errors depending on the snippets used. It seems so close to being done, but my mind is fried. Help!
I got some vba code from here to copy the same 2 cells from multiple workbooks and paste them in one workbook. It works, however I need to paste only the values, as the cells I'm copying from have formulas in them. I tried adding some different ".PasteSpecial..." at the end of the copy statement from snippets I found online, but I get a variety of compile errors depending on the snippets used. It seems so close to being done, but my mind is fried. Help!
VBA Code:
Sub CopyRange()
Application.ScreenUpdating = False
Dim wkbDest As Workbook
Dim wkbSource As Workbook
Set wkbDest = ThisWorkbook
Const strPath As String = "C:\Users\monkey_arms\Desktop\Local Dev\EXCEL\SourceData\"
ChDir strPath
strExtension = Dir("*.xls*")
Do While strExtension <> ""
Set wkbSource = Workbooks.Open(strPath & strExtension)
With wkbSource
.Sheets("PWB").Range("D2").Copy wkbDest.Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
.Sheets("PWB").Range("B6").Copy wkbDest.Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
.Close savechanges:=False
End With
strExtension = Dir
Loop
Application.ScreenUpdating = True
End Sub