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.
Are you talking about different worksheets in the same file.
go to the other worksheet then go to the particular cell and press = on keyboard
then use mouse to select the other worksheet from the bottom of the spreadsheet .
Then click on the cell you wish to link and press enter key on keyboard.
You can also do this to spreadsheets with different file names by opening both spread sheets in different windows and switching between the windows.
you can also copy just the data by copy whole sheet (click on the square in top left hand and copy and then right click paste special on new worksheet - with values only rather than the original formulas from the spreadsheet
You did not respond to my claification request. Here is some code that might help, it copies a selection of cells going down until an emty cell is found and across until an emty cell is found. This is form an earlier version of office but should comeclose to woring in 2007.
Sub copyrange() ' ' copyrange Macro ' Macro written by Royal 11/22/2008. '
'go to first worksheet and find entered value (note this is a value serach) Worksheets(1).Activate findfor = "A1" On Error Resume Next
findfor = Cells.Find(What:=searchfor, After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _ False).Address If findfor = "A1" Then erwks = ActiveSheet.Name Sheets(wksname).Activate er = MsgBox("Search item not found on Worksheet" + erwks, , "Search Error") Exit Sub Else findfor.Activate End If
'save this address and start searching for copy area boundaries begcell = ActiveCell.Address begcl = ActiveCell.Cells.Column begri = ActiveCell.Cells.Row 'search amaximum of 1000 rows and 676 columns endri = begri + 1000 endcl = 26 * 26 maxrow = 0 maxcol = 0
For col = Cells.Column To endcl If Cells(begri, col) = "" Then maxcol = col col = endcl ri = endri Else ri = begri End If For ri = ri To (Cells.Row + 1000) If Cells(ri, col) = "" Then If ri > maxrow Then maxrow = ri End If ri = endri End If Next ri Next col
maxrow = maxrow - 1 maxcol = maxcol - 1
'copy the selected area endcell = Cells(maxrow, maxcol).Address crnge = begcell & ":" & endcell Range(crnge).Select Selection.Copy 'go back and paste it in Sheets(wksname).Activate Range(returncell).Select ' use this if yo want to paste formulas etc. 'ActiveSheet.Paste ' use this code if you want to paste values instead of formulas etc. Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range(returncell).Select
If Sale Line 100 is an Excel Template then it is simple.
The Templates are just preformatted spreadsheets which are edited just like any other spreadsheet.
If you mean that you are using the Template to create another document but wanting all the fields to be the same as the Invoice then I suggest the following method:
Copy the Template fron Worksheet 1 onto Worksheet 2.
Add and remove any fields that you wnt to add or remove (Prices would be deleted on this copy and the title would be changed from Invoice to Delivery Docket, etc)
Then (on Worksheet 2) in the fields that you want to be the same - click on the cell you want the same Data in and hit the = sign. Then go to Worksheet 1 and Click on the cell that you want the data to be the same. Hit Enter. This will send you back to Worksheet 2 and you will see that any data entered into the cell in Worksheet 1 will now also appear in Worksheet 2.
Repeat this process for all the cells you want copied from worksheet 1.
When you complete the data in Worksheet 1 (the Invoice) all the data selected will automatically appear in the cells selected on Worksheet 2.
Try this on worksheet 2 select cell A1, type =, then open worksheet 1 and select B1. You will be returned to worksheet 2 and the formula in cell A1 will now read =Sheet1!A1. Enter anything into Shhet 1 A1 and you will see that it appears in the appropriate cell on Sheet 2.
When you want to print off the Invoice and the Delivery Docket - you only have to open each Worksheet and hit Print and a copy of each worksheet will be printed giving you an Invoice and a Delivery Docket with the appropriate info on each printout (eg if you exclude prices from the Del. Docket then after completing all the details in the Invoice will create a Del Docket with all the relevant information other than the prices.
Hi, I am not really sure if I get your exct meaning, so would I be correct in saying that you wish to copy all the data and structure from DCT INFO across to DCT. If this is correct so long as the dollar data is formatted in the first worksheet to currency then when it is pasted it will maintain its formating into the next sheet.
One question though...Do you wish to change the data on the DCT INFO sheet and wish it to be automatically changed in the DCT sheet?
If yes then you will have to write the following formula into the DCT cells:- =DCT INFO!A1 (where A1 is the first cell of data in sheet DCT INFO that you wish to duplicate.) You can then use auto fill by selecting the cell you just typed the formula in to and then placing the cursor onto the small black square in the righthand bottom of the cell and clicking the left mouse button and holding and dragging down to whereever your data ends. For example A30. Repeat this with the column B.
Let me know if tis is not entirelt what you require and I will try to help further.
You can refer to cells that are on other worksheets by perpending the name of the worksheet followed by an exclamation point (!)
to the cell reference. In the following example, the AVERAGE worksheet
function calculates the average value for the range C1:C10 on the
worksheet named Marketing in the same workbook.
Refers to the worksheet named Marketing
Refers to the range of cells between C1 and C10, inclusively
Click the cell in which you want to enter the formula.