Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Selection
Hello all,
I'm pretty new to all this, so go easy :P I'm trying to write a macro that will allow me to not have to hardcode the sheet name into the program. Here is my code: Sub PasteLink() 'start on Sheet 1R Dim count As Integer count = 0 Application.Goto Sheets("Sheet 1E").Range(Selection.Address) Selection.Copy If Selection.Font.ColorIndex = 3 Then count = 1 End If Application.Goto Sheets("Sheet 1R").Range(Selection.Address) ActiveSheet.Paste Link:=True With Selection.Font .Name = "Arial" .Size = 8 .Bold = True If count = 1 Then .ColorIndex = 3 'change font to red Else: .ColorIndex = 0 'change font to black End If End With End Sub You see how I have Sheet 1E and Sheet 1R? I would like to make it so that those are generic, and it takes the active sheet's selection then goes to the sheet right before it, selects the same range, copies it, goes back to the original sheet, pastes link and formats cell. The current order of sheets a Sheet1 = "Sheet 1E"; Sheet2 = "Sheet 1R" (there are more sheets in the workbook titled in the same fasion: Sheet 2E, Sheet 2R, Sheet 3E, Sheet 3R). Sheet 1E and 1R are carbon copies of each other. Any help is greatly appreciated. Also, if you could add comments so I can learn what exactly the coding means, it would be very helpful. Thanks, Lance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Selection
Did you really want to apply the formatting yourself or did you want to copy the
format from the previous sheet? I'm guessing that you wanted the format from the previous sheet: Option Explicit Sub myPasteLink() Dim ActSheet As Worksheet Dim PrevSheet As Worksheet Dim iCtr As Long Set ActSheet = ActiveSheet Set PrevSheet = Nothing For iCtr = ActSheet.Index - 1 To 1 Step -1 If TypeName(Sheets(iCtr)) = "Worksheet" Then Set PrevSheet = Sheets(iCtr) Exit For End If Next iCtr If PrevSheet Is Nothing Then MsgBox "No previous sheet!" Exit Sub End If PrevSheet.Range(Selection.Address).Copy ActSheet.Paste link:=True Selection.PasteSpecial Paste:=xlPasteFormats End Sub Lance wrote: Hello all, I'm pretty new to all this, so go easy :P I'm trying to write a macro that will allow me to not have to hardcode the sheet name into the program. Here is my code: Sub PasteLink() 'start on Sheet 1R Dim count As Integer count = 0 Application.Goto Sheets("Sheet 1E").Range(Selection.Address) Selection.Copy If Selection.Font.ColorIndex = 3 Then count = 1 End If Application.Goto Sheets("Sheet 1R").Range(Selection.Address) ActiveSheet.Paste Link:=True With Selection.Font .Name = "Arial" .Size = 8 .Bold = True If count = 1 Then .ColorIndex = 3 'change font to red Else: .ColorIndex = 0 'change font to black End If End With End Sub You see how I have Sheet 1E and Sheet 1R? I would like to make it so that those are generic, and it takes the active sheet's selection then goes to the sheet right before it, selects the same range, copies it, goes back to the original sheet, pastes link and formats cell. The current order of sheets a Sheet1 = "Sheet 1E"; Sheet2 = "Sheet 1R" (there are more sheets in the workbook titled in the same fasion: Sheet 2E, Sheet 2R, Sheet 3E, Sheet 3R). Sheet 1E and 1R are carbon copies of each other. Any help is greatly appreciated. Also, if you could add comments so I can learn what exactly the coding means, it would be very helpful. Thanks, Lance -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Selection
That works. Thanks Dave
"Dave Peterson" wrote: Did you really want to apply the formatting yourself or did you want to copy the format from the previous sheet? I'm guessing that you wanted the format from the previous sheet: Option Explicit Sub myPasteLink() Dim ActSheet As Worksheet Dim PrevSheet As Worksheet Dim iCtr As Long Set ActSheet = ActiveSheet Set PrevSheet = Nothing For iCtr = ActSheet.Index - 1 To 1 Step -1 If TypeName(Sheets(iCtr)) = "Worksheet" Then Set PrevSheet = Sheets(iCtr) Exit For End If Next iCtr If PrevSheet Is Nothing Then MsgBox "No previous sheet!" Exit Sub End If PrevSheet.Range(Selection.Address).Copy ActSheet.Paste link:=True Selection.PasteSpecial Paste:=xlPasteFormats End Sub Lance wrote: Hello all, I'm pretty new to all this, so go easy :P I'm trying to write a macro that will allow me to not have to hardcode the sheet name into the program. Here is my code: Sub PasteLink() 'start on Sheet 1R Dim count As Integer count = 0 Application.Goto Sheets("Sheet 1E").Range(Selection.Address) Selection.Copy If Selection.Font.ColorIndex = 3 Then count = 1 End If Application.Goto Sheets("Sheet 1R").Range(Selection.Address) ActiveSheet.Paste Link:=True With Selection.Font .Name = "Arial" .Size = 8 .Bold = True If count = 1 Then .ColorIndex = 3 'change font to red Else: .ColorIndex = 0 'change font to black End If End With End Sub You see how I have Sheet 1E and Sheet 1R? I would like to make it so that those are generic, and it takes the active sheet's selection then goes to the sheet right before it, selects the same range, copies it, goes back to the original sheet, pastes link and formats cell. The current order of sheets a Sheet1 = "Sheet 1E"; Sheet2 = "Sheet 1R" (there are more sheets in the workbook titled in the same fasion: Sheet 2E, Sheet 2R, Sheet 3E, Sheet 3R). Sheet 1E and 1R are carbon copies of each other. Any help is greatly appreciated. Also, if you could add comments so I can learn what exactly the coding means, it would be very helpful. Thanks, Lance -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sheet selection | Excel Programming | |||
Sheet selection help | Excel Programming | |||
Calculate active sheet on sheet selection | Excel Programming | |||
Selection from list on main sheet from suplemental sheet in same w | New Users to Excel | |||
Sheet selection | Excel Programming |