Save Created sheet with different name

  • Do you mean that every sheet has to create it's own template ? Just get rid of the ws Array and the cycle For i.

    Just set a variable (ws) to read the name of the sheet from which you launch the macro.

    Also I would close the template just after saving it if you don't need it straight away (see note in macro).

  • Yes! This is working Fine ! Thank you, Thank you, Thank you !!!

    If I can ask, at the same workgroup , at sheet PROFORMA DRYHIRE , the macro is working fine, but when I press the button and the macro ends , do the job, it redirect to sheet 3.... Why is it doing that? I dont have any redirect to do that, maybe goes to last sheet before PROFORMA DRYHIRE.

    If it is not possible to answer that ( due to forum rules) I understand and I can open a new thread.

  • That's because you are activating the sheets Sheet(ws(i)).Activate to fetch the data and the last activated sheet will be the last of the ws Array so it's the "3. ...". It isn't necessary to activate a sheet to fetch data from it; just give the right reference to the ranges used.

    Now, first of all get rid of the trailing spaces in the sheet names, they only create confusion since they aren't visible.

    Then try this:

  • Sorry, found a typo, missed a reference to sheet, change this:

    Code
    Range(.Cells(cell.Row, "C"), .Cells(cell.Row, "E")).Copy

    to (note the leading dot):

    Code
     .Range(.Cells(cell.Row, "C"), .Cells(cell.Row, "E")).Copy
  • thank you for your answer! I will try it at night, cos I am at work right now! Thanks a lot. I was wondering, because at the excel there will be a lot of sheets, for the last one code, can it choose all the sheets and leave one out? ie choose all the sheets, except PROFORMA DRYHIRE the one that collects all the data. I have find something at the net but nope, doesnt work well. Thank you!

  • If you are using ws Array you will only pick from here, elsewise you could change your macro and add a If/Then, something like this:

    Code
    If Sheets.Name <> "PROFORMA DRYHIRE" Then
    'do something
    End If
  • Sorry, found a typo, missed a reference to sheet, change this:

    Code
    Range(.Cells(cell.Row, "C"), .Cells(cell.Row, "E")).Copy

    to (note the leading dot):

    Code
     .Range(.Cells(cell.Row, "C"), .Cells(cell.Row, "E")).Copy

    Yeap! Working like a charm ! Thank you!

  • Can I ask and some other thing? I want to make the below code to work globaly for all sheets (its from the same workgroup), I can manage to do it for just the activesheet but not globaly. (I tried but...)


    What should I change? Thank you

  • Sorry, but this time it's better to start a new thread, the title of this one has nothing to do with this last request.

    You are absolutely right. As I told you in my previous post, I dont want to go against forum rules! I will open a new one, about this matter. Again, thank you very very much for all the effort and your help! Thank you !!! ?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!