Trying to Change this code to make it paste 'Values' only.

[RWarczy]

HI All - Very new to this forum and also vba. I don't know much, so I appreciate any help.

I currently have this code. It copies and pastes data from a specific range that I've specified of multiple workbooks of a specific tab, into a master sheet. The data from each workbook is pasted directly under the information that it collected from the last workbook that was opened.

It is currently pasting the formulas; however, I need it to paste values only. Can anyone edit this code to fix that? Other than this, it works perfectly.

Thanks!

Ron

Sub TestCopyDataFromMultipleWorkbooks()
' updated 2008-04-30 by OPE
Dim varWorkbooks As Variant, wb As Workbook
    varWorkbooks = "Excel Workbooks (*.xl*),*.xl*,All Files (*.*),*.*"
    varWorkbooks = Application.GetOpenFilename(varWorkbooks, 1, _
        "Select one or more workbooks to copy data from (Ctrl+A selects all items in the folder)", , True)
    If Not IsArray(varWorkbooks) Then Exit Sub ' the user cancelled the dialog
    
    With Application
        .ScreenUpdating = False
        .Cursor = xlWait
    End With
    
    Set wb = Workbooks.Add ' create the new report workbook
    
    ' the following line(s) must be customized for each copy task
    ' copy from one named worksheet:
    CopyDataFromMultipleWorkbooks wb.Worksheets(1), varWorkbooks, "Load Summary ", "A9:P26"
    ' copy from the first (or another numbered) worksheet:
    'CopyDataFromMultipleWorkbooks wb.Worksheets(1), varWorkbooks, 1, "A9:P26"
    ' copy from all worksheets:
    'CopyDataFromMultipleWorkbooks wb.Worksheets(1), varWorkbooks, vbNullString, "A9:P26"
    
    wb.Activate
    Set wb = Nothing
    
    With Application
        .Cursor = xlDefault
        .StatusBar = False
        .ScreenUpdating = True
    End With
End Sub

Sub CopyDataFromMultipleWorkbooks(wsTarget As Worksheet, varWorkbooks As Variant, _
    varWorksheet As Variant, strWorksheetRange As String)
' updated 2008-04-30 by OPE
Dim r As Long, i As Long, wb As Workbook, ws As Worksheet, rng As Range
    If wsTarget Is Nothing Then Exit Sub ' no target workbook
    ' assumes that wsTarget is a new unfiltered worksheet
    If Not IsArray(varWorkbooks) Then Exit Sub ' invalid input
    
    For i = LBound(varWorkbooks) To UBound(varWorkbooks)
        On Error Resume Next
        Set wb = Workbooks.Add(varWorkbooks(i)) ' try to open a copy of the workbook
        On Error GoTo 0
        If Not wb Is Nothing Then
            With wb
                Application.StatusBar = "Copying information from " & varWorkbooks(i) & "..."
                If Len(varWorksheet) = 0 Then ' no worksheet name specified, copy from all worksheets
                    For Each ws In .Worksheets
                        With wsTarget ' find the next target row to paste the copied content
                            ' the following line assumes that column A always is populated
                            r = .Range("A" & .Rows.Count).End(xlUp).Row + 1
                        End With
                        On Error Resume Next
                        Set rng = ws.Range(strWorksheetRange)
                        If Not rng Is Nothing Then ' the range exists
                            rng.Copy wsTarget.Range("A" & r) ' copy the source range to the target worksheet
                            Set rng = Nothing
                        End If
                        On Error GoTo 0
                    Next ws
                    Set ws = Nothing
                Else ' copy from one worksheet
                    On Error Resume Next
                    Set ws = wb.Worksheets(varWorksheet)
                    On Error GoTo 0
                    If Not ws Is Nothing Then ' the worksheet exists
                        With wsTarget ' find the next target row to paste the copied content
                            ' the following line assumes that column A always is populated
                            r = .Range("A" & .Rows.Count).End(xlUp).Row + 1
                        End With
                        On Error Resume Next
                        Set rng = ws.Range(strWorksheetRange)
                        If Not rng Is Nothing Then ' the range exists
                            rng.Copy wsTarget.Range("A" & r) ' copy the source range to the target worksheet
                            Set rng = Nothing
                        End If
                        On Error GoTo 0
                        Set ws = Nothing
                    End If
                End If
                .Close False ' close the workbook copy without saving any changes
                Application.StatusBar = False
            End With
            Set wb = Nothing
        End If
    Next i ' next workbook
End Sub

[jaslake]

Hi Ron

Please wrap your code in code tags. Go to your original post, Edit Post. Highlight the code, click the # sign, Save Changes.

You need to make this a two liner to paste values

Rng.Copy
ws.Target.Range("A" & r).PasteSpecial (xlPasteValues)

[RWarczy]

Thanks! I made the change with marking the #. Can you tell me where I have to add those two lines? Or would you mind just placing it in where it needs to be? I honestly know nothing about how this works. Just winging it.

Thanks again for the prompt response, I really appreciate it!!

[jaslake]

Hi Ron

Can't test this but, assuming your code works otherwise

Sub CopyDataFromMultipleWorkbooks(wsTarget As Worksheet, varWorkbooks As Variant, _
        varWorksheet As Variant, strWorksheetRange As String)
' updated 2008-04-30 by OPE
    Dim r As Long, i As Long, wb As Workbook, ws As Worksheet, rng As Range
    If wsTarget Is Nothing Then Exit Sub    ' no target workbook
    ' assumes that wsTarget is a new unfiltered worksheet
    If Not IsArray(varWorkbooks) Then Exit Sub    ' invalid input

    For i = LBound(varWorkbooks) To UBound(varWorkbooks)
        On Error Resume Next
        Set wb = Workbooks.Add(varWorkbooks(i))    ' try to open a copy of the workbook
        On Error GoTo 0
        If Not wb Is Nothing Then
            With wb
                Application.StatusBar = "Copying information from " & varWorkbooks(i) & "..."
                If Len(varWorksheet) = 0 Then    ' no worksheet name specified, copy from all worksheets
                    For Each ws In .Worksheets
                        With wsTarget    ' find the next target row to paste the copied content
                            ' the following line assumes that column A always is populated
                            r = .Range("A" & .Rows.Count).End(xlUp).Row + 1
                        End With
                        On Error Resume Next
                        Set rng = ws.Range(strWorksheetRange)
                        If Not rng Is Nothing Then    ' the range exists

                            rng.Copy
                            ws.Target.Range("A" & r).PasteSpecial (xlPasteValues)
                            '                            rng.Copy wsTarget.Range("A" & r) ' copy the source range to the target worksheet
                            '                            Set rng = Nothing
                        End If
                        On Error GoTo 0
                    Next ws
                    Set ws = Nothing
                Else    ' copy from one worksheet
                    On Error Resume Next
                    Set ws = wb.Worksheets(varWorksheet)
                    On Error GoTo 0
                    If Not ws Is Nothing Then    ' the worksheet exists
                        With wsTarget    ' find the next target row to paste the copied content
                            ' the following line assumes that column A always is populated
                            r = .Range("A" & .Rows.Count).End(xlUp).Row + 1
                        End With
                        On Error Resume Next
                        Set rng = ws.Range(strWorksheetRange)
                        If Not rng Is Nothing Then    ' the range exists
                            
                            rng.Copy
                            ws.Target.Range("A" & r).PasteSpecial (xlPasteValues)
                            '                            rng.Copy wsTarget.Range("A" & r)    ' copy the source range to the target worksheet
                            '                            Set rng = Nothing
                        End If
                        On Error GoTo 0
                        Set ws = Nothing
                    End If
                End If
                .Close False    ' close the workbook copy without saving any changes
                Application.StatusBar = False
            End With
            Set wb = Nothing
        End If
    Next i    ' next workbook
End Sub

[RWarczy]

That one didn't work for some reason, but I saw where you input the rng.Copy and the second line that you sent, and plugged it into the original one that I had sent. It gave me an error, so I changed ws.Target to wsTarget on the line you sent and it worked like a charm!

Thank you very very much!!!!

[jaslake]

Hi Ron...yeah...those dots will get you every time...sorry about that...glad you worked through.

You're welcome...glad I could help. Thanks for the Rep.

If this resolves your issue please mark your Thread as SOLVED.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[RWarczy]

I don't even know how I figured out it was supposed to be there. I have no idea what that dot means.
Just saw it somewhere else without it.

Thanks again! Will mark as solved. Y/W on the rep!

[jaslake]

Hi Ron

Well, you were prescient enough that you realized your old code didn't have the dot...and it worked (not as you wished...but it worked). The new code DIDN'T work with the dot...you saw that...you fixed it. I'd say that a job well done.