CODEWITHSUNDEEP
excelvba
TestMcTesterson
TestMcTesterson

Reputation: 133

Paste Special breaking VBA Module

I have been trying to figure out why I am not able to paste values only in this VBA module. Can anyone spot my issue? I have tried many different combinations, and still getting compile errors

Sub Merge_Sheets()

'Set Master sheet for consolidation
Set mtr = Worksheets("Master")

Set wb = ThisWorkbook

'loop through all sheets
For Each ws In wb.Worksheets
     'except the master sheet from looping
     If ws.Name <> "Master" Then
        ws.Activate
        Range("A5:P20").Copy _
        mtr.Range("A" & mtr.Cells(Rows.Count, 1).End(xlUp).Row + 1).PasteSpecial xlPasteValues
            End If
Next ws

Worksheets("Master").Activate

End Sub

Does anyone have any ideas for the issue on this? Thanks in advance!

Upvotes: 2

Views: 56

Answers (1)

VBasic2008
VBasic2008

Reputation: 54767

Append Range Values From All Worksheets

Option Explicit

Sub AppendRangeValues()

    Const SRC_RANGE As String = "A5:P20"

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim dws As Worksheet: Set dws = wb.Sheets("Master")
    Dim dfCell As Range
    Set dfCell = dws.Cells(dws.Rows.Count, "A").End(xlUp).Offset(1)
    
    Dim drg As Range, rCount As Long
    
    With dws.Range(SRC_RANGE)
        rCount = .Rows.Count
        Set drg = dfCell.Resize(rCount, .Columns.Count)
    End With
    
    Dim sws As Worksheet
    
    For Each sws In wb.Worksheets
        If Not sws Is dws Then
            drg.Value = sws.Range(SRC_RANGE).Value
            Set drg = drg.Offset(rCount)
        End If
    Next sws
    
    MsgBox "Range values appended.", vbInformation
    
End Sub

Upvotes: 2

Related Questions