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

tbakbradley

Board Regular
Joined
Sep 24, 2010
Messages
126
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?
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,997
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

Board Regular
Joined
Sep 24, 2010
Messages
126
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.

Code:
[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

Board Regular
Joined
Sep 24, 2010
Messages
126
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,437
Office Version
365
Platform
Windows
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)?
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,437
Office Version
365
Platform
Windows
Here is a little snippet of code that show you one way to activate the open CSV file, and assign it to an object:
Code:
    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

Board Regular
Joined
Sep 24, 2010
Messages
126
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,437
Office Version
365
Platform
Windows
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:
Code:
    Dim XLwb As Workbook
    Set XLwb = ActiveWorkbook
 

tbakbradley

Board Regular
Joined
Sep 24, 2010
Messages
126
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:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,363
Messages
5,468,184
Members
406,569
Latest member
Quest_

This Week's Hot Topics

Top