| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros--user choices
Hello,
I am brand new here and working a new job where I need to get something right. I hope I have come to the right place for help. I am writing an excel macro where I want to format a new workssheet & have it add some columns from a previous worksheet. I have written the parts to format the sheet & to excute the vlookup that retrieves the sheets. This workbook is a monthly report where the added columns are information from the previous month. My issue is this: when I wrote the macro (using the wizard) I told it to go to the worksheet '0905' for the lookup. What I would like it to do is to go to the previous month (eg, to run the macro today it should go to '1005'). Is there someway I can automatically build it in to look at the worksheet that is right before the open worksheet, or is there a way for a box to pop up where the user can enter the name of the sheet from which they want the current sheet to look up? Thanks for all your help. I have included my macro below. Sub TST_TERAPPT() ' ' TST_TERAPPT Macro ' Macro recorded 10/12/2005 by ddm ' ' Keyboard Shortcut: Ctrl+Shift+W ' Sheets("1005").Select Sheets("1005").Copy Befo=Workbooks("test0905.xls").Sheets(1) Columns("C:D").Select Selection.Delete Shift:=xlToLeft Columns("D:E").Select Selection.Delete Shift:=xlToLeft Columns("F:I").Select Selection.Delete Shift:=xlToLeft ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Sheets("0905").Select Columns("A:I").Select Selection.Copy Sheets("1005").Select Range("A1").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Range("G2").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=VLOOKUP('1005'!RC[-6],'0905'!C[-6]:C[2],7)" Range("H2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],'0905'!C[-7]:C[1],8)" Range("I2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-8],'0905'!C[-8]:C,9)" Range("G2").Select Selection.AutoFill Destination:=Range("G2:G170") Range("G2:G170").Select Range("H2").Select Selection.AutoFill Destination:=Range("H2:H170") Range("H2:H170").Select Range("I2").Select Selection.AutoFill Destination:=Range("I2:I170") Range("I2:I170").Select Sheets("0905").Select Range("G1:I1").Select Selection.Copy Sheets("1005").Select Range("G1").Select ActiveSheet.Paste End Sub Sub Macro2() ' ' Macro2 Macro ' Macro recorded 10/12/2005 by ddm ' ' ActiveCell.FormulaR1C1 = "=VLOOKUP('1005'!RC[-6],'0905'!C[-6]:C[2],7)" Range("H2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],'0905'!C[-7]:C[1],8)" Range("I2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-8],'0905'!C[-8]:C,9)" Range("G2").Select Selection.AutoFill Destination:=Range("G2:G170") Range("G2:G170").Select Range("H2").Select Selection.AutoFill Destination:=Range("H2:H170") Range("H2:H170").Select Range("I2").Select Selection.AutoFill Destination:=Range("I2:I170") Range("I2:I170").Select Sheets("0905").Select Range("G1:I1").Select Selection.Copy Sheets("1005").Select Range("G1").Select ActiveSheet.Paste End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macros--user choices
You can get the previous month with
format(dateserial(year(date),month(date)-1,1),"mmyy") so you activate that sheet with sSheet = format(dateserial(year(date),month(date)-1,1),"mmyy") Worksheets(sSheet).Activate -- HTH RP (remove nothere from the email address if mailing direct) "dnatoday" wrote in message ups.com... Hello, I am brand new here and working a new job where I need to get something right. I hope I have come to the right place for help. I am writing an excel macro where I want to format a new workssheet & have it add some columns from a previous worksheet. I have written the parts to format the sheet & to excute the vlookup that retrieves the sheets. This workbook is a monthly report where the added columns are information from the previous month. My issue is this: when I wrote the macro (using the wizard) I told it to go to the worksheet '0905' for the lookup. What I would like it to do is to go to the previous month (eg, to run the macro today it should go to '1005'). Is there someway I can automatically build it in to look at the worksheet that is right before the open worksheet, or is there a way for a box to pop up where the user can enter the name of the sheet from which they want the current sheet to look up? Thanks for all your help. I have included my macro below. Sub TST_TERAPPT() ' ' TST_TERAPPT Macro ' Macro recorded 10/12/2005 by ddm ' ' Keyboard Shortcut: Ctrl+Shift+W ' Sheets("1005").Select Sheets("1005").Copy Befo=Workbooks("test0905.xls").Sheets(1) Columns("C:D").Select Selection.Delete Shift:=xlToLeft Columns("D:E").Select Selection.Delete Shift:=xlToLeft Columns("F:I").Select Selection.Delete Shift:=xlToLeft ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Sheets("0905").Select Columns("A:I").Select Selection.Copy Sheets("1005").Select Range("A1").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Range("G2").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=VLOOKUP('1005'!RC[-6],'0905'!C[-6]:C[2],7)" Range("H2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],'0905'!C[-7]:C[1],8)" Range("I2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-8],'0905'!C[-8]:C,9)" Range("G2").Select Selection.AutoFill Destination:=Range("G2:G170") Range("G2:G170").Select Range("H2").Select Selection.AutoFill Destination:=Range("H2:H170") Range("H2:H170").Select Range("I2").Select Selection.AutoFill Destination:=Range("I2:I170") Range("I2:I170").Select Sheets("0905").Select Range("G1:I1").Select Selection.Copy Sheets("1005").Select Range("G1").Select ActiveSheet.Paste End Sub Sub Macro2() ' ' Macro2 Macro ' Macro recorded 10/12/2005 by ddm ' ' ActiveCell.FormulaR1C1 = "=VLOOKUP('1005'!RC[-6],'0905'!C[-6]:C[2],7)" Range("H2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],'0905'!C[-7]:C[1],8)" Range("I2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-8],'0905'!C[-8]:C,9)" Range("G2").Select Selection.AutoFill Destination:=Range("G2:G170") Range("G2:G170").Select Range("H2").Select Selection.AutoFill Destination:=Range("H2:H170") Range("H2:H170").Select Range("I2").Select Selection.AutoFill Destination:=Range("I2:I170") Range("I2:I170").Select Sheets("0905").Select Range("G1:I1").Select Selection.Copy Sheets("1005").Select Range("G1").Select ActiveSheet.Paste End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Defining user access (via user id) using macros | Excel Discussion (Misc queries) | |||
| Using macros in a second user account | Excel Discussion (Misc queries) | |||
| Choices for User | Excel Worksheet Functions | |||
| Excel User Form Macros | Excel Programming | |||
| Prevent user from disabling macros | Excel Programming | |||
Linear Mode
