I thought this would be a simple enough task but after a few days of beating my head on the desk and searching the internet for hours for a solution here I am.

I need to copy 2 lists of serial numbers from a single worksheet in a variable workbook. I have the variable workbook name called out in a cell on the destination worksheet.
The purpose of this list is for validation of the serial number being entered for shipping.
My goal is for an operator to be able to click a button and have the list of serial numbers update in the background since product is occasionally shipped in the same day as it is built and the assembly area may still be using the file.

Below is the code I have pieced together so far and I've become stuck on "Workbooks(BuildBook).Worksheets("Build").Range("X4:X" & RedLast).Copy". Subscript out of range error. My limited knowledge says it should work.

Thanks for the help



Sub Refresh()

'Application.ScreenUpdating = False

'Open Build workbook and copy serial numbers for updating of validation worksheet

'Set Workbook name variables
   Dim BuildBook As String
    BuildBook = Sheets("Validate").Range("D2").Value
   Dim ShipBook As String
     ShipBook = Sheets("Validate").Range("D3").Value
'Open/Refresh Build in read only mode
   Workbooks.Open Filename:=BuildBook, ReadOnly:=True
   Dim BluLast As Integer
    BluLast = Worksheets("Build").Range("E65536").End(xlUp).Row
   Dim RedLast As Integer
    RedLast = Worksheets("Build").Range("X65536").End(xlUp).Row
   
' Copy list of serial numbers from the build workbook and place in Validate sheet
   Worksheets("Build").Range("E4:E" & BluLast).Copy
   Workbooks(ShipBook).Worksheets("Validate").Range("A2").PasteSpecial (xlPasteValues)
  
  Dim ValLast As Integer
   ValLast = Workbooks(ShipBook).Sheets("Validate").Range("A65536").End(xlUp).Row + 1
   
   Workbooks(BuildBook).Worksheets("Build").Range("X4:X" & RedLast).Copy
   Workbooks(ShipBook).Worksheets("Validate").Range("A" & ValLast).PasteSpecial (xlPasteValues)
   
   
  

'Application.ScreenUpdating = True

End Sub