Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > Update Data in Macro
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 54
Default Update Data in Macro
Hi
I have a Macro which is run on a Monthly Basis to clear and move fields etc
for new months data. This works well, except I would like this is either
Stop and let me input the New Month OR can I insert code to insert the
following month Automatically from the previous cell.
The new months data will always be on the same Row and Cells.
Thank you
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 54
Default Update Data in Macro
Hi

This Macro is quite large. Being an amatuer at this it probably could be
improved. Do you still want me to post it.

Thanks

"FSt1" wrote:

hi
post your code for evaluation.
regards
FSt1

"enna49" wrote:

Hi
I have a Macro which is run on a Monthly Basis to clear and move fields etc
for new months data. This works well, except I would like this is either
Stop and let me input the New Month OR can I insert code to insert the
following month Automatically from the previous cell.
The new months data will always be on the same Row and Cells.
Thank you
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 3,942
Default Update Data in Macro
hi
yes. both of your requests are possible but we wont know which way would be
best or where to put the new code unless we see the code. Always.....Always
post your code for evaulation. we are not mind readers.

Regards
FSt1

"enna49" wrote:

Hi

This Macro is quite large. Being an amatuer at this it probably could be
improved. Do you still want me to post it.

Thanks

"FSt1" wrote:

hi
post your code for evaluation.
regards
FSt1

"enna49" wrote:

Hi
I have a Macro which is run on a Monthly Basis to clear and move fields etc
for new months data. This works well, except I would like this is either
Stop and let me input the New Month OR can I insert code to insert the
following month Automatically from the previous cell.
The new months data will always be on the same Row and Cells.
Thank you
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 54
Default Update Data in Macro
Hi
Here is my code, hopefully you can read it. Sorry for the long drawn out
process

Sub Status_Report_New_Month()
' Copy_and_Clear_for_New_Month Macro

' Opens File - This will be the prior month file to be setup for Current Month

current_workbook = ActiveWorkbook.Name
fn = Application.GetOpenFilename("XLS Files,*.XLS", 1, "Select File to
Open", , False)
If TypeName(fn) = "Boolean" Then Exit Sub
Workbooks.Open Filename:=fn
opened_workbook = ActiveWorkbook.Name

'Save as Following Month
Dim rng As Range
ActiveWorkbook.SAVEAS Filename:=Application.GetSaveAsFilename


ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
'Regional Freight
Sheets("Regional Freight-QNRFA").Select
Range("RegFreight1").Select
Selection.Copy
Range("A5").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("B17:H17").Select
Range("A21:I32").Select
Selection.Copy
Range("A20").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=12
Range("A36:I47").Select
Selection.Copy
Range("A35").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=12
Range("A51:I62").Select
Selection.Copy
Range("A50").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False

'Intermodal - Retail
Sheets("Int Retail-QNIMR").Select
ActiveWindow.SmallScroll Down:=-15
Range("A6:H17").Select
Selection.Copy
Range("A5").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("A21:I32").Select
Selection.Copy
Range("A20").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=12
Range("A36:I47").Select
Selection.Copy
Range("A35").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=12
Range("A51:I62").Select
Selection.Copy
Range("A50").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False

'Intermodal Wholesale
Sheets("Int Wholesale-QNIMW").Select
Range("A6:H17").Select
Selection.Copy
Range("A5").Select
ActiveSheet.PasteSpecial Format:=1, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("A21:I32").Select
Selection.Copy
Range("A20").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=15
Range("A36:I47").Select
Selection.Copy
Range("A35").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=15
Range("A51:I62").Select
Selection.Copy
Range("A50").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False

'Passenger - Sheet
Sheets("Passenger-PS").Select
Range("A6:H17").Select
Selection.Copy
Range("A5").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=12
Range("A21:I32").Select
Selection.Copy
Range("A20").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("A36:I47").Select
Selection.Copy
Range("A35").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=15
Range("A51:I62").Select
Selection.Copy
Range("A50").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False

'Network - Sheet
Sheets("Network-NW").Select
Range("A6:H17").Select
Selection.Copy
Range("A5").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("A21:I32").Select
Selection.Copy
Range("A20").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=9
Range("A36:I47").Select
Selection.Copy
Range("A35").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=15
Range("A51:I62").Select
Selection.Copy
Range("A50").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False

'Infrastructure Sheet
Sheets("Infrastructure-IS").Select
Range("A6:H17").Select
Selection.Copy
Range("A5").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("A21:I32").Select
Selection.Copy
Range("A20").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=15
Range("A36:I47").Select
Selection.Copy
Range("A35").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=15
Range("A51:I62").Select
Selection.Copy
Range("A50").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
' Workshop Sheet
Sheets("Workshop-WS").Select
Range("A6:H17").Select
Selection.Copy
Range("A5").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("A21:I32").Select
Selection.Copy
Range("A20").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=9

'Go Back to Index

Sheets("Index").Select
ActiveWindow.SmallScroll Down:=-3
' End Sub
'Sub Status_Report_Change_Month()
'
' Status_Report_Change_Month Macro
' Macro recorded 3/03/2010 by Anne Reid
'
Sheets("Regional Freight-QNRFA").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets(Array("Regional Freight-QNRFA", "Int Retail-QNIMR", "Int
Wholesale-QNIMW", _
"Passenger-PS", "Network-NW", "Infrastructure-IS", "Workshop-WS",
"COMB IS & WS")). _
Select
Sheets("Regional Freight-QNRFA").Activate
Range("C1").Select
ActiveCell.FormulaR1C1 = "2/1/2010"
€˜ This is where the date is required to be input
Range("A1:B1").Select
Sheets("Data").Select
Range("A1").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("INDEX").Select
Range("A1").Select
ActiveWorkbook.Save

Sheets("FULL CURRENT ATB").Select
' This code should make certain the AutoFilter if OFF
ActiveSheet.AutoFilterMode = False

Range(Selection, Selection.End(xlDown)).Select
Range("A2:V3500").Select
Selection.ClearContents
Range("A1").Select
ActiveWorkbook.Save
End Sub


"FSt1" wrote:

hi
yes. both of your requests are possible but we wont know which way would be
best or where to put the new code unless we see the code. Always.....Always
post your code for evaulation. we are not mind readers.

Regards
FSt1

"enna49" wrote:

Hi

This Macro is quite large. Being an amatuer at this it probably could be
improved. Do you still want me to post it.

Thanks

"FSt1" wrote:

hi
post your code for evaluation.
regards
FSt1

"enna49" wrote:

Hi
I have a Macro which is run on a Monthly Basis to clear and move fields etc
for new months data. This works well, except I would like this is either
Stop and let me input the New Month OR can I insert code to insert the
following month Automatically from the previous cell.
The new months data will always be on the same Row and Cells.
Thank you
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 3,942
Default Update Data in Macro
hi
yes, your code could use some clean up.
but to the point.
instead of this.....

Range("C1").Select
ActiveCell.FormulaR1C1 = "2/1/2010"

add this and you will be prompted for the date when the code needs it.

Dim d As Date
d = InputBox("Enter the desired date.")
Range("C1").Value = d

this will take care of your question/need in your first post.

suggested code clean up.....
you have this.......
Range("RegFreight1").Select
Selection.Copy
Range("A5").Select
ActiveSheet.PasteSpecial...........

seldom do you need to select. the above could be reduced to this....
Range("RegFreight1").Copy
Range("A5").PasteSpecial...........

you have a LOT of code like the above. you could probably reduce your macro
by a forth to a third. 220 lines down to maybe 150.

also you have a number of small scrolls.
those are not needed unless you just like to see the screen jump all around.

hope this helps
regards
FSt1

"enna49" wrote:

Hi
Here is my code, hopefully you can read it. Sorry for the long drawn out
process

Sub Status_Report_New_Month()
' Copy_and_Clear_for_New_Month Macro

' Opens File - This will be the prior month file to be setup for Current Month

current_workbook = ActiveWorkbook.Name
fn = Application.GetOpenFilename("XLS Files,*.XLS", 1, "Select File to
Open", , False)
If TypeName(fn) = "Boolean" Then Exit Sub
Workbooks.Open Filename:=fn
opened_workbook = ActiveWorkbook.Name

'Save as Following Month
Dim rng As Range
ActiveWorkbook.SAVEAS Filename:=Application.GetSaveAsFilename


ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
'Regional Freight
Sheets("Regional Freight-QNRFA").Select
Range("RegFreight1").Select
Selection.Copy
Range("A5").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("B17:H17").Select
Range("A21:I32").Select
Selection.Copy
Range("A20").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=12
Range("A36:I47").Select
Selection.Copy
Range("A35").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=12
Range("A51:I62").Select
Selection.Copy
Range("A50").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False

'Intermodal - Retail
Sheets("Int Retail-QNIMR").Select
ActiveWindow.SmallScroll Down:=-15
Range("A6:H17").Select
Selection.Copy
Range("A5").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("A21:I32").Select
Selection.Copy
Range("A20").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=12
Range("A36:I47").Select
Selection.Copy
Range("A35").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=12
Range("A51:I62").Select
Selection.Copy
Range("A50").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False

'Intermodal Wholesale
Sheets("Int Wholesale-QNIMW").Select
Range("A6:H17").Select
Selection.Copy
Range("A5").Select
ActiveSheet.PasteSpecial Format:=1, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("A21:I32").Select
Selection.Copy
Range("A20").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=15
Range("A36:I47").Select
Selection.Copy
Range("A35").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=15
Range("A51:I62").Select
Selection.Copy
Range("A50").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False

'Passenger - Sheet
Sheets("Passenger-PS").Select
Range("A6:H17").Select
Selection.Copy
Range("A5").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=12
Range("A21:I32").Select
Selection.Copy
Range("A20").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("A36:I47").Select
Selection.Copy
Range("A35").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=15
Range("A51:I62").Select
Selection.Copy
Range("A50").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False

'Network - Sheet
Sheets("Network-NW").Select
Range("A6:H17").Select
Selection.Copy
Range("A5").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("A21:I32").Select
Selection.Copy
Range("A20").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=9
Range("A36:I47").Select
Selection.Copy
Range("A35").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=15
Range("A51:I62").Select
Selection.Copy
Range("A50").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False

'Infrastructure Sheet
Sheets("Infrastructure-IS").Select
Range("A6:H17").Select
Selection.Copy
Range("A5").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("A21:I32").Select
Selection.Copy
Range("A20").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=15
Range("A36:I47").Select
Selection.Copy
Range("A35").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=15
Range("A51:I62").Select
Selection.Copy
Range("A50").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
' Workshop Sheet
Sheets("Workshop-WS").Select
Range("A6:H17").Select
Selection.Copy
Range("A5").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("A21:I32").Select
Selection.Copy
Range("A20").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=9

'Go Back to Index

Sheets("Index").Select
ActiveWindow.SmallScroll Down:=-3
' End Sub
'Sub Status_Report_Change_Month()
'
' Status_Report_Change_Month Macro
' Macro recorded 3/03/2010 by Anne Reid
'
Sheets("Regional Freight-QNRFA").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets(Array("Regional Freight-QNRFA", "Int Retail-QNIMR", "Int
Wholesale-QNIMW", _
"Passenger-PS", "Network-NW", "Infrastructure-IS", "Workshop-WS",
"COMB IS & WS")). _
Select
Sheets("Regional Freight-QNRFA").Activate
Range("C1").Select
ActiveCell.FormulaR1C1 = "2/1/2010"
€˜ This is where the date is required to be input
Range("A1:B1").Select
Sheets("Data").Select
Range("A1").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("INDEX").Select
Range("A1").Select
ActiveWorkbook.Save

Sheets("FULL CURRENT ATB").Select
' This code should make certain the AutoFilter if OFF
ActiveSheet.AutoFilterMode = False

Range(Selection, Selection.End(xlDown)).Select
Range("A2:V3500").Select
Selection.ClearContents
Range("A1").Select
ActiveWorkbook.Save
End Sub


"FSt1" wrote:

hi
yes. both of your requests are possible but we wont know which way would be
best or where to put the new code unless we see the code. Always.....Always
post your code for evaulation. we are not mind readers.

Regards
FSt1

"enna49" wrote:

Hi

This Macro is quite large. Being an amatuer at this it probably could be
improved. Do you still want me to post it.

Thanks

"FSt1" wrote:

hi
post your code for evaluation.
regards
FSt1

"enna49" wrote:

Hi
I have a Macro which is run on a Monthly Basis to clear and move fields etc
for new months data. This works well, except I would like this is either
Stop and let me input the New Month OR can I insert code to insert the
following month Automatically from the previous cell.
The new months data will always be on the same Row and Cells.
Thank you
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 54
Default Update Data in Macro
Thank you very much for your patience in going through the code. As I said
I am an amateur and most of this VBA stuff, I have picked up from the Forum
or Recording Macros. Need to find a good book.

"FSt1" wrote:

hi
yes, your code could use some clean up.
but to the point.
instead of this.....

Range("C1").Select
ActiveCell.FormulaR1C1 = "2/1/2010"

add this and you will be prompted for the date when the code needs it.

Dim d As Date
d = InputBox("Enter the desired date.")
Range("C1").Value = d

this will take care of your question/need in your first post.

suggested code clean up.....
you have this.......
Range("RegFreight1").Select
Selection.Copy
Range("A5").Select
ActiveSheet.PasteSpecial...........

seldom do you need to select. the above could be reduced to this....
Range("RegFreight1").Copy
Range("A5").PasteSpecial...........

you have a LOT of code like the above. you could probably reduce your macro
by a forth to a third. 220 lines down to maybe 150.

also you have a number of small scrolls.
those are not needed unless you just like to see the screen jump all around.

hope this helps
regards
FSt1

"enna49" wrote:

Hi
Here is my code, hopefully you can read it. Sorry for the long drawn out
process

Sub Status_Report_New_Month()
' Copy_and_Clear_for_New_Month Macro

' Opens File - This will be the prior month file to be setup for Current Month

current_workbook = ActiveWorkbook.Name
fn = Application.GetOpenFilename("XLS Files,*.XLS", 1, "Select File to
Open", , False)
If TypeName(fn) = "Boolean" Then Exit Sub
Workbooks.Open Filename:=fn
opened_workbook = ActiveWorkbook.Name

'Save as Following Month
Dim rng As Range
ActiveWorkbook.SAVEAS Filename:=Application.GetSaveAsFilename


ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
'Regional Freight
Sheets("Regional Freight-QNRFA").Select
Range("RegFreight1").Select
Selection.Copy
Range("A5").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("B17:H17").Select
Range("A21:I32").Select
Selection.Copy
Range("A20").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=12
Range("A36:I47").Select
Selection.Copy
Range("A35").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=12
Range("A51:I62").Select
Selection.Copy
Range("A50").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False

'Intermodal - Retail
Sheets("Int Retail-QNIMR").Select
ActiveWindow.SmallScroll Down:=-15
Range("A6:H17").Select
Selection.Copy
Range("A5").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("A21:I32").Select
Selection.Copy
Range("A20").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=12
Range("A36:I47").Select
Selection.Copy
Range("A35").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=12
Range("A51:I62").Select
Selection.Copy
Range("A50").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False

'Intermodal Wholesale
Sheets("Int Wholesale-QNIMW").Select
Range("A6:H17").Select
Selection.Copy
Range("A5").Select
ActiveSheet.PasteSpecial Format:=1, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("A21:I32").Select
Selection.Copy
Range("A20").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=15
Range("A36:I47").Select
Selection.Copy
Range("A35").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=15
Range("A51:I62").Select
Selection.Copy
Range("A50").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False

'Passenger - Sheet
Sheets("Passenger-PS").Select
Range("A6:H17").Select
Selection.Copy
Range("A5").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=12
Range("A21:I32").Select
Selection.Copy
Range("A20").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("A36:I47").Select
Selection.Copy
Range("A35").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=15
Range("A51:I62").Select
Selection.Copy
Range("A50").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False

'Network - Sheet
Sheets("Network-NW").Select
Range("A6:H17").Select
Selection.Copy
Range("A5").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("A21:I32").Select
Selection.Copy
Range("A20").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=9
Range("A36:I47").Select
Selection.Copy
Range("A35").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=15
Range("A51:I62").Select
Selection.Copy
Range("A50").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False

'Infrastructure Sheet
Sheets("Infrastructure-IS").Select
Range("A6:H17").Select
Selection.Copy
Range("A5").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("A21:I32").Select
Selection.Copy
Range("A20").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=15
Range("A36:I47").Select
Selection.Copy
Range("A35").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=15
Range("A51:I62").Select
Selection.Copy
Range("A50").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
' Workshop Sheet
Sheets("Workshop-WS").Select
Range("A6:H17").Select
Selection.Copy
Range("A5").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("A21:I32").Select
Selection.Copy
Range("A20").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=9

'Go Back to Index

Sheets("Index").Select
ActiveWindow.SmallScroll Down:=-3
' End Sub
'Sub Status_Report_Change_Month()
'
' Status_Report_Change_Month Macro
' Macro recorded 3/03/2010 by Anne Reid
'
Sheets("Regional Freight-QNRFA").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets(Array("Regional Freight-QNRFA", "Int Retail-QNIMR", "Int
Wholesale-QNIMW", _
"Passenger-PS", "Network-NW", "Infrastructure-IS", "Workshop-WS",
"COMB IS & WS")). _
Select
Sheets("Regional Freight-QNRFA").Activate
Range("C1").Select
ActiveCell.FormulaR1C1 = "2/1/2010"
€˜ This is where the date is required to be input
Range("A1:B1").Select
Sheets("Data").Select
Range("A1").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("INDEX").Select
Range("A1").Select
ActiveWorkbook.Save

Sheets("FULL CURRENT ATB").Select
' This code should make certain the AutoFilter if OFF
ActiveSheet.AutoFilterMode = False

Range(Selection, Selection.End(xlDown)).Select
Range("A2:V3500").Select
Selection.ClearContents
Range("A1").Select
ActiveWorkbook.Save
End Sub


"FSt1" wrote:

hi
yes. both of your requests are possible but we wont know which way would be
best or where to put the new code unless we see the code. Always.....Always
post your code for evaulation. we are not mind readers.

Regards
FSt1

"enna49" wrote:

Hi

This Macro is quite large. Being an amatuer at this it probably could be
improved. Do you still want me to post it.

Thanks

"FSt1" wrote:

hi
post your code for evaluation.
regards
FSt1

"enna49" wrote:

Hi
I have a Macro which is run on a Monthly Basis to clear and move fields etc
for new months data. This works well, except I would like this is either
Stop and let me input the New Month OR can I insert code to insert the
following month Automatically from the previous cell.
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 54
Default Update Data in Macro
Hi
The input of date works thank you. Problem is I am trying to change on 8
Worksheets at once. My code is below, but the date will only change on the
first sheet. I have also input a similar row with .Activate and this will
not work either.

Please can you help with this

Thank you

Sheets(Array("Regional Freight-QNRFA", "Int Retail-QNIMR", "Int
Wholesale-QNIMW", _
"Passenger-PS", "Network-NW", "Infrastructure-IS", "Workshop-WS",
"COMB IS & WS")).Select



Dim d As Date
d = InputBox("Enter the desired date.")
Range("C1").Value = d

"FSt1" wrote:

hi
yes, your code could use some clean up.
but to the point.
instead of this.....

Range("C1").Select
ActiveCell.FormulaR1C1 = "2/1/2010"

add this and you will be prompted for the date when the code needs it.

Dim d As Date
d = InputBox("Enter the desired date.")
Range("C1").Value = d

this will take care of your question/need in your first post.

suggested code clean up.....
you have this.......
Range("RegFreight1").Select
Selection.Copy
Range("A5").Select
ActiveSheet.PasteSpecial...........

seldom do you need to select. the above could be reduced to this....
Range("RegFreight1").Copy
Range("A5").PasteSpecial...........

you have a LOT of code like the above. you could probably reduce your macro
by a forth to a third. 220 lines down to maybe 150.

also you have a number of small scrolls.
those are not needed unless you just like to see the screen jump all around.

hope this helps
regards
FSt1

"enna49" wrote:

Hi
Here is my code, hopefully you can read it. Sorry for the long drawn out
process

Sub Status_Report_New_Month()
' Copy_and_Clear_for_New_Month Macro

' Opens File - This will be the prior month file to be setup for Current Month

current_workbook = ActiveWorkbook.Name
fn = Application.GetOpenFilename("XLS Files,*.XLS", 1, "Select File to
Open", , False)
If TypeName(fn) = "Boolean" Then Exit Sub
Workbooks.Open Filename:=fn
opened_workbook = ActiveWorkbook.Name

'Save as Following Month
Dim rng As Range
ActiveWorkbook.SAVEAS Filename:=Application.GetSaveAsFilename


ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
'Regional Freight
Sheets("Regional Freight-QNRFA").Select
Range("RegFreight1").Select
Selection.Copy
Range("A5").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("B17:H17").Select
Range("A21:I32").Select
Selection.Copy
Range("A20").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=12
Range("A36:I47").Select
Selection.Copy
Range("A35").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=12
Range("A51:I62").Select
Selection.Copy
Range("A50").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False

'Intermodal - Retail
Sheets("Int Retail-QNIMR").Select
ActiveWindow.SmallScroll Down:=-15
Range("A6:H17").Select
Selection.Copy
Range("A5").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("A21:I32").Select
Selection.Copy
Range("A20").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=12
Range("A36:I47").Select
Selection.Copy
Range("A35").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=12
Range("A51:I62").Select
Selection.Copy
Range("A50").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False

'Intermodal Wholesale
Sheets("Int Wholesale-QNIMW").Select
Range("A6:H17").Select
Selection.Copy
Range("A5").Select
ActiveSheet.PasteSpecial Format:=1, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("A21:I32").Select
Selection.Copy
Range("A20").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=15
Range("A36:I47").Select
Selection.Copy
Range("A35").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=15
Range("A51:I62").Select
Selection.Copy
Range("A50").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False

'Passenger - Sheet
Sheets("Passenger-PS").Select
Range("A6:H17").Select
Selection.Copy
Range("A5").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=12
Range("A21:I32").Select
Selection.Copy
Range("A20").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("A36:I47").Select
Selection.Copy
Range("A35").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=15
Range("A51:I62").Select
Selection.Copy
Range("A50").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False

'Network - Sheet
Sheets("Network-NW").Select
Range("A6:H17").Select
Selection.Copy
Range("A5").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("A21:I32").Select
Selection.Copy
Range("A20").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=9
Range("A36:I47").Select
Selection.Copy
Range("A35").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=15
Range("A51:I62").Select
Selection.Copy
Range("A50").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False

'Infrastructure Sheet
Sheets("Infrastructure-IS").Select
Range("A6:H17").Select
Selection.Copy
Range("A5").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("A21:I32").Select
Selection.Copy
Range("A20").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=15
Range("A36:I47").Select
Selection.Copy
Range("A35").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=15
Range("A51:I62").Select
Selection.Copy
Range("A50").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
' Workshop Sheet
Sheets("Workshop-WS").Select
Range("A6:H17").Select
Selection.Copy
Range("A5").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
Range("A21:I32").Select
Selection.Copy
Range("A20").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
ActiveWindow.SmallScroll Down:=9

'Go Back to Index

Sheets("Index").Select
ActiveWindow.SmallScroll Down:=-3
' End Sub
'Sub Status_Report_Change_Month()
'
' Status_Report_Change_Month Macro
' Macro recorded 3/03/2010 by Anne Reid
'
Sheets("Regional Freight-QNRFA").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets(Array("Regional Freight-QNRFA", "Int Retail-QNIMR", "Int
Wholesale-QNIMW", _
"Passenger-PS", "Network-NW", "Infrastructure-IS", "Workshop-WS",
"COMB IS & WS")). _
Select
Sheets("Regional Freight-QNRFA").Activate
Range("C1").Select
ActiveCell.FormulaR1C1 = "2/1/2010"
€˜ This is where the date is required to be input
Range("A1:B1").Select
Sheets("Data").Select
Range("A1").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("INDEX").Select
Range("A1").Select
ActiveWorkbook.Save

Sheets("FULL CURRENT ATB").Select
' This code should make certain the AutoFilter if OFF
ActiveSheet.AutoFilterMode = False

Range(Selection, Selection.End(xlDown)).Select
Range("A2:V3500").Select
Selection.ClearContents
Range("A1").Select
ActiveWorkbook.Save
End Sub


"FSt1" wrote:

hi
yes. both of your requests are possible but we wont know which way would be
best or where to put the new code unless we see the code. Always.....Always
post your code for evaulation. we are not mind readers.

Regards
FSt1

"enna49" wrote:

Hi

This Macro is quite large. Being an amatuer at this it probably could be
improved. Do you still want me to post it.

Thanks

"FSt1" wrote:

hi
post your code for evaluation.
regards
FSt1

"enna49" wrote:

Hi
I have a Macro which is run on a Monthly Basis to clear and move fields etc
for new months data. This works well, except I would like this is either
Stop and let me input the New Month OR can I insert code to insert the
following month Automatically from the previous cell.
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Data Validation lists update orginal cell with list update [email protected] Excel Worksheet Functions 3 July 11th 08 07:56 AM
HOW CAN I UPDATE DATA FROM INTERNET WITHOUT RUNNING MACRO lookup Excel Discussion (Misc queries) 2 April 23rd 08 10:15 PM
Macro Auto-update of data labels [email protected] Excel Worksheet Functions 0 August 7th 07 04:17 PM
How may I macro update a normal distribution chart from new data? Mike Williams Charts and Charting in Excel 4 September 11th 06 01:20 PM
Macro to Update Charts Source Data [email protected] Charts and Charting in Excel 1 October 18th 05 03:22 AM


All times are GMT +1. The time now is 11:35 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement