Copy Certain Columns from an open .CSV file to another open Worksheet

[tbakbradley]

Is there a method that would allow me to write a Macro on a Workbook (always a different file name) so that when I activate it, it copies specific columns from an opened .CSV File (always with a different name as well), and pastes it into different columns on the Workbook?

.xlsm file with macro. When initiated, it will pull copy columns A, B and C from the .CSV file and past them into "Sheet 2" of the open .xlsm file under Columns M, N and O?

 

[oldbrewer]

you need to ensure the macro is always available - in excel 2000 which I use I save as personal macro files

I would copy the whole of the csv file to sheet 3 of the .xlsm file then it is straight forward to grab the first 3 columns and paste them into sheet 2 cols M,N,O

 

[tbakbradley]

Is there a method that would allow me to write a Macro on a Workbook (always a different file name) so that when I activate it, it copies specific columns from an opened .CSV File (always with a different name as well), and pastes it into different columns on the Workbook?

.xlsm file with macro. When initiated, it will pull copy columns A, B and C from the .CSV file and past them into "Sheet 2" of the open .xlsm file under Columns M, N and O?

I once had this, but not sure how to handle this if the Source file is .CSV. Macro will be located on the "Target" and Take columns A, B and C from the "Source"= CSV File and past it into the Target .xlsm file.

[COLOR=#333333]Public Sub CopyColumnsSourceToTarget()[/COLOR]
Dim sourceBook As Workbook
Dim targetBook As Workbook
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet

' Must have exactly 2 workbooks open
If Workbooks.Count <> 2 Then
    MsgBox "There must be exactly 2 workbooks open to run the macro", vbCritical + vbOKOnly, "Copy Columns From Source To Target"
    Exit Sub
End If

' Set the source and target workbooks
Set targetBook = ActiveWorkbook
If Workbooks(1).Name = targetBook.Name Then
    Set sourceBook = Workbooks(2)
Else
    Set sourceBook = Workbooks(1)
End If

' Set up the sheets
Set sourceSheet = sourceBook.ActiveSheet
Set targetSheet = targetBook.ActiveSheet

' Copy the columns
sourceSheet.Range("A1").EntireColumn.Copy Destination:=targetSheet.Range("M1")
sourceSheet.Range("B1").EntireColumn.Copy Destination:=targetSheet.Range("N1")
sourceSheet.Range("C1").EntireColumn.Copy Destination:=targetSheet.Range("O1")


[COLOR=#333333]sourceSheet.Range("AP1").Select[/COLOR]

 

[tbakbradley]

you need to ensure the macro is always available - in excel 2000 which I use I save as personal macro files

I would copy the whole of the csv file to sheet 3 of the .xlsm file then it is straight forward to grab the first 3 columns and paste them into sheet 2 cols M,N,O

That would work for me. But I'm not sure how to pull the data from an Opened CSV file into a Worksheet on my .xlsm file with the Macro. Both .CSV and .XLSM will always be named something different, never the same.

 

[Joe4]

Is the CSV opened in the same session of Excel as the Excel file?
And will it be the ONLY CSV open in the same Excel session (there shouldn't be more than one at a time)?

 

[Joe4]

Here is a little snippet of code that show you one way to activate the open CSV file, and assign it to an object:

    Dim wb As Workbook
    Dim CSVwb As Workbook


    For Each wb In Application.Workbooks
        If UCase(Right(wb.Name, 3)) = "CSV" Then
            Windows(wb.Name).Activate
            Set CSVwb = ActiveWorkbook
            Exit For
        End If
    Next wb

 

[tbakbradley]

Is the CSV opened in the same session of Excel as the Excel file?
And will it be the ONLY CSV open in the same Excel session (there shouldn't be more than one at a time)?

Yes....just two Excel Files open. one CSV and one XLSM.

 

[Joe4]

Yes....just two Excel Files open. one CSV and one XLSM.

OK. See if the code snippet I provided in my last post gives you what you need.
The key is to capture each workbook in a workbook object. Then it is easy to bounce back and forth between then two using the workbook object (regardless of what the names are).

Note, since the code will be in the Excel (xlsm) workbook, it is easy to capture that in a workbook object by putting this code at the very beginning of your code:

    Dim XLwb As Workbook
    Set XLwb = ActiveWorkbook

 

[tbakbradley]

OK. See if the code snippet I provided in my last post gives you what you need.
The key is to capture each workbook in a workbook object. Then it is easy to bounce back and forth between then two using the workbook object (regardless of what the names are).

Note, since the code will be in the Excel (xlsm) workbook, it is easy to capture that in a workbook object by putting this code at the very beginning of your code:

    Dim XLwb As Workbook
    Set XLwb = ActiveWorkbook

Thanks. I'll play around with the code I provided with the Objects you provided and see how it goes and post back tomorrow.