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

[JakPen]

Hello
I am just getting familiar with VBA macros

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

Is there a code that can simply pull rows based on cell value from one worksheet into another
For example, the entire row containing a cell value under the column year of 1961 and a cell in that same row with the value of ImportsPerCap or ExportsPerCap


Pretty much, I would only want in each worksheet (one worksheet for each year 1961-2010) filled in with all the rows from column C = to a certain year AND column D = to both ImportsPerCap and ExportsPerCap (column D values are populated with text: ImportsPerCap, ExportsPerCap, GDP, Production or Consumption; I just want Imports and Exports)


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!!!

[ashleyuk1984]

Yeah should be fairly simple to do.
If you can provide a sample workbook with some of the data (if not all) then we can work off that and achieve your end result for you.

[JakPen]

Hi there
I attached a section from Afghanistan to Argentina.....
These would account for probably nearly 500 worksheets alone
Pretty much, what I am hoping to end up with would be a worksheet for Afghanistan, its code and imports and exports for 1961;
another for Afghanistan, its code and imports and exports for 1962; ...and so on, for each year for each country
I am so glad that I found out this can even be done by code b/c manually would be an exhausting endeavor in itself!!!
Thank-You SO VERY much for your help!!!

[ashleyuk1984]

You could achieve this by using the AutoFilter function.... But just incase there was a genuine reason behind you wanting seperate sheets... This is what I've come up with.

Sub Data()
Dim LastRow As Integer

Sheets(1).Name = "Data"

LastRow = Range("D1048576").End(xlUp).Row

For x = 1 To LastRow


If Range("D" & x).Value = "EFImportsPerCap" Then

    'Add Sheet and give name (Country and Year)
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = Sheets(1).Range("A" & x).Value & " " & Sheets(1).Range("C" & x).Value
    
    'Copy data from main sheet and place it into newly created sheet
    Sheets(1).Range("D" & x, "E" & x).Copy
    Range("A1").PasteSpecial xlPasteValues
    
    'This should grab all the Export information. I took a look at a few years and found that the Exports are two rows below the imports.. So I'll just use an offset to grab that aswell.
    Sheets(1).Range("D" & x, "E" & x).Offset(2, 0).Copy
    Range("A2").PasteSpecial xlPasteValues
    
    Cells.EntireColumn.AutoFit
    Range("A1").Select
    
    Sheets(1).Select
End If

Next x

End Sub

Delete Sheets 2 and 3... And then run the code.

You'll be left with Sheets looking like this:



And the data in each sheet will look like this.



I hope this is what you want.

[JakPen]

Hi I really am very green with this stuff and am not sure where to input my variables
After seeing the results from your run of the code, I realize that I actually would want a separate worksheet or file for all countries but for each year....
So, the columns would be:
COUNTRY CODE RECORD TOTAL
One sheet or file with all countries for 1961, one for 1962, 1963, and so on....
But the record column would only pull results for "EFProdPerCap" and "EFConsPerCap"

I'm sorry, I think I did give a different variable above, but if I knew where in the code to specify for it, I think I could make it work either way

Again, I can't tell you how much I appreciate your help!!
Thank-You so much AshleyUK!!

[ashleyuk1984]

Ok, here you go. It's a little bit more code. I've tried to add details to the code, you'll see this as GREEN text when you insert it into your module.

Sub Data()    
    Dim LastRow As Integer
    Dim WorkingYear As String
    Dim ws As Variant

    'Name first sheet
    Sheets(1).Name = "Data"
    
    'Find last row, this indicates how much data we have to process
    LastRow = Range("D1048576").End(xlUp).Row
    
    'FOR Loop, from 1 to Last Row
    For x = 1 To LastRow
        
        'Check D1 / D2 / D3 etc for EFProdPerCap, if found then continue, otherwise go to next row
        If Range("D" & x).Value = "EFProdPerCap" Then
            
            'Set variable for Year
            WorkingYear = Range("C" & x).Value
            
            'Check if Year Worksheet has already been created or not
            For Each ws In ActiveWorkbook.Worksheets
            If ws.Name = WorkingYear Then
            ws.Select
            GoTo continue: '<---- I'm usually against GoTo, but this is probably the easiest way for now
            End If
            Next ws
            
             'Add Sheet and give name (Year)
            Sheets.Add After:=Sheets(Sheets.Count)
            ActiveSheet.Name = WorkingYear
             
             'Give new sheet column headers
            Range("A1").Value = "COUNTRY"
            Range("B1").Value = "CODE"
            Range("C1").Value = "RECORD"
            Range("D1").Value = "TOTAL"
             
continue:
             
             'Copy data from main sheet and place it into newly created sheet - (Part 1)
            Sheets(1).Range("A" & x, "B" & x).Copy
            
            'Find Next avaliable row and paste data - (Part 1)
            Range("A1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            
            'Copy data from main sheet and place it into newly created sheet - (Part 1)
            Sheets(1).Range("D" & x, "E" & x).Copy
            
            'Find Next avaliable row and paste data - (Part 1)
            Range("C1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
                        
             'This should grab all the EFConsPerCap information. Just using an offset to grab this information - (Part 2)
            Sheets(1).Range("A" & x, "B" & x).Offset(6, 0).Copy
            Range("A1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
             
             'This should grab all the EFConsPerCap information. Just using an offset to grab this information - (Part 2)
            Sheets(1).Range("D" & x, "E" & x).Offset(6, 0).Copy
            Range("C1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            
             'Autofit columns
            Cells.EntireColumn.AutoFit
            Range("A1").Select
             
            Sheets(1).Select
        End If
         
    Next x
     
End Sub

Again, delete Sheets 2 and 3... And then run the code.

You'll be left with Sheets looking like this:



And the data in each sheet will look like this.



Again, I hope this is what you want.

[JakPen]

Hello
I'm not sure if I am doing this right....
I hit alt+f11
in a new module pasted the code
in this line:
'Set variable for Year
WorkingYear = Range("C" & x).Value
I replaced the "C" with the year, i.e. "1961"
Went back to the worksheet, hit alt+f8 and selected the Macro name to run
I don't think it worked....
I also tried to change other values throughout on different runs to the year I was trying to pull the data for but no luck~
I'm sorry if I am missing something obvious, I really truly am quite new to this and the extent I've done anything like this in excel thus far has just been by recording macros and then replaying with a keyboard shortcut
Thanks again for taking the time to help!!

[ashleyuk1984]

There are no modifications needed.
You don't need to change "C" to "1961".

Range("C" & x) means Column C, row "x" (Whatever x might be), could be C1 / C2 / C3 etc etc.

[JakPen]

So I tried it out on the original file I sent you and it worked perfectly, but when I tried it out on the actual full file, all it did was create another sheet named data without the years pulled separately
I couldn't attach the actual file here, maybe b/c it's too big?
Hoping that it might be something your expert eye can easily pick out though, it can be accessed where I uploaded it here:
https://app.box.com/s/en5jeqiexy2kjmd6loz6
Sorry for so much trouble but I am really grateful for the help!!!

[ashleyuk1984]

Ahhh, it's because there is soooooooo many rows.
Declaring LastRow as Integer will only allow the row number to go up to: 32,767

As there is 99697 rows, VBA produces a OVERFLOW error. We have to declare this as another type of variable, "LONG".
Long, will effectively allow you to store a number up to: 2,147,483,647 (although there isn't that many rows in Excel anyway).

So, just change the first row to look like this:

Dim LastRow As Long

And the code will run
Please be patient while it runs, it takes a bit of time to process all the data.

(I've managed to write all this, and it's still running lol) Go make a cup of coffee or something :P

[JakPen]

You are an Amazing Genius AshleyUK!!!
This saved me SO much time and effort!!!
Thank-You! Thank-You! Thank-You