Save Created sheet with different name

  • Hello ,

    The code below is working fine and creates a new sheet.

    The newly created sheet is saved by a constant name.

    Can be saved with the name of the sheet whEre the new sheet created plus a number (or no number just the name).

    (i,e. the name of the sheet that will get data and create a new sheet named 1.Power Distribution - Dimmer, so the new workgroup saved as this name)

    I have uploaded example. The module need to be changed is Module5


  • Could be a quick (static) solution:

    Dim N$
    N = ActiveWorkbook.Path & "\" & Worksheets("1.Power Distribution - Dimmer").Name & ".xlsx" '<- changed
  • rolls13. Thank you for your answer.

    I havent test the solution but I need name to be the active worksheet, the one I am retrieving the data from. It may be 1.Power Distribution - Dimmer or 2.PowerCycle2 or 3.Dimmers . the newly created sheet take the name of the sheet from the data is retrieved.

  • As said it would be static since the name of the sheet is hardcode and was already in your macro, here:

    For Each cell In Worksheets("1.Power Distribution - Dimmer").Range("D5:D9, ...

    How will the macro work with other sheet names ?

    So, all you have to do is create a new variable (example: shtName) at the beginning and assign to it the name of the active sheet to be used here in the For/Each and later down in the macro when you assign to the variable N the path and the name of the file to be saved.


  • @rollis13 is it possible to ask for something else for the same code?

    I have change the current code to working only for column D , and copy values but also copy formatting. I am stuck! Can only copy values? not formatting.

    I think I have to change code line

    Range(Cells(cell.Row, "C"), Cells(cell.Row, "E")).Copy Sheets("NewSheet").Cells(nr, "A")

    I think I have to add

    PasteSpecial Paste:=xlPasteValues

    But I am confused...

    Thank you !

  • Yes, but you need to divide the line of code like this:

    Range(Cells(cell.Row, "C"), Cells(cell.Row, "E")).Copy
    Sheets("NewSheet").Cells(nr, "A").PasteSpecial Paste:=xlPasteValues
  • I would also fix this, at the beginning you set it False then before ending your macro turn it back to True:

    Next i
    Application.ScreenUpdating = True             '<- fixed
    Const S = "NewSheet"
  • @rollis13 One more LAST help. (As you can understand I am newbie and trying to understand and learn). The code is working fine (extremely fine) but when I am trying to create a new Template, it all what it needs to do, but when I create the first template from sheet 1 (1.Power Distribution - Dimmer) working fine. When I am trying to create an other template (lets say from sheet 2.POWER CABLES - ADAPTORS) it appends the result to NewSheet (I dont know where finds it as it has been rename and moved) and creates the new Template with data from both sheets (append the results from both sheets, or all sheets that have data at column D).

    I cant figure out why this happens, Maybe needs to make a "refresh" and delete all the other data from memory. I dont know. I am trying all night to figure out. ?(

    I attach the example for you. Thank you !

  • 'Loop through all sheets in sheets array
    For i = LBound(ws) To UBound(ws)

    Here you are looping through all the sheets but the loop ends before the code that saves the new file. That means that the new file will always contain copy of all the sheets. Move the line Next i to the end of the macro and move the lines Set shtName = ActiveSheet and nr = 2 just inside the For i loop.

    Leave it to you to spot/fix any other issues (probably there won't be any).

  • @rollis13 Unfortenetly I cant make it work

    The code is this one now


  • Also done some cleaning out, have a try:

  • There was an extra space at the end of sheet "3. ...." too.

    Yes, if the macro gets interrupted you need to manually delete the "Newsheet" but you could implement, at the beginning of the macro, a check if there already is a sheet named Newsheet and delete it before proceeding. But this can only happen while testing, once the macro is definitve this should no longer occur.

  • Yes, You are absolutely correct (as anytime...) Everything is working fine, all templates are made correctly. But (there is always a but), now it creates templates for all the sheets not only the active sheet. You see every sheet will have its own button to create the template. How can this will be done? I know that you must be tired, correcting things, but this will be the last one (I hope...)

Participate now!

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