daily macro

bonovox1

Board Regular
Joined
Aug 11, 2007
Messages
91
hi,

i run this macro daily. This macro runs fine when the file is called bryan.xls. But if i want to change the file name to bryan+todays date and allow the macro to run. Could any body supply me with code to do this. It doesn`t seem to hard but its wasting a lot of my time and i can`t get the macro to run despite a lot of attempts.

Sub Button1_Click()

End Sub
Sub DAILY_RUN()
'
' DAILY_RUN Macro
'
'

'
Application.CutCopyMode = False
Workbooks.Open Filename:="K:\VerasisReports\SVP1-EREISWFR.csv"
Range("B2:C39").Select
Selection.Copy
Windows("bryan.xls").Activate
Sheets("EUR").Select
Range("X2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("SVP1-EREISWFR.csv").Activate
ActiveWindow.SmallScroll Down:=-18
Range("E2:F39").Select
Application.CutCopyMode = False
Selection.Copy
Windows("bryan.xls").Activate
Sheets("GBP").Select
Range("AB2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("SVP1-EREISWFR.csv").Activate
ActiveWindow.SmallScroll Down:=-15
Range("H2:I40").Select
Application.CutCopyMode = False
Selection.Copy
Windows("bryan.xls").Activate
Sheets("USD").Select
Range("Z2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("R2:R26").Select
Application.CutCopyMode = False
Selection.NumberFormat = "0"
Sheets("EUR").Select
Range("R2:R25").Select
Selection.NumberFormat = "0"
Sheets("GBP").Select
Range("T2:T28").Select
Selection.NumberFormat = "0"


End Sub
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Just a question, when you are changing the name of the file are you also changing the name used with in the macro:

Windows("bryan.xls").Activate
 
Upvote 0
hi, thats my problem. I want to allow bryan.xls to change with the date, i.e bryan_08_22_07.xls, ect. How can i change my macro to take this in to account.
 
Upvote 0
This code will change the active workbook name as you are asking:
Code:
Sub MyReName()
    Path = ActiveWorkbook.Path & "\"
    FNm = Application.WorksheetFunction.Text(Date, "mm")
    FNd = Application.WorksheetFunction.Text(Date, "dd")
    FNy = Application.WorksheetFunction.Text(Date, "yy")
    Filename = "Bryan_" & FNm & "_" & FNd & "_" & FNy & ".xls"
'save the new file as long as there is not a file with same name in folder
    If Dir(Path & Filename) = "" Then ActiveWorkbook.SaveAs Filename:=Path & Filename
End Sub
 
Upvote 0
hi, i tried the latter code and it does run. still getting an error about file name. So just to be clear i have bryan_08_24_07.xls saved for example and i try to run it.
 
Upvote 0
Please post the exact code you are using now. (use the code tags available on the "postreply" page)
Let us know what line you get the error on.
What is the name of the workbook your code is in?
What is the name of the workbook you want to create?
What is the path of each workbook?
 
Upvote 0
This is the code i am using. It is in the file bryan.todays date.xls. I get an error in the following line; yellow arrow.
Windows("bryan_" & FNm & "_" & FNd & "_" & FNy & ".xls").Activate

Code:
Sub Button1_Click()

End Sub
Sub MyReName()
    Path = ActiveWorkbook.Path & "\"
    FNm = Application.WorksheetFunction.Text(Date, "mm")
    FNd = Application.WorksheetFunction.Text(Date, "dd")
    FNy = Application.WorksheetFunction.Text(Date, "yy")
    Filename = "bryan_" & FNm & "_" & FNd & "_" & FNy & ".xls"
'save the new file as long as there is not a file with same name in folder
    If Dir(Path & Filename) = "" Then ActiveWorkbook.SaveAs Filename:=Path & Filename
End Sub

Sub DAILY_RUN()
'

'

'
    Application.CutCopyMode = False
    Workbooks.Open Filename:="K:\Reports\SVP1-EREISWFR.csv"
    Range("B2:C39").Select
    Selection.Copy
    Windows("bryan_" & FNm & "_" & FNd & "_" & FNy & ".xls").Activate
    Sheets("EUR").Select
    Range("X2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("SVP1-EREISWFR.csv").Activate
    ActiveWindow.SmallScroll Down:=-18
    Range("E2:F39").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("bryan_" & FNm & "_" & FNd & "_" & FNy & ".xls").Activate
    Sheets("GBP").Select
    Range("AB2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("SVP1-EREISWFR.csv").Activate
    ActiveWindow.SmallScroll Down:=-15
    Range("H2:I40").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("bryan_" & FNm & "_" & FNd & "_" & FNy & ".xls").Activate
    Sheets("USD").Select
    Range("Z2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("R2:R26").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "0"
    Sheets("EUR").Select
    Range("R2:R25").Select
    Selection.NumberFormat = "0"
    Sheets("GBP").Select
    Range("T2:T28").Select
    Selection.NumberFormat = "0"
    

End Sub
[/quote]
 
Upvote 0
You can not use the line "Windows("bryan_" & FNm & "_" & FNd & "_" & FNy & ".xls").Activate" in a macro that does not have the three variables assigned. That was used in the MyReName macro I posted earlier.
Add this line to the beginning of your "DAILY_RUN" macro.
Code:
    MyWBName = ActiveWorkbook.Name
Then replace your erroring line with this:
Code:
    Windows(MyWBName).Activate
 
Upvote 0
Hi i tried the code advice you gave me; but i still get an error. :( See im not too good at excel; only 4th week working with it, so im prone to stupid enough errors. If ye could look at the code i am now using and let me know what i am doung wrong. I really appreciate your help.

Here is the code
Code:
Sub Button1_Click()

 

End Sub

Sub MyReName()

    Path = ActiveWorkbook.Path & "\"

    FNm = Application.WorksheetFunction.Text(Date, "mm")

    FNd = Application.WorksheetFunction.Text(Date, "dd")

    FNy = Application.WorksheetFunction.Text(Date, "yy")

    Filename = "bryan_" & FNm & "_" & FNd & "_" & FNy & ".xls"

'save the new file as long as there is not a file with same name in folder

    If Dir(Path & Filename) = "" Then ActiveWorkbook.SaveAs Filename:=Path & Filename

End Sub

 

Sub DAILY_RUN()

'

' DAILY_RUN Macro


'

 

'   bryan = ActiveWorkbook.Name

    Application.CutCopyMode = False

    Workbooks.Open Filename:="K:\VerasisReports\SVP1-EREISWFR.csv"

    Range("B2:C39").Select

    Selection.Copy

    Windows(bryan).Activate

    Windows("bryan_" & FNm & "_" & FNd & "_" & FNy & ".xls").Activate

    Sheets("EUR").Select

    Range("X2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Windows("SVP1-EREISWFR.csv").Activate

    ActiveWindow.SmallScroll Down:=-18

    Range("E2:F39").Select

    Application.CutCopyMode = False

    Selection.Copy

    Windows("bryan_" & FNm & "_" & FNd & "_" & FNy & ".xls").Activate

    Sheets("GBP").Select

    Range("AB2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Windows("SVP1-EREISWFR.csv").Activate

    ActiveWindow.SmallScroll Down:=-15

    Range("H2:I40").Select

    Application.CutCopyMode = False

    Selection.Copy

    Windows("bryan_" & FNm & "_" & FNd & "_" & FNy & ".xls").Activate

    Sheets("USD").Select

    Range("Z2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    ActiveWindow.ScrollColumn = 16

    ActiveWindow.ScrollColumn = 15

    ActiveWindow.ScrollColumn = 14

    ActiveWindow.ScrollColumn = 13

    ActiveWindow.ScrollColumn = 12

    ActiveWindow.ScrollColumn = 11

    ActiveWindow.ScrollColumn = 10

    ActiveWindow.ScrollColumn = 9

    ActiveWindow.ScrollColumn = 8

    ActiveWindow.ScrollColumn = 7

    ActiveWindow.ScrollColumn = 6

    ActiveWindow.ScrollColumn = 5

    ActiveWindow.ScrollColumn = 4

    ActiveWindow.ScrollColumn = 3

    ActiveWindow.ScrollColumn = 2

    ActiveWindow.ScrollColumn = 1

    Range("R2:R26").Select

    Application.CutCopyMode = False

    Selection.NumberFormat = "0"

    Sheets("EUR").Select

    Range("R2:R25").Select

    Selection.NumberFormat = "0"

    Sheets("GBP").Select

    Range("T2:T28").Select

    Selection.NumberFormat = "0"

    

 

End Sub

I get an error in this line
Code:
 Windows(bryan).Activate
I know im doing something stupid but just can`t see what. Hope ye can help.
 
Upvote 0
bonovox1,

You have 3 macros in your last post.

1. Button1_Click (which has no code in it)
2. MyReName (which I gave you earlier)
3. DAILY_RUN (which I rewrote below)

You don't mention how or in what order they are run.
You keep inserting code from macro 2 into macro 3, and get errors, leave them separate.

In the revised DAILY_RUN code. I have included variables for the two workbooks that you are working with. I also removed many of the Select statements and scroll code that the macro recorder puts in.
Code:
Sub DAILY_RUN()
Dim TargetWB
Dim SourceWB

    'assign variable for Target workbook
    TargetWB = ActiveWorkbook.Name

    Workbooks.Open Filename:="K:\Reports\SVP1-EREISWFR.csv"

    'assign variable for Source workbook
    SourceWB = ActiveWorkbook.Name
        Range("B2:C39").Copy
    
    Windows(TargetWB).Activate
        Sheets("EUR").Select
        Range("X2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Windows(SourceWB).Activate
        Range("E2:F39").Copy
        
    Windows(TargetWB).Activate
        Sheets("GBP").Select
        Range("AB2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Windows(SourceWB).Activate
        Range("H2:I40").Copy
    
    Windows(TargetWB).Activate
        Sheets("USD").Select
        Range("Z2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Range("R2:R26").NumberFormat = "0"
    Sheets("EUR").Range("R2:R25").NumberFormat = "0"
    Sheets("GBP").Range("T2:T28").NumberFormat = "0"
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top