Selecting several tables of varying size in non-contiguous range

[Kentetsu]

I have a sheet with 6 tables of varying size (length & width). The furthest Column used will be Column J.

So, I need to start at the bottom of Column A and go up to find the first populated cell, then extend that range to the top of the sheet and over to Column J.

I don't know why I'm having so much difficulty with this, as I've done it before and it was pretty simple from what I recall. But I've been wracking my brain on this all day and getting no traction at all.

If anyone can give me a hand with this, I would really appreciate it.

 

[Kentetsu]

That is the result that I am getting with Peter_SSs's solution. I am attempting to select A1 last cell with data/Column J.

I've even tried designating two cells as named ranges, then selecting between the two. Not having much luck there either, but I'm still researching:

Range(Cells(2, Range("StartRange").Row), Cells(2, Range("EndRange").Column)).Select

Thanks for the assistance Domenic..

 

[Domenic]

The starting cell for the returned reference should always be A1. When there's no data in Column A, it would return A1:J1. With data in Column A, it would return A1:J?. I can't see how it would return anything else.

 

[Kentetsu]

Yeah, it shouldn't be this difficult.

Here is a copy of the output so you can at least see what I'm working with: https://www.dropbox.com/s/87gp0xovh0nfj1x/Recap.xlsm?dl=0

 

[Kentetsu]

Okay, maybe this is a problem between my own ears. Adding Peter's suggestion directly to my code failed. However, placing it in its own module and then calling the sub works. I think I'll be able to figure it out from here now. Thanks for your patience Domenic...

 

[Domenic]

You shouldn't have to place the code in its own module. Since I don't download workbooks, try posting the code directly in this thread using code tags.

 

[Kentetsu]

Okay, just please understand that this is a very rough draft. I usually break up each major step and place them in modules (a bit easier to understand and maintain). Once I get the bugs worked out, then I will organize it in a better manner.

Thanks again for the input...

(Scroll all the way to the bottom for the bit we've been working on)

Option Explicit
Private Sub CommandButton1_Click()
Dim wbNewLog As Workbook
Dim CookLog As String
Dim OvenIssues As String
Dim RackIssues As String
Dim w As Worksheet
Dim IsBlank As Boolean
Dim rng As Range
Dim lngLastRow As Long

Application.ScreenUpdating = False

CookLog = "J:\ZZ  PII Ovens\Batch Ovens PC\Oven Logs Database\Log2.xlsm"
OvenIssues = "J:\P-II Ovens\Batch Oven Issue Log.xls"
RackIssues = "J:\ZZ  PII Ovens\Rack Repair Tracking\Rack Repair List3.xlsb"

'------------------------------------------------------------------------------------
'           GET LOADING INFORMATION
   
            Application.EnableEvents = False
            Workbooks.Open CookLog, ReadOnly:=True
            Application.EnableEvents = True

            ActiveWorkbook.Worksheets("Log").Copy
            
Set wbNewLog = ActiveWorkbook
        
            Application.DisplayAlerts = False
            ActiveWorkbook.SaveAs "J:\tempfile\Recap.xlsm", FileFormat:=52
            Application.DisplayAlerts = True
            Windows("Log2.xlsm").Activate
            ActiveWorkbook.Close savechanges:=False
            
Set w = Worksheets("Log")

            With Sheets("Log")
                .Unprotect Password:="****"
                Worksheets.Add().Name = "PII Yields"
                'Filter by date
                w.Cells.AutoFilter Field:=1, Criteria1:=Range("I8").Value, _
                Operator:=xlOr, Criteria2:="Important"
                Worksheets("Log").UsedRange.SpecialCells(xlCellTypeVisible).Copy
                Sheets("PII Yields").Range("a1").PasteSpecial Paste:=8
                Sheets("PII Yields").Range("a1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Transpose:=False
                Application.DisplayAlerts = False
                Sheets("Log").Delete
                Application.DisplayAlerts = True
            End With
                        
            With Sheets("PII Yields")
                .Range("A1") = "Loaded"
                .Range("B1") = "SKU"
                .Range("C1") = "Racks"
                .Range("D1") = "Oven"
                .Range("O1") = "Unload"
                .Range("A1").Select
            End With
                           
            With ActiveSheet.ListObjects.Add(xlSrcRange, ActiveSheet.Range("A1").CurrentRegion, , xlYes)
                .Name = "Table1"
                .TableStyle = "TableStyleMedium9"
            End With
                            
            With wbNewLog.Worksheets("PII Yields")
                    .Range("Q:S").EntireColumn.Delete
                    .Range("L:N").EntireColumn.Delete
                    .Range("E:J").EntireColumn.Delete
                    .Range("A:A, F:F").NumberFormat = "HH:MM;@"
                    .Range("E:E, G:G").WrapText = True
                    .Range("A1").CurrentRegion.HorizontalAlignment = xlCenter
                    .Range("A1").EntireRow.HorizontalAlignment = xlLeft
                    .Range("A1").EntireRow.Insert
                With .Range("A1")
                    .Value = "Ovens Loading & Unloading Activity"
                    .Font.Size = 20
                    .Font.Bold = True
                End With
          End With
'--------------------------------------------

'       GET MAINTENANCE ISSUES

Sheets("PII Yields").Cells(Rows.Count, "A").End(xlUp)(4).Name = "PasteIssues"
                        
            Application.EnableEvents = False
            Workbooks.Open OvenIssues, ReadOnly:=True
            Application.EnableEvents = True
            
    With Worksheets("Repair Requests")
            .Unprotect Password:="****"
            .Range("A3:Q3").AutoFilter
'            Filter for current date
            .Cells.AutoFilter Field:=1, Operator:= _
            xlFilterValues, Criteria2:=Array(2, Date)
             Application.Goto Worksheets("Repair Requests").Range("A1"), True
            .Range("A1:A2").EntireRow.Hidden = True
            .Range("J:Q").EntireColumn.Delete
            .Range("F:F").EntireColumn.Delete
             Worksheets("Repair Requests").UsedRange.SpecialCells(xlCellTypeVisible).Copy
    End With
                    
        Windows("Recap.xlsm").Activate
        Application.Goto Reference:="PasteIssues"
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

    With Sheets("PII Yields")
        .Range("PasteIssues").Select
        .Range(Selection, Selection.End(xlToRight)).Select
                     
            With Selection
                .WrapText = True
                .Font.Bold = True
            End With
                    
            With ActiveSheet.ListObjects.Add(xlSrcRange, ActiveSheet.Range("PasteIssues").CurrentRegion, , xlYes)
                .Name = "Table2"
                .TableStyle = "TableStyleMedium9"
            End With
            
            With .Range("PasteIssues")(-0)
                .Value = "Oven Mechanical Issues Noted"
                .Font.Size = 20
                .Font.Bold = True
            End With
                .Range("PasteIssues")(1, 5).Select
                .Range(Selection, Selection.End(xlDown)).Select
            With Selection
                .WrapText = True
            End With
        End With
        
        Sheets("PII Yields").Range("PasteIssues")(2).Select
        If Sheets("PII Yields").Range("PasteIssues")(2).Value = IsBlank Then
            ActiveCell(1, 5).Value = "No Issues Reported Today"
        End If
                
'------------------------------------------------------------------------------------------------
'           GET RESOLVED ISSUES

        With Sheets("PII Yields")
            .Cells(Rows.Count, "A").End(xlUp)(4).Select
             Selection.Name = "PasteResolved"
        End With
            
        With Workbooks("Batch Oven Issue Log.xls").Sheets("Resolved")
            .Unprotect Password:="****"
'            Filter for current date
            
            .Cells.AutoFilter Field:=1, Operator:= _
            xlFilterValues, Criteria2:=Array(2, Date)
                                   
            .Range("A1:A2").EntireRow.Hidden = True
            .Range("N:Q").EntireColumn.Delete
            .Range("F:F").EntireColumn.Delete
            .UsedRange.SpecialCells(xlCellTypeVisible).Copy
        End With
                    
        Application.Goto Reference:="PasteResolved"
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

        With Sheets("PII Yields")
                .Range("PasteResolved").Select
                .Range(Selection, Selection.End(xlToRight)).Select
                
            With Selection
                .WrapText = True
                .Font.Bold = True
            End With
                    
            With ActiveSheet.ListObjects.Add(xlSrcRange, ActiveSheet.Range("PasteResolved").CurrentRegion, , xlYes)
                .Name = "Table5"
                .TableStyle = "TableStyleMedium9"
            End With
            
            With .Range("PasteResolved")(-0)
                .Value = "Oven Mechanical Issues Resolved"
                .Font.Size = 20
                .Font.Bold = True
            End With
            
            .Range("PasteResolved")(1, 5).Select
            .Range(Selection, Selection.End(xlDown)).Select
                    
            With Selection
                    .WrapText = True
            End With
                
            .Range("PasteResolved")(1, 9).Select
            .Range(Selection, Selection.End(xlDown)).Select
                    
            With Selection
                .WrapText = True
            End With
        End With
        
            Sheets("PII Yields").Range("PasteResolved")(2).Select
        If Sheets("PII Yields").Range("PasteResolved")(2).Value = IsBlank Then
            ActiveCell(1, 5).Value = "No Issues Resolved Today"
        End If
        
       Windows("Batch Oven Issue Log.xls").Activate
                Application.EnableEvents = False
                Application.DisplayAlerts = False
                ActiveWorkbook.Close , False
                Application.DisplayAlerts = True
                Application.EnableEvents = True

'----------------------------------------------------------------------------------------

'           GET RACK REPAIR INFO
                    
            Application.EnableEvents = False
            Workbooks.Open RackIssues, ReadOnly:=True
            Application.EnableEvents = True

            With Sheets("Requested")
                lngLastRow = ActiveSheet.UsedRange.Rows.Count
                .Unprotect Password:="****"
                .Range("E:I").EntireColumn.Delete
                .Range("B:B").EntireColumn.Delete
                .Range("B:B").NumberFormat = "MM/DD/YY"
                .Range("B2").EntireColumn.Insert
                .Range("B2").Value = "=RIGHT(A2,4)"
                .Range("A1").Select
                Selection.End(xlDown).Select
                ActiveCell.Offset(0, 1).Select
                .Range(Selection, Selection.End(xlUp)).Select
                Selection.FillDown
                Selection.Copy
                .Range("A2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
                .Range("B:B").EntireColumn.Delete
'                   Filter by Date
                .Range("A1:C1").AutoFilter
                .Cells.AutoFilter Field:=2, Operator:= _
                xlFilterValues, Criteria2:=Array(2, Date)
                .Range("A1").Select
                .UsedRange.SpecialCells(xlCellTypeVisible).Copy
            End With
                    
Windows("Recap.xlsm").Activate
        With Sheets("PII Yields")
                .Cells(Rows.Count, "A").End(xlUp)(4).Select
                Selection.Name = "PasteRacks"
                .Range("PasteRacks").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
                xlNone, SkipBlanks:=False, Transpose:=False
                .Range("PasteRacks").Select
                .Range(Selection, Selection.End(xlToRight)).Select
            With Selection
                    .WrapText = True
                    .Font.Bold = True
            End With
                    
            With ActiveSheet.ListObjects.Add(xlSrcRange, ActiveSheet.Range("PasteRacks").CurrentRegion, , xlYes)
                .Name = "Table3"
                .TableStyle = "TableStyleMedium9"
            End With
                              
            With .Range("PasteRacks")(-0)
                .Value = "Racks Tagged for Repair Today"
                .Font.Size = 20
                .Font.Bold = True
            End With
                
            .Range("PasteRacks")(1, 3).Select
            .Range(Selection, Selection.End(xlDown)).Select
            With Selection
                .WrapText = True
            End With
        End With
        
        Sheets("PII Yields").Range("PasteRacks")(2).Select
        If Sheets("PII Yields").Range("PasteRacks")(2).Value = IsBlank Then
            ActiveCell(1, 3).Value = "No Issues Today"
        End If
        
'--------------------------------------------------------------------------------------------
'           GET REPAIRED RACKS INFORMATION

        With Sheets("PII Yields")
                .Cells(Rows.Count, "A").End(xlUp)(4).Select
                Selection.Name = "PasteFixedRacks"
        End With

            Windows("Rack Repair List3.xlsb").Activate
            Sheets("Completed").Activate
            
        With Sheets("Completed")
                .Unprotect Password:="****"
                .Range("B:B").EntireColumn.Delete
                .Range("B:B").NumberFormat = "MM/DD/YY"
                .Range("G:G").NumberFormat = "MM/DD/YY"
                .Range("B2").EntireColumn.Insert
                .Range("B2").Value = "=RIGHT(A2,4)"
                .Range("A1").Select
                Selection.End(xlDown).Select
                ActiveCell.Offset(0, 1).Select
                .Range(Selection, Selection.End(xlUp)).Select
                Selection.FillDown
                Selection.Copy
                .Range("A2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
                .Range("B:B").EntireColumn.Delete
'                   Filter by Date
                .Range("A1:H1").AutoFilter
                .Cells.AutoFilter Field:=7, Operator:= _
                xlFilterValues, Criteria2:=Array(2, Date)
                .Range("A1").Select
                .UsedRange.SpecialCells(xlCellTypeVisible).Copy
        End With

Windows("Recap.xlsm").Activate
                    
        With Sheets("PII Yields")
                .Range("PasteFixedRacks").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
                xlNone, SkipBlanks:=False, Transpose:=False
                .Range("PasteFixedRacks").Select
                .Range(Selection, Selection.End(xlToRight)).Select
            With Selection
                .WrapText = True
                .Font.Bold = True
            End With
                    
            With ActiveSheet.ListObjects.Add(xlSrcRange, ActiveSheet.Range("PasteFixedRacks").CurrentRegion, , xlYes)
                .Name = "Table9"
                .TableStyle = "TableStyleMedium9"
            End With
                        
            With .Range("PasteFixedRacks")(-0)
                .Value = "Racks Tagged for Repair Today"
                .Font.Size = 20
                .Font.Bold = True
            End With
                .Range("PasteFixedRacks")(1, 3).Select
                .Range(Selection, Selection.End(xlDown)).Select
            With Selection
                    .WrapText = True
            End With
                
                .Range("PasteFixedRacks")(1, 5).Select
                .Range(Selection, Selection.End(xlDown)).Select
            With Selection
                    .WrapText = True
            End With
        End With
        
        Sheets("PII Yields").Range("PasteFixedRacks")(2).Select
        
        If Sheets("PII Yields").Range("PasteFixedRacks")(2).Value = IsBlank Then
            ActiveCell(1, 5).Value = "No Racks Released Today"
        End If
            
            Application.DisplayAlerts = False
            Windows("Rack Repair List3.xlsb").Close , False
            Application.DisplayAlerts = True

'---------------------------------------------------------------------------------------
'           FORMATTING

        With Sheets("PII Yields")
            .Range("A:A").ColumnWidth = 10
            .Range("B:B").ColumnWidth = 14
            .Range("C:C").ColumnWidth = 15
            .Range("D:D, G:G, H:H").ColumnWidth = 18
            .Range("E:E").ColumnWidth = 28
            .Range("F:F").ColumnWidth = 14
            .Range("A:A").ColumnWidth = 15
            .Range("I:I").ColumnWidth = 20
            .Range("J:K").EntireColumn.Delete
        
'Call GetRange          (In separate module, this correctly identifies the range)

Set rng = Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 10)
MsgBox "Is this the range you want?" & vbLf & rng.Address(0, 0)       'THIS RETURNS A1:J1
        End With

'       AT THIS POINT, NEED TO COPY rng

Call MailMe
            
'---------------------------------------------------------------------------------------
'           WRAP UP
                                                   
                    'Return Application Settings to default & Close this spreadsheet
                    
                    Application.Goto wbNewLog.Worksheets("PII Yields").Range("A1"), True
                    ThisWorkbook.Activate
                    Application.ScreenUpdating = True
                    Application.ExecuteExcel4Macro "Show.ToolBar(""Ribbon"", True)"
                    Application.DisplayFormulaBar = True
                    ActiveWindow.DisplayHeadings = True
                    ActiveWindow.DisplayGridlines = True
                    Application.DisplayAlerts = True
                    Windows("Recap.xlsm").Activate
'                    ThisWorkbook.Close savechanges:=False
End Sub

 

[Domenic]

It looks like you'd like to define your range in "PII Yields", right? If so, you'll need to qualify your references. You'll need to replace...

Set rng = Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 10)

with

Set rng = .Range("A1", .Range("A" & .Rows.Count).End(xlUp)).Resize(, 10)

Does this help?

 

[Kentetsu]

That did the trick Domenic. Was it because this was within a With/End With block that I needed those extra periods?

 

[Domenic]

That's right. Without those periods, it refers to the active sheet. With those periods, it refers to the object being manipulated by With/End With.

 

[Kentetsu]

One more lesson learned. Thank you...