VBA - Exclude columns from copy/paste

[sparkytech]

I have the following code below that is currently working, but I need to tweak it. I need to modify this code to copy only columns A:W and Z:Z from the source worksheet (exclude columns X:Y, as the destination worksheet does not contain columns X:Y) and paste A:W to A:W, Z:Z to X:X to the destination worksheet.

In other words:
Worksheet 1 (source of copied data) copy columns A:W, Z:Z
Worksheet 2 (destination of copied data) paste columns A:W to A:W, paste column Z:Z to X:X

Can someone help me tweak this?

Sub ExportCleanExcel(control As IRibbonControl)

'Exports the information in the master sheet to a chosen excel file; will just move 'the data without macros or other extra features

Dim FileToOpen As Variant
Dim DestWkb As Workbook
Dim MasBotRow As Long
Dim MasLasCol As Long

'Turn off screen updates to improve performance
Application.ScreenUpdating = False

'Allows user to select a file to export to using the traditional open file window
FileToOpen = Application.GetOpenFilename("All Excel Files (*.xls?), *.xls?", , "Please export file")

'If the user selects cancel when choosing a file to open FileToOpen is set to FALSE
'Using this we can check if a file was actually selected before continuing
If FileToOpen <> False Then

'Find the bottom row and last column of the table on the master sheet
MasBotRow = Sheet1.Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
MasLasCol = Sheet1.Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column

'Copies the entire table from the master sheet (NEED TO EXCLUDE columns X:Y)
Sheet1.Range(Cells(5, 1), Cells(MasBotRow, MasLasCol)).Copy

'Open the file that was selected and then set that workbook as DestWkb
Set DestWkb = Application.Workbooks.Open(FileToOpen)

'Pastes the table into the destination file in the A5 cell of the first sheet
DestWkb.Sheets(1).Cells(5, 1).PasteSpecial

'Turn off alerts to avoid clipboard notices when closing the file
Application.DisplayAlerts = False

'Close the data file
DestWkb.Close True

'Turn back on alerts after closing the file
Application.DisplayAlerts = True

End If

'Turn back on screen updates
Application.ScreenUpdating = True

End Sub

 

[DanteAmor]

Change this line:

Sheet1.Range(Cells(5, 1), Cells(MasBotRow, MasLasCol)).Copy

For this:

Sheet1.Range("A1:W" & MasBotRow & ",Z1:Z" & MasBotRow).Copy

 

[sparkytech]

Change this line:

Sheet1.Range(Cells(5, 1), Cells(MasBotRow, MasLasCol)).Copy

For this:

Sheet1.Range("A1:W" & MasBotRow & ",Z1:Z" & MasBotRow).Copy

Thanks! That worked great with a few simple mods:

Sheet1.Range("A5:W" & MasBotRow & ",Z5:Z" & MasBotRow).Copy

DestWkb.Sheets(1).Cells(5, 1).PasteSpecial xlPasteValues

I forgot to mention I needed the data to paste starting at row 5, and paste values only. Thanks for the help!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.