Help Clean Up Code

[sneakybeakylike]

Hey Y'all!

I recently wrote this bit of code as part of a macro. It essentially opens a workbook, copies individual columns and puts them in different places in another workbook. However, this is slightly time consuming. Is there any way I could clean this up somehow? Thanks!

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

    Dim myFile As Variant
    Dim OpenBook As Workbook
    
    Application.ScreenUpdating = False
    
' creates new sheet to format data in

    Sheets.Add.Name = "Temporary Formatting Sheet"
    
' opens window to browse for data

    myFile = Application.GetOpenFilename(, , "Browse for Data")
      If myFile <> False Then
      Set OpenBook = Application.Workbooks.Open(myFile)
 
' copy and paste selected data
    
    Set wsCopy = OpenBook.ActiveSheet
    Set wsDest = ThisWorkbook.ActiveSheet

    lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "E").End(xlUp).Row
    lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "E").End(xlUp).Row
    
    wsCopy.Range("A2:A" & lCopyLastRow).Copy _
    wsDest.Range("E" & lDestLastRow)
    
    wsCopy.Range("L2:L" & lCopyLastRow).Copy _
    wsDest.Range("F" & lDestLastRow)

    wsCopy.Range("Q2:Q" & lCopyLastRow).Copy _
    wsDest.Range("G" & lDestLastRow)
    
    wsCopy.Range("R2:R" & lCopyLastRow).Copy _
    wsDest.Range("Q" & lDestLastRow)
    
    wsCopy.Range("T2:T" & lCopyLastRow).Copy _
    wsDest.Range("R" & lDestLastRow)
    
    wsCopy.Range("B2:B" & lCopyLastRow).Copy _
    wsDest.Range("S" & lDestLastRow)
    
    wsCopy.Range("C2:C" & lCopyLastRow).Copy _
    wsDest.Range("T" & lDestLastRow)
    
    wsCopy.Range("D2:D" & lCopyLastRow).Copy _
    wsDest.Range("U" & lDestLastRow)
    
    wsCopy.Range("E2:E" & lCopyLastRow).Copy _
    wsDest.Range("V" & lDestLastRow)
    
    wsCopy.Range("F2:F" & lCopyLastRow).Copy _
    wsDest.Range("W" & lDestLastRow)
    
    wsCopy.Range("G2:G" & lCopyLastRow).Copy _
    wsDest.Range("X" & lDestLastRow)
    
    wsCopy.Range("H2:H" & lCopyLastRow).Copy _
    wsDest.Range("Y" & lDestLastRow)
    
    wsCopy.Range("I2:I" & lCopyLastRow).Copy _
    wsDest.Range("Z" & lDestLastRow)
    
    wsCopy.Range("J2:J" & lCopyLastRow).Copy _
    wsDest.Range("AA" & lDestLastRow)
    
    wsCopy.Range("K2:K" & lCopyLastRow).Copy _
    wsDest.Range("AB" & lDestLastRow)
    
    wsCopy.Range("M2:M" & lCopyLastRow).Copy _
    wsDest.Range("AC" & lDestLastRow)
    
    wsCopy.Range("N2:N" & lCopyLastRow).Copy _
    wsDest.Range("AD" & lDestLastRow)
    
    wsCopy.Range("O2:O" & lCopyLastRow).Copy _
    wsDest.Range("AE" & lDestLastRow)
    
    wsCopy.Range("P2:P" & lCopyLastRow).Copy _
    wsDest.Range("AF" & lDestLastRow)
    
    wsCopy.Range("S2:S" & lCopyLastRow).Copy _
    wsDest.Range("AG" & lDestLastRow)
    
    wsCopy.Range("U2:U" & lCopyLastRow).Copy _
    wsDest.Range("AH" & lDestLastRow)
        
    wsCopy.Range("V2:V" & lCopyLastRow).Copy _
    wsDest.Range("AI" & lDestLastRow)
    
    wsCopy.Range("W2:W" & lCopyLastRow).Copy _
    wsDest.Range("AJ" & lDestLastRow)
    
    wsCopy.Range("X2:X" & lCopyLastRow).Copy _
    wsDest.Range("AK" & lDestLastRow)
        
    wsCopy.Range("Y2:Y" & lCopyLastRow).Copy _
    wsDest.Range("AL" & lDestLastRow)
        
    wsCopy.Range("Z2:Z" & lCopyLastRow).Copy _
    wsDest.Range("AM" & lDestLastRow)
        
    wsCopy.Range("AA2:AA" & lCopyLastRow).Copy _
    wsDest.Range("AN" & lDestLastRow)
        
    wsCopy.Range("AB2:AB" & lCopyLastRow).Copy _
    wsDest.Range("AO" & lDestLastRow)
        
    wsCopy.Range("AC2:AC" & lCopyLastRow).Copy _
    wsDest.Range("AP" & lDestLastRow)
    
    wsCopy.Range("AD2:AD" & lCopyLastRow).Copy _
    wsDest.Range("AQ" & lDestLastRow)
    
    wsCopy.Range("AE2:AE" & lCopyLastRow).Copy _
    wsDest.Range("AR" & lDestLastRow)
    
    wsCopy.Range("AF2:AF" & lCopyLastRow).Copy _
    wsDest.Range("AS" & lDestLastRow)
    
    wsCopy.Range("AG2:AG" & lCopyLastRow).Copy _
    wsDest.Range("AT" & lDestLastRow)
    
    wsCopy.Range("AH2:AH" & lCopyLastRow).Copy _
    wsDest.Range("AU" & lDestLastRow)
    
    wsCopy.Range("AI2:AI" & lCopyLastRow).Copy _
    wsDest.Range("AW" & lDestLastRow)
    
      OpenBook.Close
   
    End If

 

[mikerickson]

One thing I see is that you could consolidate many of the contiguous columns into one copy paste

wsCopy.Range("U2:AI" & lCopyLastRow).Copy destination:=wsDest.Range("AH" & lDestLastRow)

 

[sneakybeakylike]

One thing I see is that you could consolidate many of the contiguous columns into one copy paste

wsCopy.Range("U2:AI" & lCopyLastRow).Copy destination:=wsDest.Range("AH" & lDestLastRow)

That definitely helped, thanks!