Copying selected columns in background and pasting values in a separate sheet

[sahaider]

Hi All,

I have the following code that opens a csv file in background, copies everything between Cell A1 and BF from that csv file and then pastes in a a separate sheet.

I would like to modify this code so that it copes only Column A and BF from the csv file that is opened in the background , and then paste the copied contents in a separate sheet. The row count of these two columns is dynamic.
Any other columns in between A and BF can be ignored from copy paste operation

Any guidance would be apprecaied.

FileToOpen = Application.GetOpenFilename(Title:="Browse for the latest TX Data", fileFilter:="CSV Files(*.csv),*csv*")
    If FileToOpen <> False Then
    Set Openbook = Application.Workbooks.Open(FileToOpen)
    
    With Openbook.Sheets(1)
           .Range("A1", .Range("BF" & Rows.Count).End(xlUp)).Copy
    End With
    
    
    ThisWorkbook.Worksheets("Vendor Swap Data").Range("A1").PasteSpecial xlPasteValues
    Openbook.Close False

 

[Sektor]

I have made your code more dynamic. In arrays arrSourceCols and arrTargetCols you write down source and target columns in their respective positions.

Sub CopyOnlyAandBF()

    Dim arrSourceCols, arrTargetCols, col%

    FileToOpen = Application.GetOpenFilename(Title:="Browse for the latest TX Data", fileFilter:="CSV Files(*.csv),*csv*")
    If FileToOpen <> False Then
        Set Openbook = Application.Workbooks.Open(FileToOpen)
    Else
        Exit Sub
    End If
  
    arrSourceCols = Array("A", "BF")
    arrTargetCols = Array("A", "B")
  
    With Openbook.Sheets(1)
        For col = 0 To UBound(arrSourceCols)
           .Range(.Cells(1, arrSourceCols(col)), .Cells(.Rows.Count, arrSourceCols(col)).End(xlUp)).Copy
           ThisWorkbook.Worksheets("Vendor Swap Data").Cells(1, arrTargetCols(col)).PasteSpecial xlPasteValues
        Next
        
    End With
End Sub

 

[HighAndWilder]

And another approach.

Put this code into a standard code module.

I'm not sure where you want to run it from but to test it on a copy of your workbook place your cursor into the
CopyData procedure and press F5.

Option Explicit

Public Sub CopyData()
Dim rngData As Range
Dim FileToOpen As Variant
Dim WsActive As Worksheet
Dim Openbook As Workbook
Dim strFileName As String

    Set WsActive = ActiveSheet
    
    ' You may not want to clear all of the target sheet.
    ActiveSheet.Cells.Clear
    
    FileToOpen = Application.GetOpenFilename(Title:="Browse for the latest TX Data", fileFilter:="CSV Files(*.csv),*csv*")
    
    If FileToOpen <> False Then
        Set Openbook = Application.Workbooks.Open(FileToOpen)
    Else
        Exit Sub
    End If
    
    strFileName = Openbook.FullName
       
    Set rngData = ActiveSheet.Range("A1").Resize(ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row, 1)
    WsActive.Range("A1").Resize(rngData.Rows.Count, rngData.Columns.Count) = rngData.Value

    Set rngData = ActiveSheet.Range("BF1").Resize(ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row, 1)
    WsActive.Range("B1").Resize(rngData.Rows.Count, rngData.Columns.Count) = rngData.Value

    Openbook.Close False
    
    MsgBox "Data has been copied from " & strFileName, vbInformation, "Confirmation"

End Sub