Thank you Both very much, I think I can make these work for what I'm trying
to do.

Thanks again,

Jacky D.

"Joel" wrote:

Try this

Sub CopyData()

FiletoOpen = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls")
If FiletoOpen = False Then
MsgBox ("Cannot Open File - Exiting Macro")
Exit Sub
End If

Set SourceBk = Workbooks.Open(Filename:=FiletoOpen)
Set SourceSht = SourceBk.Sheets("Validation")

ThisWorkbook.Sheets("Validation").Select


With SourceSht
Set myRange = Application.InputBox( _
prompt:="Select Destination 1", Type:=8)

.Range("A3:AA3").Copy

myRange.PasteSpecial _
Paste:=xlAll, _
Transpose:=True

.Range("A29:AA29").Copy
Set myRange = Application.InputBox( _
prompt:="Select Destination 2", Type:=8)
myRange.PasteSpecial _
Paste:=xlAll, _
Transpose:=True

Set myRange = Application.InputBox( _
prompt:="Select Destination 3", Type:=8)
.Range("A1:AA1").Copy

myRange.PasteSpecial _
Paste:=xlAll, _
Transpose:=True
End With
End Sub




"Jacky D." wrote:

. 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.