Link to home
Start Free TrialLog in
Avatar of daintysally
daintysallyFlag for United States of America

asked on

VBA to copy values from one worksheet to cells in another worksheet

Hi Experts,

I have a workbook with 2 worksheets.  One of the worksheets provides an overview and the other provides the data.  Can someone help me with the vba that will copy the values from the 'Data' worksheet to a cell in the 'Overview' worksheet when the value in cell 'A10' from the 'Overview' worksheet is equal to the value in the 'Data' worksheet?
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Rather than do that, why not just use an AutoFilter on the Data worksheet?
Avatar of daintysally

ASKER

Thanks for responding matthewspatrick.  What I need to have happen is this:

On the Overview worksheet the value that I need to compare is a header.  If that header value on the Overview worksheet is found on the Data worksheet, then I need to copy specific values from under that value to the Overview sheet.  I don't want to put if statements in the cells.
I understand what you are trying to do.  I still maintain that the path of least resistance is to simply use the AutoFilter.
I have tried using Advanced AutoFilter and I get a 'Reference is not valid' error.  How do I say that the value on the Overview worksheet should be equal to the value on the Data worksheet for the criteria range?
I did not recommend the Advanced Filter.  I recommended the AutoFilter.
Please explain further on how to apply AutoFilter to this scenario.
Please see the attached file:

Q-27914615.xlsx

1) I created 500 rows of fake sample data

2) To set the AutoFilter, I select A1, went to the Data tab of the Ribbon, and clicked Filter

3) Now, if I want to view only subsets of the data, I can use the drop down selectors in the header row to set my criteria (for example, I can select only those rows where Name = Janice)

My suggestion to you is to just use your Data sheet, and set the AutoFilter on it, rather than rely on VBA.  AutoFilter is one of the things Excel does very, very well, and IMHO you should stick with it when it is capable of doing what you need.
I have reattached the sample file to show what I am trying to do.  Sheet2 has the value of 'TestA' in cell A1.  I want the vba to go and look for 'TestA' on Sheet3 and copy the values under 'TestA' and paste them on Sheet2.  I don't see how AutoFilter will accomplish that.
Q-27914615-1-.xlsx
Can someone please help me with this request?
Avatar of Sulgurth
Sulgurth

Example: Overview sheet named: "Overview" and source data sheet named "Data"
for cell reference on overview to pull cell A1 on the data sheet: "=Data!A1"

To do the code in VBA:  worksheets("Overview").cells(1,1).value = worksheets("Data").cells(1,1).value

to cycle thru the spreadsheets, variables can be placed in for the worksheet names, and cell locations.
Thank you for responding to my request.  Can you apply the vba to the sample worksheet that I attached in my post on 10/26?
I reattached the example with a macro in sheet2 that will copy the cell contents under the text in cell A1 ONLY into the cells below it, with a delimiter of a blank line in sheet3.
Q-27914615-1-.xlsm
I tried to run your code, but it is prompting me for a macro name.  When I type in a name, it opens a new macro.  What am I not doing?
The run macro button will not work due to this being an event based Macro
Goto Sheet2 and change value in Cell A1

Any time that is changed, the values under it will change to match what is listed with them in column A on sheet 3
I have attached another sample file, I want the user to be able to click on the 'Get Data' button and the vba will populate the values in the 'TestData' worksheet in columns A and B from the 'TestOverview' worksheet.  How can your example be modified to do that?
Test.xlsx
This is being set to fill the A1 segment on TestData

The main changes from the other version are:
* removed the multi-cell validation from the top of the function
* moved the function to the ThisWorkbook object, and renamed it to a public non-event Sub
* Changed All Sheet2's to TestData
* Changed All Sheet3's to TestOverview
* Added second line in the data copy section for the second column; also changed source columns from 1 to 2-3
* Saved test file to Macro-Enabled Workbook
* Added line to resize columns A and B on the TestData page to fit the data

# To make another function for the second test value section, copy/paste the function, give it a new name
# Change Variable J (Which is your destination sheet row rounter) Initial value from Row 2 to Row 14
# Adjust the if statement that alter the boolean status for In/Out of section to True (line 21) from (1,1) to (13, 1)
Test.xlsm
Instead of making another function for the second test value, can the code be modified so that it will read through the 'TestData' worksheet and fill in the blank values where the condition is met for the headers ("TestValue1", "TestValue3")?
ASKER CERTIFIED SOLUTION
Avatar of Sulgurth
Sulgurth

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you Sulgurth...you are AWESOME!!