I have a macro that inputs a sheet and cell from the user. when the macro tries to make a formula it outputs =data!'AE3'. I need it to output =data!AE3
Dim Sheet As String
Sheet = InputBox("Take Data from where?")
Dim startcell As String
startcell = InputBox("Start Cell?")
ActiveCell.FormulaR1C1 = "=" & Sheet & "!" & startcell & ""
I have information across 5 cells what I want to say is if P3 is not equal 00/01/1900
Then copy and paste values of P3,Q3,R3,S3,T3 in a new sheet in cells P3,Q3,R3,S3,T3.
Can you link a cell to a sheet tab. If yes how?
View 4 Replies View RelatedIs it possible to link a sheetname?
I have a list of employees on sheet1 and each employee has there own sheet with their name as the sheet name. Each sheet contains a form that the employees need to fill in.
I have created several extra sheets for new employees and I would like these sheet names to be linked to a cell on sheet1. This way the only updating I need to do when receiving new employees is enter there name to the list in sheet1.
Is it possible to have a user form command button perform link or steps that are recorded with macro recorder? I am trying to reformat cut and paste a xls raw data file. I am trying to keep my file uploads to a min., but can upload an example if necessary.
View 4 Replies View RelatedIs there a way of embedding a formula in a worksheet so that the tab name of each worksheet is linked to a value in a cell on that sheet? (I don't want to run a macro)
In other words if the cell name is changed, the page name changes automatically.
I'm trying to make a floor plan which shows what seats are available depending on the time it is. So I made sheet1 = to the map (where the seats are located), sheet2 - rows = seat #'s, columns = Time (broken down by half hours). Now i have conditional format to show "t" in red (which means taken), "f" in blue (which means free), and "n" to show in gray (which means not working).
What i need is example - Seat # 200 in sheet 1 to show red, blue, or gray depending on the value in the row that corespond to this seat and the column that corespond to the current time.
I want to create a dynamic line graph using week and year numbers stated in another sheet.
e.g.
Start Year - 2012
End Year - 2014
Start Week - 3
End Week - 12
The top 2 rows above my graph data are as below:
Year - 2012 2012 2012
Week - 5 6 7 etc.
This works fine if the start and end year are the same but if it's greater than one year, it doesn't recognise that.
I have a macro where I manually select a cell then the macro kicks in to copy and paste the contents into the Find function. From there it goes to another work sheet, clicks on a cell in column one and searches for matching cell contents. Then If false it manually goes back to the original worksheet/cell and then I input a N in the left adjacent cell. If true I enter a Y. Right now I can only run this for the specific cell the macro was recorded for. I would like to expand this.
What I would like to do create a loop in the macro that waits for my cell choice input then continues with the with the Find function. If false the macro should just loop back to the original worksheet/cell and wait for input, ie the next cell selected. However, if true a worksheet/cell is selected, a Y is input and it loops back to the original cell and waits for input.
how to allow formating a text and cell in protect sheet ?
View 4 Replies View RelatedIs it possible to have a macro create a hyper link once data is entered into specific cell, i have a multi sheet work book used as a stock card system.
Each stock item has its own worksheet and the product code is entered in to cell E3
(which in turn has a macro in it to change the sheet name to match the product code).
the macro also inserts a row of info into row 5 of another sheet used as a summary sheet ( CONTROL.SHEET or SHEET 4) using copy and paste link.
i need a macro to generate a hyperlink in cell D4 of the control sheet between the row info and the matching product code sheet ( the product code is displayed in cell E4 of the control sheet.
I only input one item at a time and then manually cut and insert the row info into the stock list on control sheet. so every time i input a new item, the row info is always in row 5 of the control sheet.
I have two worksheets, one with detail monthly information and one with YTD information. So let's say the three numbers I want to capture in the YTD sheet are in columns B, G and I on the monthly sheet. January's data might be in B5, G5 and I5. February's data is in B12, G12 and I12 and so on.
On the YTD sheet in cell C2 I link to Monthly!B5 and in C3 I link to Monthly!B12 so cells C2 to C13 on the YTD sheet show the monthly totals from column B on the detail sheet. On the YTD sheet, cells C20 to C31 show the monthly totals from column G on the monthly sheet, so cells G5, G12, etc. And finally, cells C40 to C51 on the YTD sheet show monthly totals form column I on the monthly sheet.
In the past I've always created all these links manually. After creating the links in C2 to C13 on the YTD sheet, is there a way to use a formula in C21 that uses the link in C2 to create a link for G5?
Using excel 2011 for mac and would like to learn how to write and record a macro to link to a button to "go to a layout and cell"
View 4 Replies View RelatedI am writing some code where I will need to refresh data and do a few calculations while the user of the spreadsheet is working on the information. How do I write the code for VBA to record what sheet/ cell the user is currently on and then do i use the "GoTO" function to return the use to where he/she was when they left off.
View 5 Replies View RelatedI am wanting to pause a macro to allow a user to select a cell and when the user hits enter the macro starts running again. My intention is that a user would select cell B5 or B8 or B55 and then hit enter and the cell would move 6 cells right and enter a value from the macro. The following is what I have tried and even though it allows to select a cell, it doesn't make that cell the active cell.
[Code].....
I need to query a column and find the cells that have been incorrectly coded. For example the coding standard is #<reference number>/. Sometimes people forget the "/". It is not as easy as using search and replace because the string may have more contents than the above.
For example it may show: ATM/45678/#789876 dt. 1-2-14/ or it something like CHQ/44384/#78987600.
There are many permutations. So I need to find the cells that don't have a "/" following the #<reference number>, then allow a user to enter the slash. At this stage, I don't need code to find cells containing errors, I think I can figure that out. I do need to know how to allow the user to edit the cell as part of the macro. Once the user corrects the error, the macro should then continue. Is this possible?
is there any way a macro can be automatically run when a user clicks unto a cell?
View 5 Replies View RelatedHow do you add a macro to a cell that works wen the user tabs out the cell?
View 9 Replies View RelatedI have a master spreadsheet that houses most of the information that remains in my control, and several other user spreadsheets that update cells from that master through links.
I am having some problems lately with users doubleclicking the locked linked cells in their workbooks, and excel then wanting to open and give the user access to my master spreadsheet. Is there a way to disable this feature? I would like it to not even acknowledge the action if that's even possible because it confuses them as to why they are getting a prompt to open another document.
I was planning to do was have a macro where the document opened and the cell that information was pasted in was decided depending on which cell was selected when the macro was run.
This worked fine, but it does mean that it needs someone who can edit the VBscript every time there is a new starter or someone leaves so isn't really viable.
What I was wondering, is if it is possible for me to start a macro, which then drags in the information I need from a text file, does all the calculations and rearranging, and then brings up a prompt for the user to open the document they want to paste it in, click on the cell where the pasting will start, and then the macro continues from there?
To try to explain it in as clear a way as I can. Say we have three managers, Alan, Brenda and Charlie. In Alan's team there are 10 staff members. Aaron, Bill etc.etc. If we have just run the report for Aaron, the macro will run, automatically drag all the information in and rearrange it, then it will pause while the user opens up the "Alan" spreadsheet, they select the "Aaron" first cell, click OK or something, and then the macro continues on its happy way?
I am about to write a small macro to do a iteration calculation. I would like though for the user to select me a cell so that the macro uses the value in that cell to start calculations. Now, what I think would be the problem is how do I code a "time" or notice an action such as clicking in a cell to continue with the macro.
Would it be by inserting a pause in the code of a certain time so that gives the user time to click and wait for the code to start? If so, how would much time is enough? Is there another thing that could restart the code such as "noticing a click in a cell" command in VBA?
At the top of my spreadsheet, a day of the week is selected via Dropdown.
Below that in a table, the days of the week are split into hourly slots.
I want to write a macro to jump to the first cell in the bottom table containing the day selected in top cell.
I have a worksheet named "ABC". In row 4 of this worksheet, each cell from G to BG is numbered from 1 onwards and each cell has a unique number. I would like to have a macro ask the user through a pop-up question box which column data should be calculated in. For example, if the user types "1" in the question box, the macro will go to column G which has "1" in cell G4 and will select G5 the cell just below the column the user defined and calculate the formula.
View 6 Replies View RelatedI need to create a Macro that will link Horizontal data to vertical data in a separate sheet. Here's the Example:
I have data in a forecast spreadsheet that is horizontal, So, J2:U2 (12 columns for 12 months). This row of data needs to be linked in a single column of data on a separate sheet called "Price_Quantity". So I need C3:C14 (in price_quantity sheet) to be linked to the data in J2:U2 (in forecast sheet). Then C15:C26 needs to be linked to J3:U3, so on and so forth. 12 rows linking to 12 columns. All the way until C4959:C4970 is linked to J415:U415.
I have a macro that copies all my sheets onto a new sheet called combine. It is pasting onto the combine sheet starting on cell A2 instead of cell A1. Following is my macro.
Sub Combine()
Dim J As Integer
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
On Error Resume Next
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy Destination:=Sheets("Combined").Cells(Rows.Count, 1).End(xlUp)(2)
Next
End Sub
I have a column who's content is determined via about 6 nested if statements from data on that row. That cell is then conditionally formatted to a certain color based on the text that is ultimately printed from the nested ifs (simply an extra visual legend for the text). All of this contributing info and about 1000 lines items make a very large and difficult to print page. What I am trying to do is a make a summary sheet that simply takes the index number of these 1000 rows and copies or links the conditional formatting of the mentioned cell onto this number on another sheet. I have already linked this status cell and put it adjacent to the index number which works well in that with two columns I can show the index and status but if I could combine the color of the status cell onto the index cell, it would be even better. I am pretty sure another conditional format for this summary sheet would not be possible or be extremely complex since the contents of the index cell I am conditionally formatting have no bearing on the conditions for the format. Was hoping there is some VBA magic that could simply mimic the conditional formatting from one cell and put it on another.
View 1 Replies View RelatedI have a workbook with several sheets. I have written a macro to check if the value in a column is matching with that in another column in another sheet and return the value in a preceding column in the second sheet into a preceeding column in the first sheet. The code looks likes this and seems to be working fine.
Sub Copy_Price_Code()
Dim Rw As Long
For Rw = 6 To Sheet6.UsedRange.Rows.Count
[Code] ....
However, there would be more sheets in the workbook in future and I need to edit the macro in such a way that the user can select the source and destination sheets which I could use in the above macro. I tried to do it as follows but its not working.
Sub Copy_Price_Code()
Dim Rw As Long
Dim Rw2 As Long
Dim Sourcesheet As String
Dim Destinationsheet As String
Sourcesheet = InputBox(Prompt:="Enter source sheet name. (example: Sheet1).", _
[Code] .......
Is it possible to link a cell e.g. A10 that has a time value in it (there is a formula in this cell that puts in the time value) to another cell in a range of cells e.g. B1:B1000 that has the same time value?
I know someone out there knows how to do this.
This file pulls totals from external links. The way I have it set up is that every month our log is saved to a naming scheme involving the date, then a fresh log is created for the current month. So, for each month I have "log mm-yyyy".
Now, the chart file is a thirteen month rolling chart (meaning every month it must show the thirteen most recent month's totals). I have created a macro to automatically update the links, based on an input in cell A:1 (meaning you can enter a date in the past and view the thirteen months previous). The problem is that we have only been doing the log for 10 months. So, when the macro runs to update the links, and it gets to months 11 through 13, it pops up a window for the user to manually browse for the missing(non-existent) files. There are quite a few cells that contain links to various information on these logs. I get a prompt for each cell and have to click "cancel". Once I have clicked cancel through all of the broken links, then everything works great.
My question (after all that) is, is there a way to suppress the prompt for the user to search for the missing file? Like I said, this chart is for management, and they shouldn't have to click cancel a dozen times just o look at a chart. If the file doesn't exist, then just break the link.
Obviously once the next three months are over, this problem will disappear, but in the mean time I need a solution.
I have got a protected sheet with macros, how can I ensure that users can only open the sheet as Macro enabled only otherwise the sheet would not open?
I understand that some users may have different Macro security settings?