Norman,
Ok, getting closer now.

This:
Const sName As String = "MyNewSheet" ......... What does this refer to,
the new created sheet in WB 2?
Is it possible to set the
Set srcSH = ThisWorkbook.Sheets("Sheet1").............. Change this to the
CURRENT ACTIVE SHEET instead?

I am trying to filter out what i don't need.
I have setup the WB2 to run when opening a macro, that creates a msg box and
the value is the Name of the New Sheet.
I have this done.
I think some of what you posted may interfere with that, as ?? above.

The sheet name from WB1 will not be the same for each running of this code,
therefore i need the ACTIVE Sheet not one specific sheet name.

Hope i ma making sense.
Corey....
"Norman Jones" wrote in message
...
Hi Corey,

My code assumed that you were using a CommandButton (from the Controls
Toolbox). In this case, the keyword Me would have referred to, and
identified, the sheet containing the button.

As this is not the case, you need to define the source workbook and the
source sheet. Try the following version:

'=============
Private Sub Tester()
Dim WB As Workbook
Dim srcSH As Worksheet
Dim destSH As Worksheet
Dim srcRng As Range
Dim destRng As Range
Const sName As String = "MyNewSheet" '<<==== CHANGE

Set srcSH = ThisWorkbook.Sheets("Sheet1") '<<==== CHANGE
Set srcRng = srcSH.Range("A1:D10") '<<==== CHANGE

Set WB = Workbooks.Open(Filename:= _
"C:\B\AA\Test2.xls") '<<==== CHANGE

With WB
Set destSH = .WB.Sheets("YouNewSheetName")
Set destRng = destSH.Range("B1") '<<==== CHANGE
srcRng.Copy Destination:=destRng
SH.Name = sName
.Close SaveChanges:=True
End With
End Sub
'<<=============


---
Regards,
Norman



"Corey" wrote in message
...
Norman,
I am using what you posted minus the create a new sheet step, as i added
a macro to run when i open the workbook 2.
But the error i get is:

[Me.] in the Set srcRng = Me.Range("A1:D10") string .
If i drop off the [Me.] i get the error moving to [Set destRng =
SH.Range("C8")]
If i drop off the [SH.] i then get no eror, but nothing is cut and pasted
also??

Corey....

"Norman Jones" wrote in message
...
Hi Corey,

The code works for me without any problem.

Currenlty i have the wb to open and create a new sheet.
But inputing tht values from the 1st wb to the 2nd wb is NOT working?

If the code opens the 2nd workbook and creates the new sheet, then the
only code which affects the copy operation is contained in the lines:

Set srcRng = Me.Range("A1:D10") '<<==== CHANGE

and

Set destRng = SH.Range("B1") '<<==== CHANGE
srcRng.Copy Destination:=destRng


Assuming that you have correctly assigned the appropriate source and
destination ranges in the first two of these lines, the copy operation
shhould proceed without impediment.

Perhaps you could expand your explanation of: "NOT working"


BTW, as written, before re-running the suggested code, you would need to
delete or rename the new sheet. Subject to you successfuuly surmounting
your central problem, the code could readily be amended to include an
appropriate test / error handler to allow for the latter.


---
Regards,
Norman



"Corey" wrote in message
...
Thanks Norman, but i could not get that to work.
Currenlty i have the wb to open and create a new sheet.
But inputing tht values from the 1st wb to the 2nd wb is NOT working?

Corey....


"Norman Jones" wrote in message
...
Hi Corey,

2. Runs a macro in that Workbook(creates a new worksheet)

Why not also create the new sheet with the button code?

Try:

'=============
Private Sub CommandButton1_Click()
Dim WB As Workbook
Dim SH As Worksheet
Dim srcRng As Range
Dim destRng As Range
Const sName As String = "MyNewSheet" '<<==== CHANGE

Set srcRng = Me.Range("A1:D10") '<<==== CHANGE

Set WB = Workbooks.Open(Filename:= _
"C:\B\AA\Test2.xls") '<<====
CHANGE

With WB
Set SH = .Sheets.Add(After:=.Sheets(.Sheets.Count))
Set destRng = SH.Range("B1") '<<==== CHANGE
srcRng.Copy Destination:=destRng
SH.Name = sName
.Close SaveChanges:=True
End With
End Sub
'<<=============


---
Regards,
Norman


"Corey" wrote in message
...
OK,
I have created a Button to open the other workbook.

So how do i add the steps of,
Inputing some cell values from the workbook sheet that the button was
on and input these values into the now opened workbook cells. (Not
same cell refs though)

Corey....




Is this possible?
Workbook (a)
Workbook (b)
From Workbook (a):
I want to be able to click a button that:
1. Opens Workbook (b)
2. Runs a macro in that Workbook(creates a new worksheet)
3. Then input some of the cell values from the Workbook (a) sheet
into cells in Workbook (b)


Can this be done, or is it beyond Excel programming ?

Corey....