making a worksheet & certain ranges within a variable

[Jacky D.]

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

 

[Bernie Deitrick]

Jacky,

You can do it with a macro like the one below - but your macro doesn't show where you want to paste
the data. I have just coded this so that it put the values at the bottom of column B. When you are
done copying cells, click "Cancel" on the inputbox....

HTH,
Bernie
MS Excel MVP


Sub JackyMacro()
Dim mySht As Worksheet
Dim myR As Range
On Error GoTo NoCells
CopyCells:
Set myR = Application.InputBox("Select the cell(s) to copy", Type:=8)
If myR Is Nothing Then Exit Sub
Set mySht = myR.Parent
myR.Copy
Sheets("Validation").Cells(Rows.Count, 2).End(xlUp)(2).PasteSpecial _
Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
mySht.Select
myR.Select
GoTo CopyCells
NoCells:
End Sub

 

[Joel]

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

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

Thanks again,

Jacky D.