copy paste range, values or formats?

[SURFER349]

So I've got this string of code that finds a sheet by name (source), then copies its data onto a combined sheet (destination) after the last row.

I pulled this from google, and not quite sure yet how the syntax works w/ the copyrng and vlaues vs formats.

How do I adjust this to preserve the formats of the source sheets?

Dim CopyRng As Range
Dim Destws As Worksheet
Dim ws As Worksheet
Dim Last As Long

Set Destws = ActiveWorkbook.Sheets("Sheet1")

For Each ws In ActiveWorkbook.Worksheets
     If LCase(ws.Name) Like LCase("source") Then
          Last = Lastrow(Destws) 'uses LastRow() sub listed above

          Set CopyRng = ws.Range("B1").CurrentRegion 'uses the current block of data
          
          CopyRng.Copy    'This statement copies values and formats.            
          With CopyRng    'paste values only, one row down from last row
               Destws.Cells(Last + 1, "A").Resize(.Rows.Count, .Columns.Count).Value = .Value
               Application.CutCopyMode = False
          End With
     End If
Next

 

[My Aswer Is This]

Assuming all the other lines of code works the way you want it to.

Change this line of code:

Destws.Cells(Last + 1, "A").Resize(.Rows.Count, .Columns.Count).Value = .Value

To this:

Destws.Cells(Last + 1, "A").Resize(.Rows.Count, .Columns.Count).PasteSpecial