Paste Special Not Working This Time

[monkey_arms]

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!

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

 

[Michael M]

Haven't got Excel at the moment, so UNTESTED !!

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
With wkbDest
    .Cells(Rows.Count, "A").End(xlUp).Value = .Cells(Rows.Count, "A").End(xlUp).Value
    .Cells(Rows.Count, "B").End(xlUp).Value = .Cells(Rows.Count, "B").End(xlUp).Value
End With
Application.ScreenUpdating = True
End Sub

 

[monkey_arms]

Hi Michael,

Thanks for replying. Unfortunately, that didn't work. The formulas and links are still coming over.

 

[Michael M]

Try then

With wkbDest
    .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Value = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Value
    .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value
End With

 

[monkey_arms]

Same result, however with an added "Run-time error code '91', Object variable or With block variable not set", and the debugger stops at the copy/paste statement in the loop.