Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Linking columns from one sheet to another sheet

0

What is the best way to copy or extract columns from one sheet to another sheet?

My web site back end creates a transaction export csv file but it has 240 columns and I only need information from 10 columns.  The back end does not allow column selection. 

I created a new Excel file with three sheets. Summary, Filtered and Data.    The CSV file will be updated and downloaded often with new data so number of rows will increase.

I imported the CSV file into the Data Sheet.    From the Data Sheet I highlighted and copied each column I wanted to view and pasted the link into the Filtered Sheet.    Then created a Summary sheet with totals etc. 

The spreadsheet worked great except, the file size was over 115 MB.  This file size caused a very slow start.  I couldn't figure out the issue until I unfiltered the Filtered Sheet and had 1,048,576 rows and any unused cells had  0's.   I deleted the rows  with 0's and the file size was reduced to 2.6 MB.  But, as soon as the CSV file was refreshed all the 0 rows returned.    My CSV file does not have the additional rows with zero they only appear in my filtered sheet.

I receive suggestions from this site but was advised to resubmit my question since the issue seems to be how I copied each column and pasted the link from one sheet to another sheet.

I've tried many other steps but still stuck.  Any help would be greatly appreciated.

Thank you

excel
MS Office Version: 2013
posted by: Why Me (rep: 4) on Tue Jun 5, 2018 at 1:19 pm
Answer
Discuss

Discussion

I looked for your earlier question at this link. Please re-read the last comment in the Discussions section under the answer, as I did. You seem to have three solutions. Therefore your immediate problem is one of choice. I suggest you choose one of them, the one that appeals most to you judging by the description, and report the problem you encounter, if any. Use the old thread to finalise the resolution of unwated zeroes. Use a new thread to ask a new question which is different from the previous.
Variatus (rep: 4889) Jun 5, '18 at 9:15 pm
Add to Discussion

Answers

0

Hi All

My solution for getting selected data from a CSV to excel is to use a macro to read the file row by row, split it into the columns, apply my selection rules and write them to a sheet.

I have attached a sample CSV and Excel to show this.

You should be able to enter the location of any CSV file on the Excel and press the button and the data will on the data sheet.

If you are using my file you will see a heading on the "actions" sheet  "include" with a highlighted cell under it. If you copy the values below this into this cell and press the button again then the data list will only contain those items.

This is only a selection of techniques available to you. 

It looks like you need to refresh this data each time you open the workbook. You could add an on open workbook action

posted by: k1w1sm (rep: 197) on Wed Jun 6, 2018 at 10:17 pm
Discuss

Discussion

Hi k1w1sm (rep
Thanks for the information.  I would like to explore your option but I did not find any files to download.    Can you reload them or tell me where to download.
Thanks 
Why Me (rep: 4) Jun 7, '18 at 5:39 pm
Hi Variatus
I am exploring your options again.  I will update you soon.  
Thanks
Why Me (rep: 4) Jun 7, '18 at 5:41 pm
Still getting to grips with this site.
I may have been unable to load the CSV and XL at the same time.
I have just added the Excel again now.
And I have added the CSV but saved as an XL. Just save it back to a CSV.

I also omitted the requirement around only selecting 10 of the 240 columns 
It can be done in this area of the code.
           ' write the rows
           If WriteRecord Then
                For i_dataBit = 0 To UBound(sp) ' replace this loop with just the columns to be used
                    Sheets(OutSheet).Cells(i_Data_Row, i_Data_Col) = sp(i_dataBit)
                    i_Data_Col = i_Data_Col + 1
                Next i_dataBit
                i_Data_Col = 1 ' Move back to col 1 ready for next row
                i_Data_Row = i_Data_Row + 1 ' Move to next row
            End If
k1w1sm (rep: 197) Jun 7, '18 at 6:50 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login