Updating data automatically using macro
 

Hello,

I created a link between two worksheet in the same
workbook using macro. The main worksheet where the data
is entered should update certain columns in another
worksheet.

When the macro is run, it should do the updation
automatically but its not working. Below is the macro
which I created. I really don't know what's wrong with it.

-----------
Sub DailyMOPS()
Application.Goto Reference:="mthProdDateRange"
Selection.Copy
Windows("MOPS.xls").Activate
Sheets("Daily MOPS").Select
Range("A5").Select
ActiveSheet.Paste
Application.Goto Reference:="mthULG97Range"
Selection.Copy
Windows("MOPS.xls").Activate
Sheets("Daily MOPS").Select
Range("B5").Select
ActiveSheet.Paste
Application.Goto Reference:="mthULG92Range"
Selection.Copy
Windows("MOPS.xls").Activate
Sheets("Daily MOPS").Select
Range("C5").Select
ActiveSheet.Paste
Application.Goto Reference:="mthKeroRange"
Selection.Copy
Windows("MOPS.xls").Activate
Sheets("Daily MOPS").Select
Range("D5").Select
ActiveSheet.Paste
Application.Goto Reference:="mthGORange"
Selection.Copy
Windows("MOPS.xls").Activate
Sheets("Daily MOPS").Select
Range("E5").Select
ActiveSheet.Paste
Application.Goto Reference:="mthGO25Range"
Selection.Copy
Windows("MOPS.xls").Activate
Sheets("Daily MOPS").Select
Range("F5").Select
ActiveSheet.Paste
Application.Goto Reference:="mthNaphthaRange"
Selection.Copy
Windows("MOPS.xls").Activate
Sheets("Daily MOPS").Select
Range("G5").Select
ActiveSheet.Paste
Application.Goto Reference:="mth180Range"
Selection.Copy
Windows("MOPS.xls").Activate
Sheets("Daily MOPS").Select
Range("H5").Select
ActiveSheet.Paste
Application.Goto Reference:="mthLSWRCrkRange"
Selection.Copy
Windows("MOPS.xls").Activate
Sheets("Daily MOPS").Select
Range("I5").Select
ActiveSheet.Paste
Range("A5:I30").Select
Selection.Sort Key1:=Range("A5"),
Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:= _
xlTopToBottom
ActiveWorkbook.Save
xltohtml
End Sub
------------------

Please help.

Thanks,
Sheela

Updating data automatically using macro
 

There is so much that can go wrong here it is not really possible to
answer.

When the macro stops with an error :-

1. What is the message ? (write it down exactly).
2. Click on "Debug". Which line is highlighted ?

The usual things that go wrong are :-

*Trying to Select on a sheet that is not Active.

*Range name not existing on the sheet(s).

*Range sizes different .. paste to a single (top left) cell.

*Are the range names on the same sheet ? You might need something like
:-
Application.Goto Reference:=Workbooks("MyBook.xls").Worksheets("MyS heet").Range("mthProdDateRange").
(NB. Best to use this method instead of Select, if you must)

* You might like to try a more "programmers" method (without
"Select"). Here are 2 methods which could replace your first 5 lines
(copy/paste from here into a new module) :
'Version 1
ActiveWorkbook.Worksheets("daily mops").Range("mthProdDateRange").Copy
_
Destination:=Workbooks("MOPS.xls").Worksheets("Dai ly
MOPS").Range("A5")
'Version 2
ActiveWorkbook.Worksheets("daily mops").Range("mthProdDateRange").Copy
Workbooks("MOPS.xls").Worksheets("Daily
MOPS").Range("A5").PasteSpecial _
Paste:=xlPasteValues

There are further refinements of this possible, but let's not make the
step too big at first. Get this working correctly first for one
transfer then add further lines below, testing as you go.

Regards
BrianB
=========================================



"Sheela" wrote in message ...
Hello,

I created a link between two worksheet in the same
workbook using macro. The main worksheet where the data
is entered should update certain columns in another
worksheet.

When the macro is run, it should do the updation
automatically but its not working. Below is the macro
which I created. I really don't know what's wrong with it.

-----------
Sub DailyMOPS()
Application.Goto Reference:="mthProdDateRange"
Selection.Copy
Windows("MOPS.xls").Activate
Sheets("Daily MOPS").Select
Range("A5").Select
ActiveSheet.Paste
Application.Goto Reference:="mthULG97Range"
Selection.Copy
Windows("MOPS.xls").Activate
Sheets("Daily MOPS").Select
Range("B5").Select
ActiveSheet.Paste
Application.Goto Reference:="mthULG92Range"
Selection.Copy
Windows("MOPS.xls").Activate
Sheets("Daily MOPS").Select
Range("C5").Select
ActiveSheet.Paste
Application.Goto Reference:="mthKeroRange"
Selection.Copy
Windows("MOPS.xls").Activate
Sheets("Daily MOPS").Select
Range("D5").Select
ActiveSheet.Paste
Application.Goto Reference:="mthGORange"
Selection.Copy
Windows("MOPS.xls").Activate
Sheets("Daily MOPS").Select
Range("E5").Select
ActiveSheet.Paste
Application.Goto Reference:="mthGO25Range"
Selection.Copy
Windows("MOPS.xls").Activate
Sheets("Daily MOPS").Select
Range("F5").Select
ActiveSheet.Paste
Application.Goto Reference:="mthNaphthaRange"
Selection.Copy
Windows("MOPS.xls").Activate
Sheets("Daily MOPS").Select
Range("G5").Select
ActiveSheet.Paste
Application.Goto Reference:="mth180Range"
Selection.Copy
Windows("MOPS.xls").Activate
Sheets("Daily MOPS").Select
Range("H5").Select
ActiveSheet.Paste
Application.Goto Reference:="mthLSWRCrkRange"
Selection.Copy
Windows("MOPS.xls").Activate
Sheets("Daily MOPS").Select
Range("I5").Select
ActiveSheet.Paste
Range("A5:I30").Select
Selection.Sort Key1:=Range("A5"),
Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:= _
xlTopToBottom
ActiveWorkbook.Save
xltohtml
End Sub
------------------

Please help.

Thanks,
Sheela

Updating data automatically using macro
 

Hello,

I managed to solve the problem. It was actually the
OFFSET formula which was wrong. Since the file was using
the worksheet function and the macro, I didn't know which
was causing the problem. I analysed it and found that the
OFFSET has been reset with another formula.

Sorry for trouble.

Regards,
Sheela


-----Original Message-----
There is so much that can go wrong here it is not really
possible to
answer.

When the macro stops with an error :-

1. What is the message ? (write it down exactly).
2. Click on "Debug". Which line is highlighted ?

The usual things that go wrong are :-

*Trying to Select on a sheet that is not Active.

*Range name not existing on the sheet(s).

*Range sizes different .. paste to a single (top left)
cell.

*Are the range names on the same sheet ? You might need
something like
:-
Application.Goto Reference:=Workbooks
("MyBook.xls").Worksheets("MySheet").Range
("mthProdDateRange").
(NB. Best to use this method instead of Select, if you
must)

* You might like to try a more "programmers" method
(without
"Select"). Here are 2 methods which could replace your
first 5 lines
(copy/paste from here into a new module) :
'Version 1
ActiveWorkbook.Worksheets("daily mops").Range
("mthProdDateRange").Copy
_
Destination:=Workbooks("MOPS.xls").Worksheets("Da ily
MOPS").Range("A5")
'Version 2
ActiveWorkbook.Worksheets("daily mops").Range
("mthProdDateRange").Copy
Workbooks("MOPS.xls").Worksheets("Daily
MOPS").Range("A5").PasteSpecial _
Paste:=xlPasteValues

There are further refinements of this possible, but
let's not make the
step too big at first. Get this working correctly first
for one
transfer then add further lines below, testing as you go.

Regards
BrianB
=========================================



"Sheela" wrote in message
...
Hello,

I created a link between two worksheet in the same
workbook using macro. The main worksheet where the
data
is entered should update certain columns in another
worksheet.

When the macro is run, it should do the updation
automatically but its not working. Below is the macro
which I created. I really don't know what's wrong with
it.

-----------
Sub DailyMOPS()
Application.Goto Reference:="mthProdDateRange"
Selection.Copy
Windows("MOPS.xls").Activate
Sheets("Daily MOPS").Select
Range("A5").Select
ActiveSheet.Paste
Application.Goto Reference:="mthULG97Range"
Selection.Copy
Windows("MOPS.xls").Activate
Sheets("Daily MOPS").Select
Range("B5").Select
ActiveSheet.Paste
Application.Goto Reference:="mthULG92Range"
Selection.Copy
Windows("MOPS.xls").Activate
Sheets("Daily MOPS").Select
Range("C5").Select
ActiveSheet.Paste
Application.Goto Reference:="mthKeroRange"
Selection.Copy
Windows("MOPS.xls").Activate
Sheets("Daily MOPS").Select
Range("D5").Select
ActiveSheet.Paste
Application.Goto Reference:="mthGORange"
Selection.Copy
Windows("MOPS.xls").Activate
Sheets("Daily MOPS").Select
Range("E5").Select
ActiveSheet.Paste
Application.Goto Reference:="mthGO25Range"
Selection.Copy
Windows("MOPS.xls").Activate
Sheets("Daily MOPS").Select
Range("F5").Select
ActiveSheet.Paste
Application.Goto Reference:="mthNaphthaRange"
Selection.Copy
Windows("MOPS.xls").Activate
Sheets("Daily MOPS").Select
Range("G5").Select
ActiveSheet.Paste
Application.Goto Reference:="mth180Range"
Selection.Copy
Windows("MOPS.xls").Activate
Sheets("Daily MOPS").Select
Range("H5").Select
ActiveSheet.Paste
Application.Goto Reference:="mthLSWRCrkRange"
Selection.Copy
Windows("MOPS.xls").Activate
Sheets("Daily MOPS").Select
Range("I5").Select
ActiveSheet.Paste
Range("A5:I30").Select
Selection.Sort Key1:=Range("A5"),
Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:= _
xlTopToBottom
ActiveWorkbook.Save
xltohtml
End Sub
------------------

Please help.

Thanks,
Sheela
.

Updating data automatically using macro
 

Hi Sheela,

Can you send me corrected VBA code.

It's needful in my case i am also facing same problem.

If you don't mind please share to me.


Thanks & Regards