I have two worksheets and want to
1) select a cell in sheet 1 (cell may change with each search),
2) copy that value,
3) search for the value in the other sheet (filter)
4) copy the values returned and
5) place those values in a specific region in the first spreadsheet
I can do this manually in a macro, but I don't know how to pass a new
cell value each time I run the macro in step 1. The value remains
hardcoded to the initial value recorded. Do I have to ask the user to
select the cell? But how do I capture in in the macro and use it?
The macro code: Sub Macro9() ' ' Macro9 Macro '
' ActiveWindow.Panes(1).Activate Range("E3").Select Selection.Copy With ActiveWindow .Width = 933.75 .Height = 573.75 End With Sheets("Sheet2").Select ActiveSheet.Range("$A$1:$GH$1670").AutoFilter Field:=2, Criteria1:= _ "=DM Review", Operator:=xlAnd Columns("B:O").Select Application.CutCopyMode = False Selection.Copy Sheets("2008 Edit Cal Deadlines").Select ActiveWindow.Panes(3).Activate Range("A222").Select ActiveSheet.Paste End Sub
- If you need clarification, ask it in the comment box above.
- Better answers use proper spelling and grammar.
- Provide details, support with references or personal experience.
Tell us some more! Your answer needs to include more details to help people.You can't post answers that contain an email address.Please enter a valid email address.The email address entered is already associated to an account.Login to postPlease use English characters only.
Tip: The max point reward for answering a question is 15.
If you want it to realy say fourthy four when the value is 44 instead of have it say "four four" you need to question the position. If it's in second position starting from left it will be fourty , if it's in third place it will be fourhundred ... etc
You can import data from one sheet to another. If you want to bring a total from Sheet 1 to Sheet 2, then in Sheet 2 enter [=sheetname!cell] (without the brackets of course. You will now have the data from Sheet 1 in a cell in Sheet 2 and you can do anything you want with it. One note: if the sheet name has a space in it (Sheet Name) you have to enclose the name in single quotes; that tells it that the whole thing is the name. For example, Monthly Sales, A10 is the total for last month; to put it in this month for a running total, you would enter in Sheet 2 ['Monthly Sales'!A10] (without the brackets again). Now you can have a running total between the two sheets.
The easiest way to do this is to use a formula called vlookup. You need to have the raw data some where in the workbook, but it can be a separate sheet. Put all the raw data in a table, make sure the account number is on the furthest left column, then start entering the formulas
value_to_find = the value you want to find on the left most column of the table table_to_check = the table you want to find the data from column_index_number = the column number of the data field you want to return into this cell, 1 = the left most column. range_lookup = false for an exact match, true for the nearest match
Say the table is on sheet2 between A1 and E300
B15 is whatever you type, so no formula needed here B12 =VLOOKUP(C1,Sheet2!A1:E300,2,FALSE) G12 = B12 =VLOOKUP(C1,Sheet2!A1:E300,3,FALSE) etc
If you right click the cell in question and go to Format Cells..., on the protection tab there should be a check mark in the locked box. Go to the tools menu and choose Protect and Protect Sheet, enter a password and your cells will be protected.
Any cells you want to allow changes to when the sheet is protected should have the Locked checkmark removed.
If you don't have MS Office, then you will have to purchase either the Excel component as a stand alone but you would be better off with the whole MSOffice as it contains your Word processor, Database, Excel, Powerpoint presentation software and in the 2003 version also Publisher.
It is not too badly priced anymore, but if you cant afford it then you may be able to borrow a copy whilst you are doing your course from a friend, but you will have to uninstall it afterwards.