Copy paste workbooks with text 2 column

[cortexnotion]

Evening

I'm after some help with a copy and paste from multiple workbooks to my master workbook. I need to:

- Open 4 workbooks in total and copy multiple columns of data to Master workbook - the range is dynamic so I need to find the last row in each column (don't think I have this right below)
- Close the workbooks in sequence - don't need to save the changes.
- Use Text to Column in Master workbook sheet "GISSTadmin" on Columns B,D,G,H,K,P,S from row 3 down to last 3 (need help with how to do this - Row 1 and 2 and merged so I can't do the entire column)

Many thanks

Sub ProjectData()

Application.ScreenUpdating = False

Sheets("GISSTadmin").Visible = True

MsgBox "Would you like to continue?", vbOKCancel

Dim EUT As Workbook, GATED As Workbook, BLOCK As Workbook, IW66 As Workbook
Dim lngLastRow1 As Long, lngLastRow2 As Long, lngLastRow3 As Long, lngLastRow4 As Long

Set EUT = Workbooks.Open(ThisWorkbook.Path & "\EUT.xls")
Set GATED = Workbooks.Open(ThisWorkbook.Path & "\GATED.xls")
Set BLOCK = Workbooks.Open(ThisWorkbook.Path & "\BLOCK.xls")
Set IW66 = Workbooks.Open(ThisWorkbook.Path & "\IW66.xls")

lngLastRow1 = EUT.Sheets("Sheet1").Range("B3").End(xlDown).Row
lngLastRow2 = GATED.Sheets("Sheet1").Range("B3").End(xlDown).Row
lngLastRow3 = BLOCK.Sheets("Sheet1").Range("B3").End(xlDown).Row
lngLastRow4 = IW66.Sheets("Sheet1").Range("B3").End(xlDown).Row

ThisWorkbook.Sheets("GISSTadmin").Range("A3:A & lngLastRow1).Value = EUT.Sheets("Sheet1").Range("B2:B & lngLastRow1).Value
ThisWorkbook.Sheets("GISSTadmin").Range("B3:B & lngLastRow1).Value = EUT.Sheets("Sheet1").Range("C2:C & lngLastRow1).Value
ThisWorkbook.Sheets("GISSTadmin").Range("C3:C & lngLastRow1).Value = EUT.Sheets("Sheet1").Range("F2:F & lngLastRow1).Value
EUT.Close SaveChanges:=False

ThisWorkbook.Sheets("GISSTadmin").Range("D3:D & lngLastRow1).Value = INCOMING.Sheets("Sheet1").Range("J3:J & lngLastRow1).Value
ThisWorkbook.Sheets("GISSTadmin").Range("E3:E & lngLastRow1).Value = INCOMING.Sheets("Sheet1").Range("F3:F & lngLastRow1).Value
ThisWorkbook.Sheets("GISSTadmin").Range("F3:F & lngLastRow1).Value = INCOMING.Sheets("Sheet1").Range("G3:G & lngLastRow1).Value
ThisWorkbook.Sheets("GISSTadmin").Range("G3:G & lngLastRow1).Value = INCOMING.Sheets("Sheet1").Range("D3:D & lngLastRow1).Value
INCOMING.Close SaveChanges:=False

ThisWorkbook.Sheets("GISSTadmin").Range("H3:H & lngLastRow1).Value = GATED.Sheets("Sheet1").Range("J3:J & lngLastRow1).Value
ThisWorkbook.Sheets("GISSTadmin").Range("I3:I & lngLastRow1).Value = GATED.Sheets("Sheet1").Range("F3:F & lngLastRow1).Value
ThisWorkbook.Sheets("GISSTadmin").Range("J3:J & lngLastRow1).Value = GATED.Sheets("Sheet1").Range("G3:G & lngLastRow1).Value
ThisWorkbook.Sheets("GISSTadmin").Range("K3:K & lngLastRow1).Value = GATED.Sheets("Sheet1").Range("D3:D & lngLastRow1).Value
GATED.Close SaveChanges:=False

ThisWorkbook.Sheets("GISSTadmin").Range("L3:L & lngLastRow1).Value = BLOCK.Sheets("Sheet1").Range("J3:J & lngLastRow1).Value
ThisWorkbook.Sheets("GISSTadmin").Range("M3:M & lngLastRow1).Value = BLOCK.Sheets("Sheet1").Range("F3:F & lngLastRow1).Value
ThisWorkbook.Sheets("GISSTadmin").Range("N3:N & lngLastRow1).Value = BLOCK.Sheets("Sheet1").Range("G3:G & lngLastRow1).Value
ThisWorkbook.Sheets("GISSTadmin").Range("O3:O & lngLastRow1).Value = BLOCK.Sheets("Sheet1").Range("D3:D & lngLastRow1).Value
BLOCK.Close SaveChanges:=False

ThisWorkbook.Sheets("GISSTadmin").Range("P3:P & lngLastRow1).Value = IW66.Sheets("Sheet1").Range("J3:J & lngLastRow1).Value
ThisWorkbook.Sheets("GISSTadmin").Range("Q3:Q & lngLastRow1).Value = IW66.Sheets("Sheet1").Range("F3:F & lngLastRow1).Value
ThisWorkbook.Sheets("GISSTadmin").Range("R3:R & lngLastRow1).Value = IW66.Sheets("Sheet1").Range("G3:G & lngLastRow1).Value
ThisWorkbook.Sheets("GISSTadmin").Range("S3:S & lngLastRow1).Value = IW66.Sheets("Sheet1").Range("D3:D & lngLastRow1).Value
IW66.Close SaveChanges:=False

'Text to column code here

Sheets("GISSTadmin").Visible = False

MsgBox "Data refresh complete."

Application.ScreenUpdating = True

End Sub