Macros--user choices

D

dnatoday

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 Before:=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
 
B

Bob Phillips

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 said:
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 Before:=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
Click to expand...
 
D

Don Guillett

Perhaps this will help with part of it. Probably best to just copy the
template or last sheet entirely and clear contents of the undesired info. In
any case you need to try to remove most or all of your selections

Sub gotolastmonthsheet()
x = Month(Date) - 1
y = Year(Date)
Sheets(x & Right(y, 2)).Select
End Sub
original
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
Click to expand...
example of removing selections
Sheets("0905").Columns("A:I").Copy _
Sheets("1005").Range("A1")


--
Don Guillett
SalesAid Software
(e-mail address removed)
dnatoday said:
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 Before:=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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

macro to copy rows that contain a cell with a certain number 2
"=TEXT(RC[-1],"MMM")" 4
Macro that deletes and pastes filterd rows and then some 2
Modify range in VBA 9
R1:C1 format 2
File Name changes with dates and Deleting Rows of Zeros 0
Complie Syntax Error 6
Macro: Copying to all possible rows 2

Top