Selecting worksheet and ranges within sheet to run macro on

[Jacky D.]

I am trying to automate a sheet in a workbook that has worksheets being added
to it continually. I have created a worksheet that is designed to pull
infomation from a selected workbook from 3 different areas of the worksheet.
I have recorded a macro that goes into the workbook that the macro was
recorded with, and pulls information as internded. Heres my problem. Can I
change this macro so I can:
1-Select the workbook that the information is to be pulled from
2-Make the second range selection a variable? I want to tell it which cells
to copy from.

Here is the recorded nacro:
Copy_from_balance_for_Validation Macro
' Macro recorded 5/21/2009 by Jacky D
'

'

Range("A8").Select
Sheets("Sheet2").Select
Range("A3:AA3").Select
Range("AA3").Activate
Selection.Copy
Sheets("Validation").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Range("B8").Select
Sheets("SHEET2").Select
ActiveWindow.ScrollColumn = 83
Range("A29:AA29").Select
Range("AA29").Activate
Application.CutCopyMode = False
Selection.Copy
Sheets("Validation").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("C8").Select
Sheets("SHEET2").Select
Range("A1:AA1").Select
Range("AA1").Activate
Application.CutCopyMode = False
Selection.Copy
Sheets("Validation").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
End Sub


Any row in the macro above that reads "Sheets("Sheet2").Select" I would want
to make a variable. The
Sheets("SHEET2").Select
ActiveWindow.ScrollColumn = 83
Range("A29:AA29").Select
Range("AA29").Activate"
I would also want to select the range within the workbook. I know I need to
define the worksheet that information is to be pulled from, and then the
range and use an inputbox to do this, but I can't seem to get it right.

Thanks so much for the help.

Jacky D.

 

[Don Guillett]

You could have a drop down list (tied to a change_event macro) to select the
wordbook to fetch from. Then an input box asking for the sheet and range to
copy from, etc. You should endeavor to remove all unnecessary "selections"
and the scrolling is completely unnecessary.

 

[Jacky D.]

Thanks, I think a drop down list could become unwieldly, there are currently
45 worksheets in this workbook, and more are added every day. They are named
uniguely, and when created, they are placed near the sheet that I will run
the macro within. I have no idea how to create a change event macro. I don't
know how to write in VBA, but have been pretty successful in recording
macros, and then tweaking to suit my needs. Wouldn't I want to keep the
selections that are pointing to the cell ranges which I do not want to change?

 

[Don Guillett]

I meant to use the drop down with list to select the file to copy from.
Then use an input box (or list to prevent errors) to define the sheet (IF
not known)
Then use an input box (or list to prevent errors) to define the range
Or, seek professional help.