How to add row into another worksheet based on multiple cell values? RRS feed

  • Question

  • Hello

    I am just getting familiar with VBA and the whole macro language

    I have thousands of entries with columns for countries, year, numeric values and more

    Is there a code that can simply pull from one worksheet into another, for example, the entire row containing a cell value of 1961 and a cell with the value of ImportsPerCap or ExportsPerCap

    Pretty much, I would only want in each worksheet, for example one for each year 1961-2010, all the rows from column C = to a certain year AND column D = to both ImportsPerCap and ExportsPerCap

    Any help with some code for starters or even learning resources where I can learn about doing this would be greatly appreciated!!

    Thank-You So Much!!!

    • Edited by JakPen Thursday, March 20, 2014 2:27 AM
    Thursday, March 20, 2014 2:19 AM

All replies

  • As this is so far unanswered, I can offer some guidance.

    Though you clearly have a huge amount of data, the solution should be fairly straightforward.  There is not enough information on just how your sheet is laid out to give useful code, but I would suggest that your best bet would be to start with the Macro recorder to show the code for filtering to the data that you want and for copying and pasting it to the relevant worksheet.  The Macro recorder generally uses absolute values for ranges and selections, so you will need to set variables for the row numbers and for the worksheet titles.  For example, the row with the bottom entry in column A of a sheet can be set as a variable thus:

    lngFinalRow = Cells(Rows.Count,1).End(xlUp).Row

    If your worksheet names are the year numbers, then you can set a variable that will be used in your filter for the years in column C and also for the worksheet names, and use it in a For Next loop to step through the whole workbook.  With 50 years and presumably 50 sheets, it may take a while!

    If you want to post a mini example of your sheet, I will be happy to try to give a more specific reply.

    Andy C

    Friday, April 4, 2014 10:14 AM