Copying Specific Columns Into A New Worksheet

  • I have a file that has various numbers of columns, some of which i need, some of which i do not. Under these columns, there are various rows, sometimes 50, sometimes 400. I need to write something that copies only the specific columns I need and pastes it into a new worksheet. For example, in the data file there is are columns property type, loan balance current, and amortization type. I need them to be copied with the information in the rows below them to a new worksheet, to eliminate the unnecessary information in the data file.

  • Re: Copying Specific Columns Into A New Worksheet


    what about copying all cells into a new sheet and then deleting what you don't need? You can start by recording a macro that does this, then edit it if needed.

  • Re: Copying Specific Columns Into A New Worksheet


  • Re: Copying Specific Columns Into A New Worksheet


    Ok so brouz... i can't do it that way because when you record the macro, it only realizes the exact columns i choose, so for example if property name is in column B in one data sheet and i record the whole macro, it will only work if property name is always in column B... but this is not the case, some times is column b sometimes c etc....


    Dave Hawley... I tried to use the VB code you suggested and nothing happens.. Am i supposed to edit this in some way... I am fairly new to this VB stuff so any explanation might help

  • Re: Copying Specific Columns Into A New Worksheet


    Using Dave's code, plug in your field names at this line in place of Accounts, Name, Age (those were examples):


    Code
    strFind = Choose(lLoop, "Accounts", "Name", "Age")


    With that, based on your first post, it might look something like this:


    and, furthermore, if you are looking for more than 3 columns, you'll need to adjust this line:


    Code
    For lLoop = 1 To 3 'Adjust to suit


    try that out and if you can't get it to work. Attach an example workbook with your code in it.

  • Re: Copying Specific Columns Into A New Worksheet


    Ok so I did exactly what you suggested and the macro doesn't run at all.. nothing happens.. I have the code from above, with the different names of the columns and changed the number from 3 to 27 (thats how many columns i need) and nothing happens at all... can you see a reason why the macro won't run at all?

  • Re: Copying Specific Columns Into A New Worksheet


    I would love to but the files are too big... the site won't let me attach anything bigger than 48kb... these files are like 480kb... unless there is another way to do it i don't know of

  • Re: Copying Specific Columns Into A New Worksheet


    just carve out an example. leave all columns, but maybe only 20 rows or so.

  • Re: Copying Specific Columns Into A New Worksheet


    i cut out everything in the entire worksheet except for the column headers and its still 400+ kb.. not sure what else i can do.. very frustrating

  • Re: Copying Specific Columns Into A New Worksheet


    It works for me. I attached an example illustrating that Dave's code works flawlessly. This places the 3 example columns in Columns B, C, D of Sheet 2.


    note that this code assumes that you are taking data from Sheet1 and placing it in Sheet2, if that's not the case, then a minor tweak will fix it.

  • Re: Copying Specific Columns Into A New Worksheet


    I see that this example seems to work... I can't tell you why it doesnt work for my data file.... the example is a very straightforward.. my data is much more complex, maybe that is why it doesn't seem to work.. ill mess around with it and see what I can do... thanks though

  • Re: Copying Specific Columns Into A New Worksheet


    I was able to break my file into two seperate files.... if you can, just copy and past "part2" next to the last blank column in "part1"... once you do this, it will be the complete data worksheet I am dealing with.. maybe you can figure out why or how I can solve this problem now b/c i have tried and cannot do it... thanks


    forum.ozgrid.com/index.php?attachment/29340/


    forum.ozgrid.com/index.php?attachment/29341/

  • Re: Copying Specific Columns Into A New Worksheet


    I pasted the 2 together... now, excactly which columns are you trying to copy to another sheet? if i have the list, i'll give it a try.

  • Re: Copying Specific Columns Into A New Worksheet


    I need the column names and all the data below them to be pasted as well... good luck...
    "PropertyName"
    "Watchist"
    "Loan Balance Secur"
    "Loan Balance Current"
    "% of deal"
    "Current Rate"
    "Orig IO Per"
    "Cross Coll #"
    "Amortization Type"
    "Day Count Basis"
    "Defeased Status"
    "Dlq Status"
    "# Times Known Dlq 12 Months"
    "# Times Known Dlq Loan Life"
    "State"
    "NormalizedProp Type"
    "Maturity Date"
    "NOI/NCF and DSCRs NOI/NCF"
    "NOI/NCF and DSCRs Secur NOI"
    "NOI/NCF and DSCRs Secur NCF"
    "NOI/NCF and DSCRs DSCR"
    "NOI/NCF and DSCRs Secur DSCR (NOI)"
    "NOI/NCF and DSCRs Secur DSCR (NCF)"
    "Appraisal and LTVs LTV"
    "Appraisal and LTVs Secur LTV"
    "Occupancy Rate"
    "Occupancy Secur Occ"
    "Year Built"

  • Re: Copying Specific Columns Into A New Worksheet


    OK...so... i *think* (not 100% sure) that the problem is with Carriage Returns in the header text. I cleaned them up using code from Jack in the UK, i found on Here: http://www.ozgrid.com/forum/showthread.php?t=39246


    I then adapted Dave's code again using your criteria. The code definitely works, i think the issue is the data you are importing. Is this coming in as a CSV file? Anyway, note also that this carriage return cleanup deleted spaces, so i modified your headers (example "Year Built" became "YearBuilt")


    This code is copying columns from "Part1" to "Sheet2", modify your sheet names as needed:


  • Re: Copying Specific Columns Into A New Worksheet


    Ok I will try this code and see how it works... the data is coming in as a CSV file and I have to convert it to an excel file. just to be clear though, you say i have to use a different code first to "clean it up" and then use this code?[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]I just tried using this code and nothing happens at all... the macro doesnt even run once i open my data sheet.. what is the code for "cleaning up" the carraige returns or whatever??

  • Re: Copying Specific Columns Into A New Worksheet


    for Data Cleanup, i noticed when i cleared all formats from your sheet (or you can un-wrap the header line) and you'll see the little boxes. That made me wonder if the macro could not find the exact match because of these carriage returns.


    i posted the hyperlink above, from the thread "Dealing with Carriage Returns" - read it for reference, and the code is below. You add a new header line and use this function.



    also, Comment the 3rd line of the procedure so that it will show the runtime errors if they occur:


    Code
    'On  Error Resume Next
  • Re: Copying Specific Columns Into A New Worksheet


    if you change

    Code
    myReplaceWith = "" ' jiuk - edit as needs be

    in Jack's code you can replace the naughty characters with anything you like (as is fairly clearly indicated, in all fairness).

  • Re: Copying Specific Columns Into A New Worksheet


    Charlie,


    Do you mean here... i tried, but it did not work


    Code
    vResult = Application.WorksheetFunction.Substitute(vResult, Chr(10), myReplaceWith = "*")

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!