I am trying to find out if it is possible to do the following
I have an excel workbook with comments in a number of worksheets. I have created a macro so I can list the comments and the value of the cells with comments. The macro creates a new worksheet and list the comments and value this great but I can only get it to create the comments from the sheet which is active. Is there a way of getting all the comment and cell value from all the worksheet (the entire workbook)
this is the code for the macro
Sub shoppinglist()
Application. ScreenUpdating = False
Dim commrange As Range
Dim mycell As Range
Dim curwks As Worksheet
Dim newwks As Worksheet
Dim i As Long
Set curwks = ActiveSheet
On Error Resume Next
Set commrange = curwks.Cells _
.SpecialCells(xlCellTypeComments)
On Error Goto 0
If commrange Is Nothing Then
MsgBox "no comments found"
Exit Sub
End If ...
I am trying to add comments in a selection of cells and where there is a value then the comment should show the formula value of the cell ie if the formula in cell A1 is = 9 + 10 then the comment should show this. This I have got to work, but if I have a comment already the macro fails and I have tried to get round this but I am not clever enough.
When adding a comment I check for an existing comment, if exist then delete and add new comment and some text in that cell. This works as long as the cell has an existing comment; other wise it advances to the "Else" where I thought I would be entering a comment and text in an empty cell. Why does my macro treat an empty cell as if it has a comment?
I have a standard laid out spreadsheet, but in column C, D and E, there are comments in each cell which are filled with a lot of text. I would like to set a macro so that whenever an 'a' is in column A, the comments for that row are shown/hidden (the 'a' being a tick symbol in Webdings). So if I place an 'a' in A4 and A23, and click a show/hide button, it causes the comments in C4, D4, E4; C23, D23 and E23 to be displayed, and when it is clicked again (or it could be a separate button), they disappear. If no row has an 'a' in the A column, then the button has no action. I have recorded a Macro of me showing and hiding comments, but of course that is for a specific row.
Reference the archives at: http://www.mrexcel.com/archive/VBA/17448.html
How can I modify this sub to skip cells that are empty? I only want cells that are populated within the range to actually display the comment indicator, with the cell text transferred to the comment.
Ideally, I'd also like the text to be moved (not just copied) to the comment and leave an 'X' in the cell, in place of the text that was transferred to the comment, but I'll settle for the action above.
I am having trouble writing a macro to do the following. Someone had created a spreadsheet a while back that highlighted the cell green and inserted a comment with the person's name and date/time if they changed anything in that cell. The macro works great, but I need a macro to undo the changes.
I would like a macro that will remove all comments from the sheet and revert the cells back to their original color. Since the cells encompass many colors, at the very least, I would like the macro to remove the comments and make the cells the default shading (white).
Among many attempts to insert and autosize a comment using VBA manually, which errored, i recorded the following bit. When running the macro, it does format the cell - text, not the comment - text and errors in the line highlighted in red.
Range("A14").Select Range("A14").AddComment Range("A14").Comment.Visible = False Range("A14").Comment.Text Text:="Ladida" With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlTop .ReadingOrder = xlContext .Orientation = xlHorizontal .AutoSize = True End With
I just want to add
.AutoSize = True
or something to that effect to the bit below. Any suggestions?
I am a novice user and not very familiar with Macros or VBA. I took a macro I found on contextures and got it to resize comments that already exist. However, I am trying to get it to also create the comment (with no content) if the comment does not already exist, and then resize it. This is what I have so far:
Code: Sub ResizeCommentsInSelection() Dim mycell As Range Dim myRng As Range Dim lArea As Long Set myRng = Selection
How would you prevent the copy/paste of cells that have comments?
Also, how would you allow cells with comments to be copied and pasted without pasting the comments?
I also have an aside question about the forum advanced search. When searching for multiple search words, how would you type the search to include all words, for example, "prevent" & "paste" & "comments".
I am trying to write a macro that takes the value in cell B2 from a workbook called ""numbers" in a worksheet called "summary," and creates a new worksheet in a workbook called "filestransfer" that is named the same thing as the value in cell B2 from "numbers." That part is done.
The ConsolidateLiveData macro only appears to check worksheets up to '2300-2399'. Any tabs after that, which are coincidentally numerical, aren't captured in the summary 'Live' worksheet. There is data on worksheets '2400-2499', '2500-2599' and '2600-2699' which meets the criteria to be included on the summary but it's not appearing.
Is there a limitation on the number of worksheets a macro can check?
how to record a macro and use 'relative referencing'
I have written the macro and I can reach a stage where it does the job and highlights the area (cannot get the formatting, but I can live with it).I have attached the file.I am getting stuck in copying the data to the adjacent sheet 'xxx'.
Sub Remove_Duplicate() On Error Resume Next Columns("a").Insert With Range("b1", Range("b" & Rows.Count).End(xlUp)).Offset(, -1) .Formula = "=If(CountIf(B$1:B1,B1)=1,"""",False)" .SpecialCells(-4123, 4).EntireRow.Delete End With Columns("a").Delete End Sub
Basically I have lots of worksheets and I want to remove the duplicates in column B for each work sheet with a duplicate entry...
I have a macro that works well for me. However, I want it to run on multiple worksheets in my workbook, but can not get it to work. Is there an easy way to accomplish this?
I have 2 worksheets. The first worksheet contaions raw data. On the second worksheet I am pasting certain data from the first worksheet. Everything copies except the data in column BI. This column has blanks as well as numbers. I need the column copied as is. The range should equal the number of entries in column A. Why am I getting the error, and Is there an easier way than what I'm using?
I have tried Sheets("").Select to just have the macro work on any worksheet in a workbook not just on the specific worksheet (i.e.Sheets("4474-60-2").Select . Get an error though.
There was a formula to enter sheets names in a cell range (ie: A1 would have value SHEET1, A2 SHEET2, etc) and use a FOR loop to cycle through the named sheets.
I have a ctrl-q key activated macro (module 2) that successfully clears specific data entry cells in forms on two identically formatted sheets. How do I prevent access to that macro on the remaining three sheets of five in the workbook?
If I have an excel workbook open and then, select the side-by-side option to open another workbook, and I want to write a macro that will switch between the two - how can I do this? The problem is, I don't want it to switch by file names (one workbook constantly changes names), I just want it to switch between active workbook and inactive workbook.
Once I have the code to switch between active workbook and inactive workbook, how can I have my macro look at a cell and see if data is present and if so, drop down to the cell below.
I am currently working on a simple macro to help with formatting a spreadsheet. The number of tabs on the spreadsheet will change to reflect the number of companies I'm running reports for. But the same process is to be applied to each tab. The issue I'm facing is I don't know the appropriate code to select all tabs without specifically referencing them.
The code that I would like to apply to all tabs is as follows: ActiveSheet.PageSetup.PrintArea = "$A$1:$J$85" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = ""
I have a workbook that is tracking part-time faculty appointments. Names and appointment details are entered on the first worksheet ("Entry"). I have another worksheet that pulls data from Entry to generate a summary of the salary for each appointment. Right now, this worksheet is called "Template" that needs to be copied and renamed for each appointment entered.
So, Entry could have up to 56 columns of data entered. There are nearly 100 rows of information for each entry (portrait-style for ease of printing). Columns A and B are information and will not have any entries. Column C is the starting point for the entry of appointments. Cell C5 will have the first appointment Name.
What I want is to create a macro that will copy the Template worksheet as many times as there are names entered from C5 and beyond (D5, E5, F5, etc.) When it creates each new worksheet, I want it to rename the new worksheet with the contents of row 5. For example, if "Smith, J." (without the quotes) is entered in C5, I want a copy of the worksheet Template called Smith, J.
The renaming of the sheet is important because I have a cell in Template that pulls the worksheet name, which is then referenced by cells in Template to pull the information pertaining to that appointment.
What I have seen here on Mr.Excel is a macro to rename a worksheet based on a specified cell, and I've seen something that looks like it created new worksheets, but I haven't seen the two put together like I'm looking for. I am also not that adept at the VBA, so I haven't really been able to figure out how to cobble it together myself.
To summarize: - I have two worksheets: Entry and Template - Names are entered into cells (C5, D5, E5, F5, etc.) on Entry - I need to copy Template as many times as Names are entered and rename each new copy with that Name (also, new sheets should be generated at the end (right) of the current list of worksheets) - The whole process can be activated by a button on Entry
The macro below works great as a conditional format routine on the Input sheet of the workbook, however I would like it to run on the Data sheet as well, but with a different range (E3:CE117). Can this be done?
Private Sub Worksheet_Calculate() Dim dcell As Range Dim icolour As Integer For Each dcell In Range("c15:g27") Select Case dcell.Value Case "GA" icolor = 43 Case "GAC" icolor = 4 Case "LA" icolor = 45.....................
I have an Excel spreadsheet from which I run a macro to open another workbook. This other workbook has multiple worksheets. The problem I have is that I never know which worksheet was left active whenever it was last saved. How do I revise my macro to open this workbook and make a specific worksheet active and hide all other worksheets?
I've been writing a little macro to prompt a user with a form which lets them select 2 reports to open and also lets them select a month. One report contains financial data for the month and the other report contains all data for the year. So each month someone has to copy data from report 1 into report 2.
I have so far been able to: -create form with error handling to open files - select values from a pivot table in report 1 - search pivot table 1 (single sheet) and copy the Name and Total $ fields
what i haven't been able to do: When i get the data from report 1 I switch to report 2. When i get to report to i need to search all the sheets in order to find a name with the original Name field from report 1 (I then have a HUGE case statement that will tell me if name = this and month = that then paste in cell $x$y). However, whenever i try to run my search across all sheets it will only find values on the sheet that is selected at the time.
I've attached my search code below, I would appreciate any help i can get as I've spent all day trying to resolve it with no luck
Function findinworkbook(TruncName) Dim ws As Worksheet
On Error Resume Next
For Each ws In ThisWorkbook.Sheets ' Find Function Cells.Find(What:=TruncName, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.ClearComments ActiveCell.AddComment ActiveCell.Comment.Text Text:="test" 'test to mark where the matched cells are On Error Goto 0
I have a excel macro that I am trying to use with multiple worksheets within the same workbook. The code runs fine on "Sheet1". However, when I click on "Sheet2", and try running the code I get an error message because the data is trying to be placed on "Sheet1".
Here is my
Sub Organics()
'The code under this section setups up the columns and rows
I have run into a problem with a Pivot Table overwriting other content when new data is added and refreshed.
This pivot table is included across a range of "Product" worksheets. To resolve this issue I would like a macro to insert a new row below the pivot table , this way when new content is added the pivot table will not overwrite the content below it.
The Pivot Table data is on the "Client" worksheet. Each Product worksheet includes a Pivot Table referencing the the Client data. I would like to force a user to add a new client data with a macro this way I can force a new row to be inserted across All "Product" worksheets.