Macro within a macro and Macro Prompts

[Guest]

Is there any way to create a macro that prompts users for certain information
or requests users to do a specifice task? I'm asking because I need to create
a macro that compiles data from multiple files then sorts/organizes said
data. The problem is that neither the file naming nor the layout of the data
within the spreadsheet is standardized (i.e. Net Income may be found in cell
J52 on "Q01-05 Net Income.xls" and N87 on "Q2-2005 NI.xls").

Also, is it possible to run a macro within a macro? I guess that would just
be extra steps within one big macro, but if possible I'd like to break steps
down into individual macros and then just have one master macro.

Thanks in advance

comparini3000

 

[Don Guillett]

for prompts have a look at INPUTBOX. As to multimacros
sub mymastermacro()
do something
call macro1
call macro2
etc
end sub

 

[Guest]

thank you very much don. i have one more question...
if i have a list in column A...say it goes from A2 to A16. if the user puts
in a value in cell A1 what should i do to have the macro cut the value in A1
and move it to A17?

 

[Don Guillett]

If you mean automatically, then
right click sheet tab>view code>insert this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Application.EnableEvents = False
lr = Cells(Rows.Count, "a").End(xlUp).Row + 1
Target.Cut Cells(lr, "a")
'or
'Range("a" & lr).Value = Target
'Target = ""
Application.EnableEvents = True
End Sub