Select a column with Varying data (VBA)

[O177812]

I have constructed the following:

Workbooks.Open Filename:= _
"P:\word documents\INW Group\WORKING\Shipping_Data.xlsx"
Columns("A:Z").Select
Selection.EntireColumn.Hidden = False
Range("V1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$V$500").AutoFilter Field:=22, Criteria1:="<>"
Windows("Shipping_Data.xlsx").Activate
Range("V2:V77").Select
Selection.Copy
Windows("SO Tracking.xlsx").Activate
Range("C2").Select
ActiveSheet.Paste
Range("B2").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("B2:B16")
Range("B2:B16").Select
Windows("Shipping_Data.xlsx").Activate
ActiveWindow.LargeScroll ToRight:=-1
Range("B2:B77").Select
Selection.Copy
Windows("SO Tracking.xlsx").Activate
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

However I need to incorporate a function, possibly .End(xlDown), because the data varies day by day and I don't want the end of the selection limited. I want it to select to the end of the data in that column.

Any help would be appreciated.

 

[davesexcel]

Trying to decipher your code, here would be the beginning set up and possible the first copy/paste part of the code.
The assumption is workbook "SO Tracking.xlsx" is already open.

    Dim bk As Workbook, sh As Worksheet
    Dim wb As Workbook, ws As Worksheet, rng1 As Range
    Set wb = Workbooks("SO Tracking.xlsx")
    Set ws = wb.Sheets("Sheet1")'change sheet name if required
    Set rng1 = ws.Range("C2")'set first range to paste

    Set bk = Workbooks.Open("P:\word documents\INW Group\WORKING\Shipping_Data.xlsx")
    Set sh = bk.Sheets("Sheet1")
    sh.Columns("A:Z").EntireColumn.Hidden = False
    sh.Range("A1").AutoFilter Field:=22, Criteria1:="<>"
    bk.Range("V2:V" & Cells(Rows.Count, "V").End(xlUp).Row).Copy Destination:=rng1

 

[O177812]

Trying to decipher your code, here would be the beginning set up and possible the first copy/paste part of the code.
The assumption is workbook "SO Tracking.xlsx" is already open.

    Dim bk As Workbook, sh As Worksheet
    Dim wb As Workbook, ws As Worksheet, rng1 As Range
    Set wb = Workbooks("SO Tracking.xlsx")
    Set ws = wb.Sheets("Sheet1")'change sheet name if required
    Set rng1 = ws.Range("C2")'set first range to paste

    Set bk = Workbooks.Open("P:\word documents\INW Group\WORKING\Shipping_Data.xlsx")
    Set sh = bk.Sheets("Sheet1")
    sh.Columns("A:Z").EntireColumn.Hidden = False
    sh.Range("A1").AutoFilter Field:=22, Criteria1:="<>"
    bk.Range("V2:V" & Cells(Rows.Count, "V").End(xlUp).Row).Copy Destination:=rng1

I recieved the following error on the last line:

It won't copy and paste

 

[davesexcel]

Should be,

sh.Range("V2:V" & Cells(Rows.Count, "V").End(xlUp).Row).Copy Destination:=rng1

 

[Fluff]

Or maybe

sh.Range("V2:V" & sh.Cells(Rows.Count, "V").End(xlUp).Row).Copy Destination:=rng1