Updating data automatically using macro

  • Thread starter Thread starter Sheela
  • Start date Start date
S

Sheela

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
 
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("Daily
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
=========================================
 
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
 
Back
Top