Sub CriticalSummerList()
Dim wb1 As Workbook
Dim ws1 As Worksheet
Set wb1 = Workbooks("1")
Set ws1 = wb1.Sheets("Sheet1")
Set wb2 = Workbooks.Open("G:...xlsm")
Set ws2 = wb2.Sheets("1")
Set ws3 = wb2.Sheets("22")
ws3.Activate
Application.AskToUpdateLinks = False
ActiveWorkbook.RefreshAll
ws2.Activate
Set rng = [B1]
Set rng = Range(rng, Cells(Rows.Count, rng.Column).End(xlUp))
rng.TextToColumns Destination:=rng, DataType:=xlDelimited
With Range("b1").CurrentRegion
.Sort Key1:=.Range("b1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
Dim Lastrow As Integer
Lastrow = Cells(Rows.Count, "b").End(xlUp).Offset(0, 0).Row
With ws3
.Range("e2:l" & Lastrow).Copy
End With
wb1.Activate
ws1.Range("E3").Select
ws1.Range("E3").PasteSpecial xlPasteValues
ws3.Activate
With ws3
.Range("q2" & Lastrow).Copy
End With
wb1.Activate
ws1.Range("M3").Select
ws1.Range("M3").PasteSpecial xlPasteValues
ws3.Activate
With ws3
.Range("u2" & Lastrow).Copy
End With
wb1.Activate
ws1.Range("N3").Select
ws1.Range("N3").PasteSpecial xlPasteValues
ws3.Activate
With ws3
.Range("v2:AA" & Lastrow).Copy
End With
wb1.Activate
ws1.Range("O3").Select
ws1.Range("O3").PasteSpecial xlPasteValues
wb2.Close savechanges:=False
Application.CutCopyMode = False
Application.DisplayAlerts = False
Range("b1").Value = ".....- " & Date
End Sub
I'm getting an overflow error. I need to text to column B column on ws2 and then I am copying from ws2 e2 to L to last row and paste to wb1.ws1. Problem seems to arise when i try to texttocolumn B:B on ws2. I set the range as B1. Does anyone know what the problem is? Could it be that I am not assigning a variable to the range???