Macro To Get Comments From All Worksheets

Feb 10, 2007

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 ...

View 6 Replies


ADVERTISEMENT

Add Cell Comments Macro

Jan 2, 2007

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.

Sub InsertingComments()

Dim r As Range

For Each r In Selection
If r.Comment = True Then

View 9 Replies View Related

Adding Comments To Cells Via Macro

Oct 8, 2009

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?

View 4 Replies View Related

Macro To Show & Hide Comments

Jul 24, 2007

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.

View 6 Replies View Related

Macro To Convert Range Of Cells To Comments

Jul 12, 2007

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.

View 9 Replies View Related

Macro To Delete Comments And Unhighlight Cells

Oct 24, 2007

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).

View 9 Replies View Related

Autosize Cell Comments/Shapes Via Macro Code

Aug 25, 2006

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?

Range("A14").Comment.Text "Ladida"

View 5 Replies View Related

Macro To Create Empty Comments And Resize Them To Uniform Size?

Nov 28, 2013

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

[code].....

View 2 Replies View Related

Prevent Copy / Paste Cells With Comments Or Allow Paste But Do Not Paste Comments

Feb 4, 2014

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".

View 7 Replies View Related

Macro To Move Between Worksheets

Apr 4, 2014

I have a work book with 6 worksheets, sheet2 is called Maersk, sheet3 Mariana, and so on ....

I need a macro so when I type Mariana in sheet1 g20 and hit enter it go's to Mariana sheet .....

View 5 Replies View Related

Deleting Worksheets With Macro?

May 2, 2014

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.

View 2 Replies View Related

How To Make Macro Run In All Worksheets

May 21, 2014

How do I make my macro tun in a loop through all sheets?

View 10 Replies View Related

Macro Not Reaching All Worksheets?

Jun 6, 2014

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?

View 6 Replies View Related

Macro Which Uses Two Worksheets At A Time?

Feb 6, 2014

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'.

View 4 Replies View Related

Apply Macro To All Worksheets

Dec 2, 2009

I need to run the below code on all work sheets:

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...

View 9 Replies View Related

Locking Worksheets With A Macro

Dec 8, 2009

I found the following code which allows you to lock sheets 1 to 3 with the ActiveX control buttons located on Sheet4.

View 8 Replies View Related

Run Macro On Multiple Worksheets

Jun 6, 2008

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?

View 9 Replies View Related

Copying Between Worksheets With A Macro

Mar 20, 2013

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?

Sheets("Raw Data").Select
Range(Range("A4"), Range("A4").End(xlDown)).Select
Selection.Copy

[Code].....

View 5 Replies View Related

Macro That Changes Worksheets Not Specific One?

Apr 16, 2013

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.

View 9 Replies View Related

Macro To Modify Several Worksheets

Jun 21, 2013

Due to the lack of time and not being able to make it work otherwise this is what I have right now:

If Range("g59") = 1 Then

Sheet1.Activate
Application.Run "Edit_view"
Sheet3.Activate
Application.Run "Edit_view"

[Code] ...

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.

View 1 Replies View Related

Restrict Macro To 2 Worksheets Of 5?

Nov 8, 2013

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?

View 2 Replies View Related

Macro: Switch Between The Two Worksheets

Jul 12, 2007

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.

Windows("Job Number Request form.xls").Activate
Range("C9").Select
Selection.Copy
Windows("Backup Copy of 2007 Capex Job Log.xls").Activate
Range("E570").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

View 9 Replies View Related

Macro: Selecting All Worksheets

Jul 16, 2007

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 = ""

View 9 Replies View Related

Macro: Create & Name New Worksheets

Sep 30, 2008

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

View 9 Replies View Related

Run Event Macro On Two Worksheets

Oct 16, 2006

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.....................

View 7 Replies View Related

Macro: Hide Worksheets

Nov 23, 2006

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?

View 9 Replies View Related

Macro To Search All Worksheets

Jul 30, 2007

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

Next
End Function

View 5 Replies View Related

Run Macro On Multiple Worksheets

Sep 28, 2007

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

Range("B1:D1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext

View 3 Replies View Related

Add To Comments With VBA

Jul 11, 2005

I know that you can add a comment to a cell using a statement like

Range("A1").AddComment "Hello"
But is it possible to add more text to that comment using VBA?
I tried things like

Range("A1").AddComment "Hello."
Range("A1").Comment = Range("A1").Comment + " How are you?"
but I guess I can't alter the comment that way.

View 11 Replies View Related

Macro To Insert Row Across Range Of Worksheets?

Apr 16, 2014

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.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved