Problem to create different folders with a macro

[viewotst]

Hello everybody,

I have a question about one of my macros

I have the main tab “data” and then I have several tabs with the name of the cities of the data tab

I have done a macro to delete some of the columns this part actually work
Sub SplitLocation()
On Error Resume Next
Application.ScreenUpdating = False
Dim Ciudad As Worksheet, NombreCarpeta As String, Ruta As String
'
With Sheets("Data")
Mensaje = MsgBox("¿Está seguro de que quiere borrar el contenido de la actuales filas 1 y 2?", _
vbYesNo, "BORRAR CONTENIDO FILAS 1 Y 2")
Select Case Mensaje
Case vbYes
.Rows("1:2").ClearContents
Case vbNo
End Select
'
Mensaje = MsgBox("¿Está seguro de que quiere borrar las actuales columnas B y E?", _
vbYesNo, "BORRAR LAS ACTUALES COLUMNAS B y E")
Select Case Mensaje
Case vbYes
.Range("B:B, E:E").EntireColumn.Delete
Case vbNo
GoTo Siguiente
End Select
End With


but the following part does not work


Siguiente:
Exit Sub
NombreCarpeta = "Macro"
MkDir ThisWorkbook.Path & "\" & C:\Users\PedroHernandez\OneDrive - Macrixx\Desktop\Macro '
Ruta = ThisWorkbook.Path & "\" & C:\Users\PedroHernandez\OneDrive - Macrixx\Desktop\Macro & "\"
For Each Ciudad In Sheets
If Not Ciudad.Name = "Data" And Not Ciudad.Name = "Pivot" Then
Kill Ruta & Ciudad.Name & ".xlsx"
Ciudad.Copy
ActiveWorkbook.SaveAs Ruta & Ciudad.Name & ".xlsx"
ActiveWorkbook.Close SaveChanges:=True
End If
Next
MsgBox ("Los nuevos Libros con el nombre de las Hojas (Ciudades), se han guardado en " & Ruta)
End Sub

What I want the Macro to do is that it creates a folder per each city (each tab) from the “Macro for different workbooks template which is located in the following folder (the file is located in the folder “Macro”)

C:\Users\PedroHernandez\OneDrive - Macrixx\Desktop\Macro

Can you please help?

Thank you

[6StringJazzer]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)