Copy paste workbooks with text 2 column

cortexnotion

Board Regular
Joined
Jan 22, 2020
Messages
131
Office Version
  1. 2013
Platform
  1. Windows
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

VBA Code:
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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Watch MrExcel Video

Forum statistics

Threads
1,114,528
Messages
5,548,571
Members
410,852
Latest member
WernerS
Top