Posts by Danny99

I have a row of 8 dates. I would like to list a given number of working days (i.e monday to friday) above these dates. So for example the last 5 working days BEFORE a particular cell.
Is this possible? So for example cell A10 and B10 and C10 ect ect all have random dates in them. Now i need a function to add the weekdays EXCLUDING saturday and sunday above each of the dates in each of the columns.
I am very keen to see the answer to this one. I did check ozgrids site but cannot seem to find it.
Much appreciated.

I haev an automated macro that sends out an email every day at 9am. what i would like to do, is send it out only if a cell is TRUE. so every morning if the cell A1 ia FALSE the email doesnt get sent. if the cell in A1 is TRUE the email gets sent ONCE only. Every day the same thing except for weekends.

how is this done?

Re: Daily Macro to Exclude Weekends

Thank you. seems to work fine.

I have a timed macro which sets off every day at 9am. but its going off in the weekends also. how can i exclude the weekends?"All help much appreciated. Thank you.

Re: Pull Latest Date From Pivot Table

Cannot upload an example because there is a restriction on what we can upload.
Basicly i need to pull the latest price from a pivot table, based on the latest date.

any ideas?

Re: Pulling Latest Date From Pivot Table

Sounds funky, but i never touched SQL before. how would i do this?

Re: Pulling Latest Date From Pivot Table

Thanks for your responce. most appreciated. Currently I am using only the Date. no time stamp. However, I like that idea better, because if the values are entered as an update or as a correction to earlier in the day values then would be better to have a time stamp also.
What are your thoughts?

I have a list of Data in an access data base. I am linking a pivot table to this list in excel.
For each historical date there are a number of prices released on that day. I would like to create a link from the pivot table to another sheet. But I would only like to PULL THE MOST REENT PRICES. (recent by date).

Any ideas?

Re: Run Macro In Closed File At Same Time Each Day

this is great! works perfectly thank you. But problem is, how to allow for weekends? Meaning I would like my automated macro to run only monday to friday. How can i (apart from turning my machine off) tell the code to stop during weekends?

OK i know there is information on this ontime method, i haev read through it, but since i am working with reuters running live in the background they just dont work.
Anyway, on a normal excel sheet, I have made the automated macros run on a timer, which is great. BUT If someone switches off my pc, its not going to be so great because i dont think the macro would run without excel being open.
QUESTION:
How can i make my macro run every morning at 8am? Assuming the worksheet is closed, even excel is closed. i am sure the answer is starring me in the face, i am just getting boerd of looking at it!
All help much appreciated. Here is the code I am using now:

in the Thisworkbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Application.OnTime dTime, "thismodule", , False
End Sub
Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("20:00:00"), "thismodule"
End Sub

and in the Module:

Sub thismodule()
dTime = Now + TimeValue("08:00:00")
Application.OnTime dTime, "thismodule"

any ideas?

Re: Merge Same Column From Multiple Files

I ran the code but got extremely weird results. After the code is ran, a worksheet from 2003 opens up. and thats it. I wait 3 to 4 minutes, then suddenly a sheet from 2003 opens, and thats that. I must be doing something wrong...ummmmmm

Re: Merge Same Column From Multiple Files

Oh no. yes 256 columns just isnt enough. I have one worksheet for every day. so 365 per year for 2 years and growing. (well minus the weekends). how would you continue the list onto another worksheet in the same master sheet? Ummmm......

Re: Merge Same Column From Multiple Files

WOW. I am sure you heard this before. but your the greatest. And such a quick responce! I am begining to think your the GOD of VBA.

Works perfect, thank you.

Re: Merge Same Column From Multiple Files

Ok Dave. Thanks for changing the title, appologies for my stupidity.

Ok well i have checked out the code, looks great, but I have no idea how to change it to match my needs. Could anyone here try and help? As stated before, I have hundreds of excel files that all have one column which I would like to copy accross. into one new excel sheet. So for example all of the excel files comprise of one sheet only, and its column D that I would like to copy into the new master sheet in sequesnce. Meannig first column D copies into column A. Second column D copies into column B ect ect.

I am sure this is possible just have no clue how to do it. I am not being jazy I have actually tried for hours but cant figure it out.HELP![hr]*[/hr] Auto Merged Post;[dl]*[/dl]

Option Explicit




Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False


On Error Resume Next


Set wbCodeBook = ThisWorkbook


    With Application.FileSearch
        .NewSearch
         'Change path to suit
        .LookIn = "R:\Structuring\Gas Structuring\curves"
        .FileType = msoFileTypeExcelWorkbooks
        '.Filename = "Book*.xls"
        
            If .Execute > 0 Then 'Workbooks in folder
                For lCount = 1 To .FoundFiles.Count 'Loop through all.
                 'Open Workbook x and Set a Workbook variable to it
                 Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                                                   
    Range("J7:J9").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("loopingthroughfilse.xls").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Windows.Activate
    Range("J7:J9").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("loopingthroughfilse.xls").Activate
    Range("B1").Select
    ActiveSheet.Paste
    Range("C1").Select
End Sub


                 wbResults.Close SaveChanges:=True
             
                 Next lCount
            End If
    End With
    
    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
End Sub

Ok i know this code is terrible and you are probably thinking I am drinking something heavy, but I really dont know how to make this work. All help much appreciated.

Re: Merging Data

Thanks for your responce.
There are hundreds of Excel files, each one of them only has ONE sheet. and each column i want to extract is the same column in each.
i am now going to read up on the link you sent to me. Thanks

I have hundreds and hundreds of excel files. but in every file, there is the same column lets say column D which has all the information I want. In stead of opening hundreds of worksheets and copying and pasting over the data into a new sheet. Is there a code I could write that would open all these files and copy the data from the same colum over into my new sheet? so column D in the first work book will copy to colulm A in the new work book. Then colum D in the second workboko will copy to the new worksheet in column B ect ect ect.

Any ideas most welcome!!

Re: Make Chart Series Dynamic

AAE's suggestions of a dynamic chart series works great! thank you.

Re: Update Chart With Macro

Thanks Zerothi, but it came up with errors stating chart or object not defined.
any ideas? I am sure doing something wrong.

I would like to update a chart after I have updated the underlying data just by clicking a button and displaying the updated date in the chart.

Does anyone know how to do this?
Currently I am having to manually click on the graphs line in the chart, which highlights the columns data, then manually extend this for the chart to update. And then I am manually typing in a date cell in the chart. There MUST be a cool way to do this.
HELP!!

I have 2 sheets in a workbook. Column A on sheet 1 has numbers such as 123456, 123457, 123458 ect ect . Column A on sheet 2 has same amount of numbers but mostly different. However there are a few duplicates.
QUESTION: How can i write a code that will fit in a new sheet lets say sheet 3 that will pull out only the list of numbers from sheet 1 and sheet 2 that are NOT duplicates?