VBA copy/paste dynamic range IF there is data

[Lukerbach]

Super new to Macros and VBA. My WB is set up with 7 "daily orders" sheets, and 1 sheet as a weekly report. I want to transfer a range from each day and paste onto the weekly report. My data range I want to select always starts in cell B6, and goes through column K...the issue I am running into is on some days we might not get any new data in, so using xlDown to copy/paste my range doesn't work because there is not any data in B6. Essentially, I would like to copy/paste data IF there is any data in that range. I work in supply chain, so I am unsure when/how many orders will be placed that day, if any. Thank you and hopefully that was a clear explanation. I do not have any code written yet for it...

 

[kevin9999]

Not really enough information to make a start on an answer here. Ideally, provide a sample of your 'weekly report' sheet, and one of your 'daily orders' sheet using the XL2BB Tool.

What is the name of the weekly report sheet? Do you want the weekly report sheet cleared of any existing data first? Does that sheet have headers & where do you want the data pasted to?

 

[kevin9999]

The following code is based on a number of assumptions: 1) your 'weekly report' sheet is called "weekly", 2) your 'daily orders' sheets have headers in row 5, with your data starting in row 6; 3) you don't want the "weekly" sheet cleared before the copy takes place, and 4) you want the new data pasted into column A of the "weekly" sheet at the first available empty row.

Option Explicit
Sub Copy_Daily()
    Dim ws1 As Worksheet, sh As Worksheet
    Set ws1 = Worksheets("weekly")
    Dim lrDest As Long, lrSource As Long
    Application.ScreenUpdating = False
    
    For Each sh In ThisWorkbook.Worksheets
        If sh.Name <> "weekly" Then
            lrDest = ws1.Cells(Rows.Count, "A").End(3).Row + 1
            lrSource = sh.Cells(Rows.Count, "B").End(3).Row
            If lrSource > 5 Then
                sh.Range(sh.Cells(6, 2), sh.Cells(lrSource, 11)).Copy ws1.Cells(lrDest, 1)
            End If
        End If
    Next sh
    Application.ScreenUpdating = True
End Sub