Posts by richardcranney

    Hi there,

    Just wondered if I could pick someones brain out there for something.

    Im wanting to create a macro that deletes the Modules in a worksheet.

    I currently have module 1, 2, and 3, and when the macro runs, im just wanting it to delete the modules.

    Can anyone help?


    Hi there

    I am trying to create a macro that copys a certain worksheet and places it second to last everytime the macro is recorded.

    I have tried doing this using the record macro facility, but this places it in the different positions everytime I try.

    Has anyone ever managed to do this?


    Re: Getting an error on this macro

    Just to let you know some more progress ive had with this, well, or not!

    When running macro "Start", all this does is stores the filenames, and calls "collect" to get information.

    When "collect" runs, it opens the file and this is showing as the active window, but when it uses code:

    For each sht in ThisWorkbook.sheets

    and then runs:


    This seems to be where the problem is. This isnt showing the open file as the active file, but going back to tables.xls.

    Can anyone help with this? Ive attatched another file which has been slightly modded, but if you download it and run it, the run macro "start", you will see what im on about.

    Thanks in advance

    Re: Getting an error on this macro

    Hi, thanks for the reply, but it does need to be saved as an .xls file.

    I have designed a webpage that opens the excel file, but the files need to be saved as excel files.

    Have you come across the error before? For some reason I cannot make it swing through all the sheets on the newly loaded sheet.

    Hi there,

    Its hard to explain but ill try

    I have a file called table.xls, and im wanting this file to go onto my webspace and load a file.

    I then want it to scroll through each sheet in this file opened and collect date from cell H46 and record it in the table.xls.

    Ive had a go, but get some errors, just wondered if anyone could help.

    Im trying to get it so that when it opens user 1, it displays this on say, line 1, and goes through each sheet in that user and the table moves accross cells to write the information.

    Then when a new user is loaded, the cell moves down, and does the same.

    Thanks for your help in advance.

    Re: Copy data from an external file


    The web query doesnt allow me to take the data i need, thanks for trying though.

    I have done the following, which works brilliantly, apart from one thing.

    Because it is selecting so much information, when the file closes, it asks me if I want to keep this informaiton on the clipboard. Is there anyway of disabling this message and automatically asking it to keep it on the clipboard?

    This is the code I have at the minute, did it just from a record macro:

    Workbooks.Open Filename:= ""

    Re: Copy data from an external file


    Thanks for the quick reply.

    This doesnt allow me to take the information from an excel file on a web server though, only from a file on my network/computer.

    I was hoping to have it set as a macro where its just one click of a button. I've seen it done on here ages ago, i think RoyUK came up with the solution, but cant fully remember.

    Hi there,

    I am trying to create something such as a macro to Copy information from an external excel file held on a web server.

    Say for instance, i have open my excel file on my computer, but I have a master copy on the web server I use which is what gets updated.

    I would like to be able to copy cells A1:G492 from "main" sheet to the "backup" sheet in my local excel file.

    Has anyone come accross this before?

    Many thanks

    Hi there,

    im making a stat sheet for my company and have 1 problem i was hoping you could help with:

    Im doing a holiday clocker so people put their hours in that they have had holiday (IE 07:00 - 16:30) which is a full day, and this adds it up at the end.

    Now this only shows me the hours, I have tried to make the code display it as days by dividing it by 9.5 which works fine, until it is something like 5 hours which shows as 0.526315789473684

    Now this can be changed in the cell formatiing to a 2 decimal place, but unfortunately I have this in the cell which displays the information a bit different:
    ="("&SUM(H368*24/9.5)&" days)"

    Which displays it as:
    (0.526315789473684 days)

    and the cell formating doesnt work with this type of formula.

    Does anyone know how I can get this to either 1 or 2 decimal places?