What is the difference in running code within the 'This Workbook' routine
and a standard module?

I have code that is currently within the 'This Workbook' which fires on
opening - works great, comes back with the correct everything, yet when I
place the same exact same code within a standard module try to run it, a
specific part of the code does not run, that code from -
"Range("S2.S2").Copy" down - all I see is the cell highlighted as happens
when you select a cell to copy. Have I found a MS bug or what? The offending
code is as follows and is doing my head in! I can run this code on its own
and it WORKS, when I try to call it from my original macro - nothing
happens. The sheet isn't protected, I'm lost

Range("S2").Select
ActiveCell.Formula = "=PROPER(F2&"" ""&G2)"
Range("S2.S2").Copy
x = 2
Do Until Cells(x, 7).Value = ""
Cells(x, 19).PasteSpecial xlPasteFormulas
x = x + 1
Loop

You say you see the cell highlighted. I assume you see the cell S2
highlighted right?

Maybe you have the wrong sheet activated when you run the code in the
module.
and in that active sheet Cell G2 is already empty?
So if G2 is empty, your do loop will terminate immediately. So obviously
after
Range("S2.S2").Copy, there is nothing else done, no pasting, and you simply
see S2 highighted with formula "=PROPER(F2&"" ""&G2)".

Please note you are only refering Range(....) which means it will be
applied to
the active sheet.

In Thisworkbook - Workbook Open, by default the sheet which was active
when the workbook was saved, will be active when the workbook opens,
so here it will always be applied to that sheet, which my be the correct
sheet.

You should refer to the cell as Worksheets("Sheet Name").Range("S2"),
so you will always get it correct both in the Thisworkbook Workbook_Open
and in the module.

Sharad


"John" wrote in message
...
What is the difference in running code within the 'This Workbook' routine
and a standard module?

I have code that is currently within the 'This Workbook' which fires on
opening - works great, comes back with the correct everything, yet when I
place the same exact same code within a standard module try to run it, a
specific part of the code does not run, that code from -
"Range("S2.S2").Copy" down - all I see is the cell highlighted as happens
when you select a cell to copy. Have I found a MS bug or what? The
offending
code is as follows and is doing my head in! I can run this code on its own
and it WORKS, when I try to call it from my original macro - nothing
happens. The sheet isn't protected, I'm lost

Range("S2").Select
ActiveCell.Formula = "=PROPER(F2&"" ""&G2)"
Range("S2.S2").Copy
x = 2
Do Until Cells(x, 7).Value = ""
Cells(x, 19).PasteSpecial xlPasteFormulas
x = x + 1
Loop

Wish it was that simple Sharad, I've posted the full code previously in a
post but the sheet that I want active is set.

There are values in G2:G49 so no problem there. The required formula pops
into S2 but the copy won't work. I even tried inserting
"Sheets("Database").Select" - where S2 reside before "Range("S2").Select" -
but still the same result


"Sharad Naik" wrote in message
...
You say you see the cell highlighted. I assume you see the cell S2
highlighted right?

Maybe you have the wrong sheet activated when you run the code in the
module.
and in that active sheet Cell G2 is already empty?
So if G2 is empty, your do loop will terminate immediately. So obviously
after
Range("S2.S2").Copy, there is nothing else done, no pasting, and you
simply
see S2 highighted with formula "=PROPER(F2&"" ""&G2)".

Please note you are only refering Range(....) which means it will be
applied to
the active sheet.

In Thisworkbook - Workbook Open, by default the sheet which was active
when the workbook was saved, will be active when the workbook opens,
so here it will always be applied to that sheet, which my be the correct
sheet.

You should refer to the cell as Worksheets("Sheet Name").Range("S2"),
so you will always get it correct both in the Thisworkbook Workbook_Open
and in the module.

Sharad


"John" wrote in message
...
What is the difference in running code within the 'This Workbook'
routine
and a standard module?

I have code that is currently within the 'This Workbook' which fires on
opening - works great, comes back with the correct everything, yet when
I
place the same exact same code within a standard module try to run it, a
specific part of the code does not run, that code from -
"Range("S2.S2").Copy" down - all I see is the cell highlighted as
happens
when you select a cell to copy. Have I found a MS bug or what? The
offending
code is as follows and is doing my head in! I can run this code on its
own
and it WORKS, when I try to call it from my original macro - nothing
happens. The sheet isn't protected, I'm lost

Range("S2").Select
ActiveCell.Formula = "=PROPER(F2&"" ""&G2)"
Range("S2.S2").Copy
x = 2
Do Until Cells(x, 7).Value = ""
Cells(x, 19).PasteSpecial xlPasteFormulas
x = x + 1
Loop

If you want to run a macro in a different wb, use a
standard module...otherwise i thought there were pretty
much the same inside the same wb. so as to why the code
don't work when move... new one on me.
try changing "Range("S2.S2").copy" to "Range("S2").copy"
this should work for both standard and this workbook
module.
good luck

-----Original Message-----
What is the difference in running code within the 'This
Workbook' routine
and a standard module?

I have code that is currently within the 'This Workbook'
which fires on
opening - works great, comes back with the correct
everything, yet when I
place the same exact same code within a standard module
try to run it, a
specific part of the code does not run, that code from -
"Range("S2.S2").Copy" down - all I see is the cell
highlighted as happens
when you select a cell to copy. Have I found a MS bug or
what? The offending
code is as follows and is doing my head in! I can run
this code on its own
and it WORKS, when I try to call it from my original
macro - nothing
happens. The sheet isn't protected, I'm lost

Range("S2").Select
ActiveCell.Formula = "=PROPER(F2&"" ""&G2)"
Range("S2.S2").Copy
x = 2
Do Until Cells(x, 7).Value = ""
Cells(x, 19).PasteSpecial xlPasteFormulas
x = x + 1
Loop


.

Maybe it has something to do with Running a query within the code, although
the same query runs on open and it works fine


"John" wrote in message
...
Wish it was that simple Sharad, I've posted the full code previously in a
post but the sheet that I want active is set.

There are values in G2:G49 so no problem there. The required formula pops
into S2 but the copy won't work. I even tried inserting
"Sheets("Database").Select" - where S2 reside before
"Range("S2").Select" -
but still the same result


"Sharad Naik" wrote in message
...
You say you see the cell highlighted. I assume you see the cell S2
highlighted right?

Maybe you have the wrong sheet activated when you run the code in the
module.
and in that active sheet Cell G2 is already empty?
So if G2 is empty, your do loop will terminate immediately. So obviously
after
Range("S2.S2").Copy, there is nothing else done, no pasting, and you
simply
see S2 highighted with formula "=PROPER(F2&"" ""&G2)".

Please note you are only refering Range(....) which means it will be
applied to
the active sheet.

In Thisworkbook - Workbook Open, by default the sheet which was active
when the workbook was saved, will be active when the workbook opens,
so here it will always be applied to that sheet, which my be the correct
sheet.

You should refer to the cell as Worksheets("Sheet Name").Range("S2"),
so you will always get it correct both in the Thisworkbook
Workbook_Open
and in the module.

Sharad


"John" wrote in message
...
What is the difference in running code within the 'This Workbook'
routine
and a standard module?

I have code that is currently within the 'This Workbook' which fires
on
opening - works great, comes back with the correct everything, yet
when
I
place the same exact same code within a standard module try to run it,
a
specific part of the code does not run, that code from -
"Range("S2.S2").Copy" down - all I see is the cell highlighted as
happens
when you select a cell to copy. Have I found a MS bug or what? The
offending
code is as follows and is doing my head in! I can run this code on its
own
and it WORKS, when I try to call it from my original macro - nothing
happens. The sheet isn't protected, I'm lost

Range("S2").Select
ActiveCell.Formula = "=PROPER(F2&"" ""&G2)"
Range("S2.S2").Copy
x = 2
Do Until Cells(x, 7).Value = ""
Cells(x, 19).PasteSpecial xlPasteFormulas
x = x + 1
Loop

try this idea

Sub balance()
Set frng = Range("h8:h" & Range("a65536").End(xlUp).Row)
With frng
.Formula = "yourformulahere"
'.Formula = .Value 'change to value if desired.
End With
End Sub

--
Don Guillett
SalesAid Software

"John" wrote in message
...
What is the difference in running code within the 'This Workbook' routine
and a standard module?

I have code that is currently within the 'This Workbook' which fires on
opening - works great, comes back with the correct everything, yet when I
place the same exact same code within a standard module try to run it, a
specific part of the code does not run, that code from -
"Range("S2.S2").Copy" down - all I see is the cell highlighted as happens
when you select a cell to copy. Have I found a MS bug or what? The
offending
code is as follows and is doing my head in! I can run this code on its own
and it WORKS, when I try to call it from my original macro - nothing
happens. The sheet isn't protected, I'm lost

Range("S2").Select
ActiveCell.Formula = "=PROPER(F2&"" ""&G2)"
Range("S2.S2").Copy
x = 2
Do Until Cells(x, 7).Value = ""
Cells(x, 19).PasteSpecial xlPasteFormulas
x = x + 1
Loop

I copied the exact code as you posted, in Thisworkbook WorkBook_Open and
also in a module.
It works both places without problem.

Sharad

"John" wrote in message
...
Maybe it has something to do with Running a query within the code,
although
the same query runs on open and it works fine


"John" wrote in message
...
Wish it was that simple Sharad, I've posted the full code previously in a
post but the sheet that I want active is set.

There are values in G2:G49 so no problem there. The required formula pops
into S2 but the copy won't work. I even tried inserting
"Sheets("Database").Select" - where S2 reside before
"Range("S2").Select" -
but still the same result


"Sharad Naik" wrote in message
...
You say you see the cell highlighted. I assume you see the cell S2
highlighted right?

Maybe you have the wrong sheet activated when you run the code in the
module.
and in that active sheet Cell G2 is already empty?
So if G2 is empty, your do loop will terminate immediately. So
obviously
after
Range("S2.S2").Copy, there is nothing else done, no pasting, and you
simply
see S2 highighted with formula "=PROPER(F2&"" ""&G2)".

Please note you are only refering Range(....) which means it will be
applied to
the active sheet.

In Thisworkbook - Workbook Open, by default the sheet which was active
when the workbook was saved, will be active when the workbook opens,
so here it will always be applied to that sheet, which my be the
correct
sheet.

You should refer to the cell as Worksheets("Sheet Name").Range("S2"),
so you will always get it correct both in the Thisworkbook
Workbook_Open
and in the module.

Sharad


"John" wrote in message
...
What is the difference in running code within the 'This Workbook'
routine
and a standard module?

I have code that is currently within the 'This Workbook' which fires
on
opening - works great, comes back with the correct everything, yet
when
I
place the same exact same code within a standard module try to run
it,
a
specific part of the code does not run, that code from -
"Range("S2.S2").Copy" down - all I see is the cell highlighted as
happens
when you select a cell to copy. Have I found a MS bug or what? The
offending
code is as follows and is doing my head in! I can run this code on
its
own
and it WORKS, when I try to call it from my original macro - nothing
happens. The sheet isn't protected, I'm lost

Range("S2").Select
ActiveCell.Formula = "=PROPER(F2&"" ""&G2)"
Range("S2.S2").Copy
x = 2
Do Until Cells(x, 7).Value = ""
Cells(x, 19).PasteSpecial xlPasteFormulas
x = x + 1
Loop

Sharad

There is a query part to the code which obviously you can't mimic, I've
extracted out just the Copy paste sections and it works fine, but as part of
the query code it doesn't within a module


"Sharad Naik" wrote in message
...
I copied the exact code as you posted, in Thisworkbook WorkBook_Open and
also in a module.
It works both places without problem.

Sharad

"John" wrote in message
...
Maybe it has something to do with Running a query within the code,
although
the same query runs on open and it works fine


"John" wrote in message
...
Wish it was that simple Sharad, I've posted the full code previously in
a
post but the sheet that I want active is set.

There are values in G2:G49 so no problem there. The required formula
pops
into S2 but the copy won't work. I even tried inserting
"Sheets("Database").Select" - where S2 reside before
"Range("S2").Select" -
but still the same result


"Sharad Naik" wrote in message
...
You say you see the cell highlighted. I assume you see the cell S2
highlighted right?

Maybe you have the wrong sheet activated when you run the code in the
module.
and in that active sheet Cell G2 is already empty?
So if G2 is empty, your do loop will terminate immediately. So
obviously
after
Range("S2.S2").Copy, there is nothing else done, no pasting, and you
simply
see S2 highighted with formula "=PROPER(F2&"" ""&G2)".

Please note you are only refering Range(....) which means it will be
applied to
the active sheet.

In Thisworkbook - Workbook Open, by default the sheet which was
active
when the workbook was saved, will be active when the workbook opens,
so here it will always be applied to that sheet, which my be the
correct
sheet.

You should refer to the cell as Worksheets("Sheet
Name").Range("S2"),
so you will always get it correct both in the Thisworkbook
Workbook_Open
and in the module.

Sharad


"John" wrote in message
...
What is the difference in running code within the 'This Workbook'
routine
and a standard module?

I have code that is currently within the 'This Workbook' which
fires
on
opening - works great, comes back with the correct everything, yet
when
I
place the same exact same code within a standard module try to run
it,
a
specific part of the code does not run, that code from -
"Range("S2.S2").Copy" down - all I see is the cell highlighted as
happens
when you select a cell to copy. Have I found a MS bug or what? The
offending
code is as follows and is doing my head in! I can run this code on
its
own
and it WORKS, when I try to call it from my original macro -
nothing
happens. The sheet isn't protected, I'm lost

Range("S2").Select
ActiveCell.Formula = "=PROPER(F2&"" ""&G2)"
Range("S2.S2").Copy
x = 2
Do Until Cells(x, 7).Value = ""
Cells(x, 19).PasteSpecial xlPasteFormulas
x = x + 1
Loop

Looks like everyone else gets it to work - so it has to be other than the
code you show.

--
Regards,
Tom Ogilvy

"John" wrote in message
...
Sharad

There is a query part to the code which obviously you can't mimic, I've
extracted out just the Copy paste sections and it works fine, but as part
of
the query code it doesn't within a module


"Sharad Naik" wrote in message
...
I copied the exact code as you posted, in Thisworkbook WorkBook_Open and
also in a module.
It works both places without problem.

Sharad

"John" wrote in message
...
Maybe it has something to do with Running a query within the code,
although
the same query runs on open and it works fine


"John" wrote in message
...
Wish it was that simple Sharad, I've posted the full code previously
in
a
post but the sheet that I want active is set.

There are values in G2:G49 so no problem there. The required formula
pops
into S2 but the copy won't work. I even tried inserting
"Sheets("Database").Select" - where S2 reside before
"Range("S2").Select" -
but still the same result


"Sharad Naik" wrote in message
...
You say you see the cell highlighted. I assume you see the cell S2
highlighted right?

Maybe you have the wrong sheet activated when you run the code in
the
module.
and in that active sheet Cell G2 is already empty?
So if G2 is empty, your do loop will terminate immediately. So
obviously
after
Range("S2.S2").Copy, there is nothing else done, no pasting, and
you
simply
see S2 highighted with formula "=PROPER(F2&"" ""&G2)".

Please note you are only refering Range(....) which means it will
be
applied to
the active sheet.

In Thisworkbook - Workbook Open, by default the sheet which was
active
when the workbook was saved, will be active when the workbook
opens,
so here it will always be applied to that sheet, which my be the
correct
sheet.

You should refer to the cell as Worksheets("Sheet
Name").Range("S2"),
so you will always get it correct both in the Thisworkbook
Workbook_Open
and in the module.

Sharad


"John" wrote in message
...
What is the difference in running code within the 'This Workbook'
routine
and a standard module?

I have code that is currently within the 'This Workbook' which
fires
on
opening - works great, comes back with the correct everything,
yet
when
I
place the same exact same code within a standard module try to
run
it,
a
specific part of the code does not run, that code from -
"Range("S2.S2").Copy" down - all I see is the cell highlighted as
happens
when you select a cell to copy. Have I found a MS bug or what?
The
offending
code is as follows and is doing my head in! I can run this code
on
its
own
and it WORKS, when I try to call it from my original macro -
nothing
happens. The sheet isn't protected, I'm lost

Range("S2").Select
ActiveCell.Formula = "=PROPER(F2&"" ""&G2)"
Range("S2.S2").Copy
x = 2
Do Until Cells(x, 7).Value = ""
Cells(x, 19).PasteSpecial xlPasteFormulas
x = x + 1
Loop