Solved VBA excel question

October 28, 2011 at 17:18:46
Specs: Windows 7
I am very new to VBA and am just understanding how to use it. Within my workbook, I have a column of unique company names and corresponding columns of data for all companies in spreadsheet 1. For Instance, within spreadsheet 1, company ABC, Inc. may be listed 5 times with different SEC filing dates and revenue figures associated with it.

Spreadsheet 1:

A	        B	        C
Company Name	Filing Date	Revenue
ABC Inc.	1/1/2000	1234
FGH Inc.	4/14/2008	5678
DEF Inc.	4/6/2005	5664
ABC Inc.	7/7/1999	1356
XYZ Inc.	5/8/2010	6456
ABC Inc.	5/8/2010	3213
FGH Inc.	1/23/2005	8546
FGH Inc.	4/5/2006	2133
ABC Inc.	12/25/2010	5466
ABC Inc.	11/13/2010	5465

In spreadhseet 2, cell D1 I have a de-duplicated drop down list of all the company names in spreadsheet 1. Also In spreadsheet 2 is a row of identical headers as exists in spreadsheet 1:

Spreadsheet 2:

A	        B	        C	D
Company Name	Filing Date	Revenue {Drop Down List}	

Ideally, the macro would recognize ABC Inc. from the drop down list in D1 and populate the company's data in columns A, B, and C in Spreadsheet 2:

Spreadsheet 2 (After the macro is run):

A	        B	        C	D
Company Name	Filing Date	Revenue ABC, Inc
ABC Inc.	1/1/2000	1234	
ABC Inc.	7/7/1999	1356	
ABC Inc.	5/8/2010	3213	
ABC Inc.	12/25/2010	5466	
ABC Inc.	11/13/2010	5465	

I have been filtering manually to get these same results but this is very time consuming considering that I do this many times per day for many companies. Any help to get me off the ground with VBA would be great!

Thanks,
Harry


See More: VBA excel question

Report •


#1
October 28, 2011 at 18:26:13
re: "I have a de-duplicated drop down list"

de-duplicated?

Do you mean that you have a list of unique values in your drop down?

Anyway, try this Change Event code.

Note: You didn't say whether you had any other data in the sheets other than in Columns A:C so, as written, the code copies and pastes the entire row from Sheet 1 to Sheet 2. If this is going to copy data from Sheet 1 that you don't want, or overwrite data in Sheet2 that it shouldn't, then you'll need to limit the range that gets copied/pasted.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim last_1Rw, last_2Rw, nxt_1Rw, nxt_2Rw As Integer
'Check to see if a change was made to the Drop Down cell
 If Target.Address = "$D$1" Then
'If yes...
'Disable events so the code doesn't fire when the chagesa re made
  Application.EnableEvents = False
'Determine last Row of data in Sheet 2 and clear data
   last_2Rw = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
    Rows("2:" & last_2Rw).ClearContents
'Determine last Row of data in Sheet 1
   last_1Rw = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Initialize Row counter for Sheet 2
    nxt_2Rw = 2
'Loop through Rows in Sheet 1, Column A
     For nxt_1Rw = 2 To last_1Rw
'If the contents match the Drop Down cell, copy the
'entire Row to the next open Row in Sheet 2
      If Sheets(1).Cells(nxt_1Rw, 1) = Target Then
        Sheets(1).Cells(nxt_1Rw, 1).EntireRow.Copy _
           Destination:=Cells(nxt_2Rw, 1)
'Increment Row counter for Sheet 2
        nxt_2Rw = nxt_2Rw + 1
      End If
     Next
   End If
'Re-enable events
 Application.EnableEvents = True
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
October 31, 2011 at 17:11:33
Hi,

I am having trouble saving this so that i can use this macro in the workbook that i want to use it in? when i copy and paste the code into the module i am not able to run it on the workbook. how do i save it so that the macro is available to all workbooks i want to use it with? Sorry, please bear with me as i am new to VBA.

Thanks


Report •

#3
October 31, 2011 at 17:27:03
✔ Best Answer
Since this is a Worksheet_Change macro you do not 'run it' in the traditonal sense of the word. It is triggered by any change in the sheet who's module the code resides in.

Right click on the sheet tab for the sheet where your drop down is and choose View Code. The pane that opens will be the sheet module for that sheet, not a standard VBA module. Paste the code into that pane and it will 'monitor' that sheet; and only that sheet, for changes.

Once a change is made, it will fire and the first thing it will do is check the address of the cell where the change was made. If it matches the cell where you said your drop down is ($D$1) then it will execute the rest of the code. If the change was made anywhere else, then the code simply exits.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
November 4, 2011 at 11:13:46
Thanks, I got it to work. I tried messing around with the range a little bit, but the code only brings back a single line for a company name for instance sheet 2 will look like this:

A               B               C                
Company Name	Filing Date	Revenue	Defendant
ABC, Inc.	1/1/2000	1234	

Instead of:

A               B               C    
Company Name	Filing Date	Revenue
ABC, Inc.	1/1/2000	1234
ABC, Inc.	7/7/1999	1356
ABC, Inc.	5/8/2010	3213
ABC, Inc.	12/25/2010	5466
ABC, Inc.	11/13/2010	5465

How do I make it so I can return all rows where "ABC, Inc." is listed in column A?

Also, The company name "FGH, Inc." becomes a blank value whenever i select it in the drop down menu. It returns the correct values from sheet 1 over to sheet 2 but the value "FGH, Inc." is no longer visible in the drop down list.

Thanks


Report •

#5
November 4, 2011 at 12:29:23
Obviously, your "messing around with the range" has impacted the results.

If I cut and paste your example data, including the Column headings, from your original question and paste it into Sheet1!A1:C11 and also set up Sheet2!A1:D1 to match this...

A	        B	        C	D
Company Name	Filing Date	Revenue {Drop Down List}

...then my code works just fine, returning all 5 instances of ABC, Inc. starting in Sheet2!A2

Since I can't see your spreadsheet from where I'm sitting, I don't know what you "messed with" so it's kind of hard for me to troubleshoot your issue.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
November 8, 2011 at 17:08:56
I followed step by step what you laid out in your last response:

1. Copy paste A1:C11 to Sheet 1:

A               B               C       
Company Name	Filing Date	Revenue
ABC, Inc.	1/1/2000	1234
FGH, Inc.	4/14/2008	5678
DEF, Inc.	4/6/2005	5664
ABC, Inc.	7/7/1999	1356
XYZ, Inc.	5/8/2010	6456
ABC, Inc.	5/8/2010	3213
FGH, Inc.	1/23/2005	8546
FGH, Inc.	4/5/2006	2133
ABC, Inc.	12/25/2010	5466
ABC, Inc.	11/13/2010	5465

2. Copy paste A1:D1 to Sheet 2:

A	        B	        C	D
Company Name	Filing Date	Revenue {Drop Down List}

3. Right click on Sheet 2 tab --> View Code --> Copy paste below macro in to pane:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim last_1Rw, last_2Rw, nxt_1Rw, nxt_2Rw As Integer
'Check to see if a change was made to the Drop Down cell
 If Target.Address = "$D$2" Then
'If yes...
'Disable events so the code doesn't fire when the chagesa re made
  Application.EnableEvents = False
'Determine last Row of data in Sheet 2 and clear data
   last_2Rw = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
    Rows("2:" & last_2Rw).ClearContents
'Determine last Row of data in Sheet 1
   last_1Rw = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Initialize Row counter for Sheet 2
    nxt_2Rw = 2
'Loop through Rows in Sheet 1, Column A
     For nxt_1Rw = 2 To last_1Rw
'If the contents match the Drop Down cell, copy the
'entire Row to the next open Row in Sheet 2
      If Sheets(1).Cells(nxt_1Rw, 1) = Target Then
        Sheets(1).Cells(nxt_1Rw, 1).EntireRow.Copy _
           Destination:=Cells(nxt_2Rw, 1)
'Increment Row counter for Sheet 2
        nxt_2Rw = nxt_2Rw + 1
      End If
     Next
   End If
'Re-enable events
 Application.EnableEvents = True
End Sub

The drop down list is now in Sheet2!D2. Whenever I select a company name from the drop down menu in Sheet2!D2 columns A, B, and C in Sheet2 are not populated so the macro does not appear to be firing. Also, the drop down in Sheet2!D2 is made up of a list in Sheet2!E:

E 
ABC, Inc.
FGH, Inc.
DEF, Inc.
XYZ, Inc.

When I select ANY value from Sheet2!D2 "FGH, Inc." is removed and the list looks like this:

E
ABC, Inc.

DEF, Inc.
XYZ, Inc.
</prev>

Any idea how to keep "FGH, Inc." from disappearing?


Report •

#7
November 8, 2011 at 18:26:23
First:

The html tag to format data in this forum is pre and /pre not prev and /prev. I assumed you noticed that your post was not formatted correctly. Is there a reason that you didn't fix it?

Second:

re: Whenever I select a company name from the drop down menu in Sheet2!D2 columns A, B, and C in Sheet2 are not populated so the macro does not appear to be firing.

The fact that "FGH, Inc." is being removed means that the macro is indeed firing. Read on...

Third:

re: ...the drop down in Sheet2!D2 is made up of a list in Sheet2!E: and Any idea how to keep "FGH, Inc." from disappearing?

I specifically warned you about that issue in my original response.

I'll repeat what I said:

[Quote]
Note: You didn't say whether you had any other data in the sheets other than in Columns A:C so, as written, the code copies and pastes the entire row from Sheet 1 to Sheet 2. If this is going to copy data from Sheet 1 that you don't want, or overwrite data in Sheet2 that it shouldn't, then you'll need to limit the range that gets copied/pasted.

In your original post you said:

"Any help to get me off the ground with VBA would be great!"

I assumed that you meant that if you were given some code as an example, you could modify it so that it worked for your specific needs. So I "got you off the ground" and warned you about the fact that the code copy/pastes the entire row and would overwrite any data beyond Columns A, B and C.

I guess I shouldn't have assumed that you would know how to fix that. My error.

re: The drop down list is now in Sheet2!D2

...and that is the major source of all of your issues.

The code was written based on your original specifications:

In spreadhseet 2, cell D1 I have a de-duplicated drop down list

Moving the Drop Down to another location requires more modifications to the code than just changing the Target.Address. Since the code clears data from Row 2 down, as well as copying and pasting entire rows, it was wiping out not only parts of your Drop Down source list, but the Target data itself since it was in Row 2.

I have attempted to modify the code to work with what I think is your current situation. It now looks for a change in Sheet2!D2, only clears Sheet2!A:C only Copy/Pastes from Sheet1!A:C.

If you can get this to work, come back and let me know. I have some troubleshooting techniques that I think you should learne, but I don't want to add any more complexity at this point.


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim last_1Rw, last_2Rw, nxt_1Rw, nxt_2Rw As Integer
'Check to see if a change was made to the Drop Down cell
 If Target.Address = "$D$2" Then
'If yes...
'Disable events so the code doesn't fire when the chagesa re made
  Application.EnableEvents = False
'Determine last Row of data in Sheet 2 and clear data
   last_2Rw = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
    Range("A2:C" & last_2Rw).ClearContents
'Determine last Row of data in Sheet 1
   last_1Rw = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Initialize Row counter for Sheet 2
    nxt_2Rw = 2
'Loop through Rows in Sheet 1, Column A
     For nxt_1Rw = 2 To last_1Rw
'If the contents match the Drop Down cell, copy data
'in Sheet1!A:C to the next open Row in Sheet 2
      If Sheets(1).Cells(nxt_1Rw, 1) = Target Then
        Sheets(1).Range("A" & nxt_1Rw & ":C" & nxt_1Rw).Copy _
           Destination:=Cells(nxt_2Rw, 1)
'Increment Row counter for Sheet 2
        nxt_2Rw = nxt_2Rw + 1
      End If
     Next
   End If
'Re-enable events
 Application.EnableEvents = True
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question