How to add code to sheet via Macro

[panamakevin]

I have a macro i am using to format spreadsheets. But part of that process requires me to right click the sheet, select view code, and paste some VB into the Sheet2's code. I can't seem to get it to accept it... Thanks in advance for your help!! Let me know if you need more info!

Thanks again

[foxguy]

I don't think you can get a macro to "view code". You will need to open a codepane in the VBE and insert the text into there.

This can get you started. http://www.cpearson.com/excel/VBE.aspx

[panamakevin]

Ok, went to the site and alot of it is over my head... here is the code i was trying to get plugged in, basically it makes it so that when I click on a cell, A-F is selected and the whole row is highlighted.

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Static rr
If rr <> "" Then
With Rows(rr).Interior
.ColorIndex = xlNone
End With
End If

r = Selection.Row
rr = r

With Rows(r).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

Range("A" & Selection.Row).Resize(,6).Select

End Sub

Is there a way I can incorporate this into my Macro?

Sub WesternUnionReformat()
'
' WesternUnionReformat Macro
'

'
Cells.Select
Cells.EntireColumn.AutoFit
Cells.EntireColumn.AutoFit
Selection.ColumnWidth = 6.29
Selection.ColumnWidth = 10.86
Cells.EntireColumn.AutoFit
Range("A:G,I:I,J:L,P:P,R:R").Select
Range("R1").Activate
ActiveWindow.SmallScroll ToRight:=9
Range("A:G,I:I,J:L,P:P,R:R,T:W").Select
Range("T1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Columns("F:F").Select
Selection.Replace What:="0", Replacement:="w", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("D:D").Select
Selection.Replace What:="dep", Replacement:="d", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("F:F").Select
Selection.Cut Destination:=Columns("K:K")
Columns("C:D").Select
Selection.Cut Destination:=Columns("G:H")
Columns("B:B").Select
Selection.Cut Destination:=Columns("C:C")
Columns("A:A").Select
Selection.Cut Destination:=Columns("D:D")
Columns("G:H").Select
Selection.Cut Destination:=Columns("A:B")
Range("F1").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[5],""f"")"
Range("F1").Select
Selection.AutoFill Destination:=Range("F1:F1057"), Type:=xlFillDefault
Range("F1:F1057").Select
Columns("A:F").Select
Range("F1").Activate
Selection.Font.Bold = True
Columns("A:F").EntireColumn.AutoFit
Sheets("Sheet1").Select
End Sub

[shg]

Please change your QUOTE tags to CODE tags.

[foxguy]

panamakevin;

Just letting you know that I'm ready to respond after you have complied with shg's request.

[panamakevin]

Ok, went to the site and alot of it is over my head... here is the code i was trying to get plugged in, basically it makes it so that when I click on a cell, A-F is selected and the whole row is highlighted.

Please Login or Register  to view this content.

Is there a way I can incorporate this into my Macro?

Please Login or Register  to view this content.

sorry for the lag on correcting the formatting, couldn't figure out the Code tags until today :P

[foxguy]

panamakevin;

You lost me.
If you already have the WesternUnionReformat macro in the workbook, what's the problem with just inserting the other macro into the workbook?

IOW, I don't want to spend time writing something that doesn't solve your problem. (I have done that too much recently). So I need a little more explanation of what your problem is.

[panamakevin]

Sorry, I have already tried just inserting it in and it wouldn't work, the only way I can get the Worksheet_SelectionChange part to work is if i copy and paste it into code of the sheet. What I was trying to say was that I would like to be able to combine it into the WesternUnionReformat macro so that I could just run that when I open each of these spreadsheets then it would be reformated and have the highlighting..

I'm sorry if I am asking this incorrectly, I saw that I also got an infraction for not complying, even though I thought I had done so correctly... but anyway, I apologize for any improper posting that I did and I apologize again if I didn't clarify what I need/want.

[foxguy]

Hi panamakevin;

Sorry, I have already tried just inserting it in and it wouldn't work, the only way I can get the Worksheet_SelectionChange part to work is if i copy and paste it into code of the sheet. What I was trying to say was that I would like to be able to combine it into the WesternUnionReformat macro so that I could just run that when I open each of these spreadsheets then it would be reformated and have the highlighting..
Put these in a standard module

I'm sorry, I'm still not sure I understand.
Are you saying that when you run WesternUnionReformat, you want it to also run your Worksheet_SelectionChange? If that is right, it's easy with what you already have.

Put this in the sheet's codepane (right where you have it now), just have it call Format_Row instead of trying to do it by itself.

Please Login or Register  to view this content.

Put this into a standard module. I assume that you already have WesternUnionReformat in a standard module, so just add the one line (that calls Format_Row) at the end, and add Format_Row to the same module (but note the "Optional" in the parameter list and the "If ..." at the top).

Please Login or Register  to view this content.