Thread
:
Selecting worksheet and ranges within sheet to run macro on
View Single Post
#
2
Posted to microsoft.public.excel.programming
Don Guillett
external usenet poster
Posts: 10,124
Selecting worksheet and ranges within sheet to run macro on
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.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Jacky D." wrote in message
...
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.
Reply With Quote
Don Guillett
View Public Profile
Find all posts by Don Guillett