PDA

View Full Version : vba comments



momo19
05-24-2013, 12:32 AM
Hi all,
i have being at this for two days now with no luck :banghead: . i am trying to copy values in once cell and paste them as a comment in a different cell in a different worksheet.

for example copy i9 to i33 from sheet 1 and paste them as comments to i9 to i33 in sheet 2 !!!

any help would be greatly appreciated thanks

mancubus
05-24-2013, 12:53 AM
....
....

deleted my solution.
just misunderstood the requirement. :)
thanks snb...

snb
05-24-2013, 01:02 AM
Sub M_snb()
sn=sheets("Sheet1").range("I9:I33")

With Sheets("Sheet2").Range("I9")
.ClearComments
.addcomment
.copy sheets("sheet2").Range("I10:I33")
End With

for j=1 to ubound(sn)
sheets("sheet2").cells(j+8,9).comment.text sn(j,1)
next
End Sub

momo19
05-24-2013, 01:17 AM
hey thanks or the reply !! it only seems to work for i9 and i33 to the range o cells between !!

momo19
05-24-2013, 01:19 AM
snb you absolute hero !! this is just what i was trying to do !! one more thing !! you think if i put this in a loop where it copies the cells from multiple sheets and paste them into one summary at the end that would work ?? or would i have to change the code around alot ??
thanks

snb
05-24-2013, 04:11 AM
Your question is too vague to answer specifically, but generally it's possible.

momo19
05-24-2013, 04:39 AM
apologies here is what i am trying to do:

i have a workbook with a bout 30ish sheets + possibility of more sheets being added...

all the sheets have the same format so that makes things a little easier, i am only interested in two columns on each sheet the observation column and the actions columns the two are beside each other.

what i wanted to do is to construct a summary sheet at the end containing all the observations and actions.

what i managed to do is copy the observations column from each sheet and paste them all beside each other in the summary sheet, the next thing i want to to paste the actions columns as a comment to the corresponding observations.

this code is given at the end of what i have done so far.

i know this is very difficult so if you have maybe an alternative and easy way to represent the data in a nice neat mater please feel free to suggest. or if you think this way is possible please again feel free to help me

thanks alot in advace

here is the current code i have
ub HoneyBoo()
' introduces variales
Dim Sht As Worksheet
Dim ShtName As String

' Deletes previous content in summary page
Sheets("Summary").Select
Cells.Select
Selection.Delete Shift:=xlUp

' pastes in the 5s headings
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("B1 Canteen").Select
Range("A8:G33").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Summary").Select
Range("A1").Select
ActiveSheet.Paste

' loop for pasting the values in
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> "Summary" Then
Sht.Select
ShtName = ActiveSheet.Name
Range("H9:H33").Select
Selection.Copy
Sheets("Summary").Select
Range("IV1").End(xlToLeft).Offset(0, 1).Select
ActiveCell.Value = ShtName
Selection.Offset(1, 0).Select
ActiveSheet.Paste
Else
End If
Next Sht

' inserting a blank column and a blank row
Sheets("Summary").Select
Columns("A:A").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("A:A").ColumnWidth = 3.5
Range("I32").Select

'making the headings Bold
Range("I2").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Font.Bold = True
' autofitin he coloumns
Columns("I:BW").Select
Columns("I:BW").EntireColumn.AutoFit

Range("A1").Select
End Sub

snb
05-24-2013, 05:55 AM
Where did you learn to use

Sheets("Summary").Select
Cells.Select
Selection.Delete Shift:=xlUp

instead of

sheets("summary").usedrange.clearcontents


??

momo19
05-24-2013, 06:37 AM
i recorded a macro and copied the code from that ~!! is the secod method muc easier ??

snb
05-24-2013, 07:47 AM
It's faster, simpler and 'better'.

Avoid any 'Select' or 'Activate' in VBA.

momo19
05-27-2013, 01:44 AM
ok im only new to this :P thanks tho ill try and not use any select or activate from now on.

Also would anyone be willing to help me or guide on the right path for getting this done ?? im completely lost here :(

SamT
05-27-2013, 09:26 AM
ok im only new to this :P thanks tho ill try and not use any select or activate from now on.

Also would anyone be willing to help me or guide on the right path for getting this done ?? im completely lost here :(
I used your recorded Macro, only took out what you should have to make it faster and neater, then I edited it for lazy typers. Compare it to your original to see what I have done.

Option Explicit

Sub SamT()
' introduces variales
Dim Sht As Worksheet
Dim ShtName As String
Dim SumSht As Worksheet
Set SumSht = Sheets("Summary")
Dim PasteCell As Range

' Deletes previous content in summary page
SumSht.UsedRange.Delete Shift:=xlUp

Application.CutCopyMode = False

'Paste Common Data
Sheets("B1 Canteen").Range("A8:G33").Copy
SumSht.Range("A1").PasteSpecial 'xlPasteAll is default

' loop for pasting the values in
For Each Sht In ActiveWorkbook.Worksheets
If Sht <> SumSht Then
ShtName = Sht.Name
'Because you will use PasteCell in Row(2) more often
Set PasteCell = SumSht.Range("H1").End(xlToRight).Offset(1, 1)
PasteCell.Offset(-1, 0).Value = ShtName
Sht.Range("H9:H33").Copy
PasteCell.PasteSpecial
'Edit the contents of snb's Sub M_snb and place here.
End If
Next Sht

With SumSht
'inserting a blank column and a blank row because we didn't paste appropriately
.Columns("A:A").Insert
.Rows("1:1").Insert
.Columns(1).ColumnWidth = 3.5

'making the headings Bold
.Range(("I2"), .Range("I2").End(xlToRight)).Font.Bold = True
' autofitin he coloumns
'Alternately autofit Rows(2).entirecolumn, then set column width of Column A
.Columns("B:BW").EntireColumn.AutoFit
End With
Range("A1").Select
End Sub