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

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

Where FName is the path to the second workbook, the macro in the second
workbook is called Test2, and you want to copy cells A1:C1 from Sheet1 to
Sheet1!A1 of your second book, this should help you some:

Sub test()
Const FName = "I:\Excel\Book2.xls"
Dim wkbTest As Workbook

Set wkbTest = Workbooks.Open(FName)
Application.Run (wkbTest.Name & "!" & "Test2")

ThisWorkbook.Sheets("Sheet1").Range("A1:C1").Copy _
wkbTest.Sheets("Sheet1").Range("A1")

wkbTest.Close savechanges:=True

End Sub


"Corey" wrote:

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

You could try something like:

Workbooks(2).Worksheets(1).Range("A1")
Workbooks(1).Worksheets(1).Range("C3")

OR

Dim Data1
Data1 = Workbooks(1).Worksheets(1).Range("C3")
Workbooks(2).Worksheets(1).Range("A1") = Data1

(I haven't test this, it's just a suggestion)

Kartune8

--
kartune8
-----------------------------------------------------------------------
kartune85's Profile: http://www.excelforum.com/member.php...fo&userid=3558
View this thread: http://www.excelforum.com/showthread.php?threadid=55437

Application.Run (wkbTest.Name & "!" & "Test2")

Thanks for the reply.
The baove code i get an error.
I have renamed the value of Test2 to the macor name in workbook2.

I can get the following:

Opens 2nd workbook
Creates a new worksheet
But i get NO values from the 1st workbook worksheet input intot he 2nd
workbook worksheet.

The cells i have inputing values from ARE NOT the same cell references in
both sheets.
So workbook worksheet1 "Z48" = workbook worksheet2 "J60"


???
Any ideas
Corey....
"JMB" wrote in message
...
Where FName is the path to the second workbook, the macro in the second
workbook is called Test2, and you want to copy cells A1:C1 from Sheet1 to
Sheet1!A1 of your second book, this should help you some:

Sub test()
Const FName = "I:\Excel\Book2.xls"
Dim wkbTest As Workbook

Set wkbTest = Workbooks.Open(FName)
Application.Run (wkbTest.Name & "!" & "Test2")

ThisWorkbook.Sheets("Sheet1").Range("A1:C1").Copy _
wkbTest.Sheets("Sheet1").Range("A1")

wkbTest.Close savechanges:=True

End Sub


"Corey" wrote:

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

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

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

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

The cells i have inputing values from ARE NOT the same cell references in
both sheets.

I was assuming you would change the cell references.

Workbook1.Sheets("Sheet1").Range("Z48").Value =
Workbook2.Sheets("Sheet2").Range("J60").Value

Where Workbook1 and Workbook2 are object variables that are set to the
appropriate workbooks.

Or

Workbook2.Sheets("Sheet2").Range("J60").Copy
Workbook1.Sheets("Sheet1").Range("Z48")

Again Workbook1 and Workbook2 are object variables.

Or,

Set WB = Workbooks.Open("Filename")
Set Rng1 = WB.Sheets("Sheet2").Range("J60")
Set Rng2 = Thisworkbook.Sheets("Sheet1").Range("Z48")
Rng1.Copy Rng2

Maybe post some of the code you are using instead of just stating the
suggestions thus far are not working. If you are getting an error message -
what does it say? Since you are able to open the workbook and add sheets, I
assume you no longer need to run the macro in the second book.


"Corey" wrote:

Application.Run (wkbTest.Name & "!" & "Test2")

Thanks for the reply.
The baove code i get an error.
I have renamed the value of Test2 to the macor name in workbook2.

I can get the following:

Opens 2nd workbook
Creates a new worksheet
But i get NO values from the 1st workbook worksheet input intot he 2nd
workbook worksheet.

The cells i have inputing values from ARE NOT the same cell references in
both sheets.
So workbook worksheet1 "Z48" = workbook worksheet2 "J60"


???
Any ideas
Corey....
"JMB" wrote in message
...
Where FName is the path to the second workbook, the macro in the second
workbook is called Test2, and you want to copy cells A1:C1 from Sheet1 to
Sheet1!A1 of your second book, this should help you some:

Sub test()
Const FName = "I:\Excel\Book2.xls"
Dim wkbTest As Workbook

Set wkbTest = Workbooks.Open(FName)
Application.Run (wkbTest.Name & "!" & "Test2")

ThisWorkbook.Sheets("Sheet1").Range("A1:C1").Copy _
wkbTest.Sheets("Sheet1").Range("A1")

wkbTest.Close savechanges:=True

End Sub


"Corey" wrote:

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

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

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

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

Hi Corey,

Const sName As String = "MyNewSheet"

You can delete this declaration. It is a relic of the CommandButton code
which, inter alia, created a new worksheet and named it with the value of
the sName constant.

Is it possible to set the
Set srcSH = ThisWorkbook.Sheets("Sheet1").............. Change this to
the CURRENT ACTIVE SHEET instead?

Sure, replace the line with:

Set srcSH = ActiveSheet


---
Regards,
Norman



"Corey" wrote in message
...
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....

Ok, thanx
I get a little way down the code before i get another error in:
Set destSH = .WB.Sheets("100101")
The "100101" is a value i placed in there, but assuming that it refers to
the Newly created sheet in wb2.
I have another macro that creates this and a msg box that gives the sheet
name value.
Can this be adapted to suit somehow?

Regards

Corey

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

Const sName As String = "MyNewSheet"

You can delete this declaration. It is a relic of the CommandButton code
which, inter alia, created a new worksheet and named it with the value of
the sName constant.

Is it possible to set the
Set srcSH = ThisWorkbook.Sheets("Sheet1").............. Change this to
the CURRENT ACTIVE SHEET instead?

Sure, replace the line with:

Set srcSH = ActiveSheet


---
Regards,
Norman



"Corey" wrote in message
...
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....

Hi Corey,

In order to copy to the newly crated sheet, it is necessary to know either
the name of the new sheet or its position.

IMO, the best option is to adopt my original suggestion, and create the new
sheet as part of the button code in the first workbook. Alternatively, post
the 2nd workbook open event code which creates the new sheet and I will
adapt the suggested current code.


---
Regards,
Norman



"Corey" wrote in message
...
Ok, thanx
I get a little way down the code before i get another error in:
Set destSH = .WB.Sheets("100101")
The "100101" is a value i placed in there, but assuming that it refers to
the Newly created sheet in wb2.
I have another macro that creates this and a msg box that gives the sheet
name value.
Can this be adapted to suit somehow?

Regards

Corey

Code when opening
Private Sub Workbook_Open()
Macro5
End Sub


and


code for Macro5

Sub Macro5()
'
' Macro1 Macro
' Macro recorded 20/06/2006 by Corey
'
Dim sh As Worksheet
Dim msg As String, sName As String
msg = "Enter the Next Quote Number...."
Do
sName = InputBox(msg)
If sName = "" Then Exit Sub
On Error Resume Next
Set sh = Worksheets(sName)
On Error GoTo 0
msg = "Quote Number has been used, try again: "
Loop While Not sh Is Nothing
With ActiveWorkbook
.Worksheets("STQ Template").Copy After:=.Worksheets(.Worksheets.Count)
End With
ActiveSheet.Name = sName
[I12].Select
ActiveCell.Value = sName

'
End Sub




To the above i am trying to have a MAX value of [I12] in ALL sheets,
displayed on the sheet that originally opens and this wil be the next number
to enter into the msg box above in Macro5
Not able to as yet, still manually doing this.

Regards

Corey


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

In order to copy to the newly crated sheet, it is necessary to know either
the name of the new sheet or its position.

IMO, the best option is to adopt my original suggestion, and create the
new sheet as part of the button code in the first workbook. Alternatively,
post the 2nd workbook open event code which creates the new sheet and I
will adapt the suggested current code.


---
Regards,
Norman



"Corey" wrote in message
...
Ok, thanx
I get a little way down the code before i get another error in:
Set destSH = .WB.Sheets("100101")
The "100101" is a value i placed in there, but assuming that it refers to
the Newly created sheet in wb2.
I have another macro that creates this and a msg box that gives the sheet
name value.
Can this be adapted to suit somehow?

Regards

Corey

Hi Corey,

Try therefo

'=============
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 = .Sheets(.Sheets.Count)
Set destRng = destSH.Range("B1") '<<==== CHANGE
srcRng.Copy Destination:=destRng
.Close SaveChanges:=True
End With
End Sub
'<<=============


I do not understand:

To the above i am trying to have a MAX value of [I12] in ALL sheets,
displayed on the sheet that originally opens and this wil be the next
number to enter into the msg box above in Macro5
Not able to as yet, still manually doing this.

However, this should have no bearing on your original question or the
suggested code.

---
Regards,
Norman



"Corey" wrote in message
...
Code when opening
Private Sub Workbook_Open()
Macro5
End Sub


and


code for Macro5

Sub Macro5()
'
' Macro1 Macro
' Macro recorded 20/06/2006 by Corey
'
Dim sh As Worksheet
Dim msg As String, sName As String
msg = "Enter the Next Quote Number...."
Do
sName = InputBox(msg)
If sName = "" Then Exit Sub
On Error Resume Next
Set sh = Worksheets(sName)
On Error GoTo 0
msg = "Quote Number has been used, try again: "
Loop While Not sh Is Nothing
With ActiveWorkbook
.Worksheets("STQ Template").Copy After:=.Worksheets(.Worksheets.Count)
End With
ActiveSheet.Name = sName
[I12].Select
ActiveCell.Value = sName

'
End Sub




To the above i am trying to have a MAX value of [I12] in ALL sheets,
displayed on the sheet that originally opens and this wil be the next
number to enter into the msg box above in Macro5
Not able to as yet, still manually doing this.

Regards

Corey