vba copy paste values and formats

[jtodd]

Hi
I have this code Sub cpynpst()
Dim sh4 As Worksheet, sh5 As Worksheet, lr As Long, rng As Range
Set sh4 = Sheets("A Days")
Set sh5 = Sheets("WeeklyDetail")
lr = sh4.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh4.Range("A2:A" & lr)
rng.EntireRow.Copy sh5.Cells(Rows.Count, 1).End(xlUp)(2)
End Sub

This work fine but, how would I adapt it to only copy from columns from A to D and paste special values ?

Just cannot get it to work

 

[MARK858]

If it is only values as per your description and not values and formats as per your title then one way is

Sub cpynpst()
    Dim sh4 As Worksheet, sh5 As Worksheet, lr As Long, rng As Range
    Set sh4 = Sheets("A Days")
    Set sh5 = Sheets("WeeklyDetail")
    lr = sh4.Cells(Rows.Count, 1).End(xlUp).Row
    
    Set rng = sh4.Range("A2:A" & lr)
    
    rng.Resize(, 4).Copy
    sh5.Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlValues
    Application.CutCopyMode = False
End Sub

 

[jtodd]

Many thanks ,
Worked a charm,

If I did need to keep the formating would that be dificult ?

 

[MARK858]

would that be dificult ?

No

Sub cpynpst()
    Dim sh4 As Worksheet, sh5 As Worksheet, lr As Long, rng As Range
    
    Set sh4 = Sheets("A Days")
    Set sh5 = Sheets("WeeklyDetail")
    lr = sh4.Cells(Rows.Count, 1).End(xlUp).Row
    Set rng = sh4.Range("A2:A" & lr)

    rng.Resize(, 4).Copy
    With sh5.Cells(Rows.Count, 1).End(xlUp)(2)
        .PasteSpecial xlValues
        .PasteSpecial xlFormats
    End With
    
    Application.CutCopyMode = False
End Sub