copy paste macros

[jtodd]

HI i have 2 macros that copy and paste data from multiple sheets to one single sheet.
the issue is that "CopyDPDData" works fine but the "CopyScanData" only copies the one sheet -MON_SCAN and ignores "TUES_SCAN" (Will be expanding to all days of the week if i can get it to work).
As far as i can see the code is exacly the same but with the sheet names changed .

hope it is something obvious but i cannot see it.

Sub CopyScanData()
    Dim ws As Worksheet
     
    Application.ScreenUpdating = False
    For Each ws In Worksheets
        If ws.Name <> "MON_DPD" And ws.Name <> "TUES_DPD" And ws.Name <> "SUMMARY" And ws.Name <> "WEEKLY" And ws.Name <> "WEEKLY_SCAN" And ws.Name <> "WEEKLY_DPD" Then
            ws.Range("A1:C5000").Copy
            Worksheets("WEEKLY_SCAN").Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
           
        End If
    Next ws
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
End Sub

Sub CopyDPDData()
    Dim ws As Worksheet
     
    Application.ScreenUpdating = False
    For Each ws In Worksheets
        If ws.Name <> "MON_SCAN" And ws.Name <> "TUES_SCAN" And ws.Name <> "SUMMARY" And ws.Name <> "WEEKLY" And ws.Name <> "WEEKLY_SCAN" And ws.Name <> "WEEKLY_DPD" Then
            ws.Range("A1:Q5000").Copy
            Worksheets("WEEKLY_DPD").Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
           
        End If
    Next ws
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
End Sub

 

[Fluff]

Will you always have values in col A on the scan sheets?

 

[jtodd]

Will you always have values in col A on the scan sheets?

Appologies for delay , yes there will be values in col A

 

[Fluff]

Ok, in that case if you step through the macro line by line using F8, does it go to this line ws.Range("A1:C5000").Copy when you get to the "TUES_SCAN" sheet?

 

[jtodd]

Ok realised not ignoring it but copying it much further down the sheet -
the DPD file copies fine with no spaces - values only but the SCAN file leaves spaces for the rows with formulas in but no data .
the DPD files also have formulas but no data but when coppied does not leave any spaces ?

 

[Fluff]

Ok, how about

Sub CopyScanData()
    Dim ws As Worksheet
     
    Application.ScreenUpdating = False
    For Each ws In Worksheets
        If ws.Name <> "MON_DPD" And ws.Name <> "TUES_DPD" And ws.Name <> "SUMMARY" And ws.Name <> "WEEKLY" And ws.Name <> "WEEKLY_SCAN" And ws.Name <> "WEEKLY_DPD" Then
            With Worksheets("WEEKLY_SCAN").Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0)
               .Resize(5000, 3).Value = ws.Range("A1:C5000").Value
            End With
        End If
    Next ws
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
End Sub

 

[jtodd]

Worked like a dream thankyou

any idea why it would not work , the only thing i can see is that the PasteSpecial (xlPasteValues) work on one but not the other .

again thanks

 

[Fluff]

Using pastespecial xlvalues will paste a null string (ie "") whenever you have a formula that returns "" whilst using .Value=.Value will ignore any cells with "" & leave them blank.