0
    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???

alex
  • 11
  • 2

0 Answers0