Cell M8 in Sheet "Sched" is a variable from 1 to 12 (whole numbers only, representing months).
When a form control button is pressed, a selection from "Sched" (L8:Z41) is copied and its content is pasted (Value only) into another sheet in the same workbook. There are 12 additional sheets labeled 1 - 12
I would like the Macro to past the content into the correct sheet as stated in cell M8.
So if M8=3, I would like the content pasted into sheet "3".
Every other part of the Marco works, I just don't know how to select a sheet using a variable. I would like to avoid referencing the sheet by it's Index Value in case other sheets are added or re-arranged in the future.
My code is below with "???" where the variable should go.
_________________________________________
Range("L8:Z41").Select
Selection.Copy
Sheets("??????????????????").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("EnterSched").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
Please use code tags when posting code, see my signature or press the # button in advanced mode.
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
Cell M8 in Sheet "Sched" is a variable from 1 to 12 (whole numbers only, representing months).
When a form control button is pressed, a selection from "Sched" (L8:Z41) is copied and its content is pasted (Value only) into another sheet in the same workbook. There are 12 additional sheets labeled 1 - 12
I would like the Macro to past the content into the correct sheet as stated in cell M8.
So if M8=3, I would like the content pasted into sheet "3".
Every other part of the Marco works, I just don't know how to select a sheet using a variable. I would like to avoid referencing the sheet by it's Index Value in case other sheets are added or re-arranged in the future.
My code is below with "???" where the variable should go.
_________________________________________
Range("L8:Z41").Select Selection.Copy Sheets("??????????????????").Select Range("C4").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Sheets("EnterSched").Select Application.CutCopyMode = False ActiveWorkbook.Save
Hi adamNBU
Try this codeJohnSheets(Range("M8").Value).Select
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
I think that will select the sheet by its index number.Originally Posted by jaslake
Maybe:
Range("L8:Z41").Copy Sheets("" & Range("M8") & "").Range("C4").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False ActiveWorkbook.Save
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
John,
Dom was correct. Your code pastes the content in the sheet based from Index number; but that's good to know too, thanks!
Dom, it worked perfectly. Wow. Thanks!
Last edited by adamNBU; 04-01-2010 at 02:01 PM. Reason: wrong name
No worries.
Thinking about it this should also work:
Range("L8:Z41").Copy Sheets(Range("M8").Text).Range("C4").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False ActiveWorkbook.Save
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
Dom, it does work. Thank you again!
Maybe you could answer one smaller question I've been unable to solve.
I have a working macro named 'RecallMonth'. I would like it to run any time the value in M8 changes. M8 contains a conditional formula based from M9 which is a data-validation drop-down list (12 options). As users select different options from M9, the value from M8 will change and the Macro should run.
Though there are several forums that discuss this topic, most seem to work only when the cell (M8) is directly changed by the user and not via a formula. Any advice?
Big thanks in advance
Adam
As M9 is being changed you can just check for that:
Private Sub Worksheet_Change(ByVal Target As Range) If Target <> Range("M9") Then Exit Sub Call RecallMonth End Sub
Right click on the sheet tab, select View Code and paste the code onto the code page that appears.
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
Dom,
I followed your instructions. When I tested it and changed M9, there was an error. I pressed "debug" and this was highlighted
"If Target <> Range("M9") Then"
... By the way, the error reads "Run-time error '13': Type mismatch"
Odd, it works for me.
Another option is to use Intersect:
Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("M9")) Is Nothing Then Exit Sub Call RecallMonth End Sub
Don't suppose M9 is merged with another cell is it?
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
Dom, the second code with the "if intersect" worked perfectly.
Thank you again!! I'm amazed.
Dom, the macro you gave me has been working perfectly. I just want to add an unprotect/protect code to it and my guessing has led me nowhere.
I would like to add something like
toSheet1.Unprotect Password:="Secret" 'ORIGINAL MACRO Sheet1.Protect Password:="Secret"
Range("L8:Z41").Copy Sheets("" & Range("M8") & "").Range("C4").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False ActiveWorkbook.Save End Sub
Haven't tested this but try:
Dim myWs As Worksheet Set myWs = Sheets("" & Range("M8") & "") Range("L8:Z41").Copy With myWs .Unprotect Password:="Secret" .Range("C4").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False .Protect Password:="Secret" End With ActiveWorkbook.Save
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks