Problema al guardar libro de excel con procedimiento creado desde macro

H o l a:

No entendí esta parte:

"

El problema surge al querer guardar el libro mediante esta misma macro, ya que al hacerlo el procedimiento no se ve reflejado

"

¿Tienes otra macro?

¿O qué es lo que quieres guardar?

si. tengo un libro principal el cual corre una serie de procedimientos, entre ellos crea un libro nuevo, le coloca ciertas columnas y le pone un nombre: archivo x

lo que busco es que mediante estos mismos procedimientos antes de ponerle nombre al "archivo x", entre a las propiedades de la hoja 2 y le coloque el sub:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.CellDragAndDrop = False
End Sub

para que al salvar el "archivo x" ya no permita la función de copiar y pegar específicamente en las hojas que deseo (hoja 2) , le ponga nombre a ese archivo  y continúe creando el siguiente archivo de una lista determinada.

 el código que ingreso para colocar el sub dentro de la hoja 2 del archivo x es el siguiente:

Dim VBAEditor As VBIDE.VBE
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim CodePan As VBIDE.CodeModule
Dim St As String
libro = nombre & "_" & apellido & "_" & year & "_" & month & ".xlsm"
Application.Workbooks(libro).VBProject.References.AddFromGuid _
GUID:="{0002E157-0000-0000-C000-000000000046}", _
Major:=5, Minor:=3
Set VBAEditor = Application.VBE
Set VBProj = Application.Workbooks(libro).VBProject
hja = 0
fin = Worksheets.Count
For hja = 2 To fin
Set VBComp = VBProj.VBComponents("Hoja" & hja)
Set CodeMod = Application.Workbooks(libro).VBProject.VBComponents("Hoja" & hja).CodeModule
strN = strN & "Private Sub Worksheet_SelectionChange(ByVal Target As Range)" & vbCr
strN = strN & "Application.CellDragAndDrop = False" & vbCr
strN = strN & "End Sub" & vbCr
CodeMod.AddFromString strN

hasta ese punto si detengo la depuración y abro el vbaproject del "archivo x" y veo mi hoja 2, esta contiene el código que se ingresó. sin embargo si salvo el documento con este código:

ActiveWorkbook.SaveAs Filename:= _
("C:\Evaluaciones\Archivos a Enviar\" & nombre & "_" & apellido & "_" & year & "_" & month & ".xlsm"), FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

el archivo queda salvado, pero el cambio que le realicé a la hoja 2 no se aplica. el único modo en que este puede quedar aplicado es deteniendo la ejecución de la macro y salvando el archivo con el botón de guardar del archivo x. 

espero que con esto quede mas claro el problema

Creo que ya entendí.

Lo que quieres hacer es poner una macro en todas las hojas.

La macro que pone la macro, está en el mismo libro.

¿Eso es lo que quieres?

¿Y quieres la macro para que no tengas que hacerlo hoja por hoja?

La otra opción es que lo hagas solamente una vez, pero en los eventos de Thisworkbook:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Index > 1 Then Application.CellDragAndDrop = False
End Sub

También me marca un error en esta variable: year, 

Si es una variable debes ponerle otro nombre.

Si quieres obtener el año, entonces debe ser así:

Year(date)

Lo mismo para Month.

No sé si en tu macro tienes On Error Resume Next y eso hace que el error de la variable no se muestre y no guarde el libro.

Revisa lo de la variable year y month, a lo mejor ese es tu problema.

Avísame cualquier detalle.

Incluso si tienes problemas, pon la macro completa, ya que no veo que valores tienen las variables year, month

Sal u dos

El código que me colocas en teoría funciona si lo pongo y guardo manualmente, sin embargo mediante la macro no he podido hacerlo ya que al guardar el documento. Estos eventos no se ven reflejados. 

Respondiendo a tu pregunta, la macro que genera los eventos para evitar el copiado y pegado está en una hoja diferente.

Las variables year y month ya están declaradas y las toma mediante un combo box . de hecho como te contento el código se ejecuta bien, incluso guarda el libro de forma correcta, el problema está que al abrir el libro creado el evento private sub no queda guardado. 

El código que te puse, no es para que lo pongas en la macro, ya que tienes dificultades con la macro.

Te estoy dando una alternativa que en lugar de hacerlo 20 veces, lo hagas solamente una vez manualmente.

Si estás creando un nuevo libro, puedes copiar la hoja y te copia las macros. Por ejemplo:

Sub CrearLibro()
'Por.Dante Amor
    Set l1 = ThisWorkbook
    Set h1 = l1.Sheets("Hoja2")
    '
    h1.Copy
    ruta = "C:\Evaluaciones\Archivos a Enviar\"
    'ruta = "C:\trabajo\"
    ActiveWorkbook.SaveAs Filename:= _
            ruta & nombre & "_" & apellido & "_" & Year(Date) & "_" & Month(Date) & ".xlsm", _
            FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

Lo que hace es copiar la hoja2  y te crea un nuevo libro, en ese mismo paso copia las macros que tenga la hoja2. Guardas el nuevo libro habilitado para macros. Abres el nuevo libro y ahí están las macros y funcionando.

O empieza desde el principio, describe con exactitud qué es lo que quieres hacer y pon tus macros completas.

Si ya existe el libro x

libro = nombre & "_" & apellido & "_" & year & "_" & month & ".xlsm"

Entonces lo que faltaría es solamente guardarlo.

En lugar de esto:

ActiveWorkbook.SaveAs Filename:= _
("C:\Evaluaciones\Archivos a Enviar\" & nombre & "_" & apellido & "_" & year & "_" & month & ".xlsm"), FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Tienes que poner simplemente

Workbooks(libro). Save

Sigo investigando qué es lo que tienes, pero sin la macro completa, ¿no sé si el libro ya existe o lo estás creando?

el libro x como tál ya existe, ya que en el se agregan varias cosas como columnas y formatos a las celdas se le colocan nombres y diversas opciones. es por eso que la alternativa de copiar la hoja tal vez no sea la mejor opción. 

ya intenté la opción que me das con el tema de salvar el libro con Workbooks(libro).Save

pero el resultado sigue siendo el mismo (no guarda el private sub en el libro x.) si lo crea y  se ve en el código de la hoja en cuestión (la del libro x), pero al guardar el libro, esté private sub, se borra)

con respecto a colocar toda la macro es complicado ya que es un desarrollo de 1000 lineas pero te puedo explicar un poco lo que hace.

la hoja con la macro principal está hecha para crear archivos de evaluación los cuales cada persona llenará. entonces esta macro crea cada hoja individual que a su vez está dividida en 3 partes. hoja de auto evaluación (hoja1) , hoja de evaluación  a compañeros de trabajo (hoja2) y hoja de evaluación a subordinados hoja3, si es que aplica).

al crear estas hojas personales determina si utiliza la plantilla pre fabricada de 2 o de 3 hojas dependiendo de la hoja de la persona que está creando. 

ya que termina de construir estas hojas, les coloca valores a celdas especificas que se utilizarán para crear reportes de dichas evaluaciones, realizar los cálculos necesarios y presentar las hojas finales. 

la intención es que ya al finalizar de construir los archivos del personal le coloque el private sub en la hoja2 o 3 según el caso para evitar que el personal copie y pegue las celdas y así de algún modo obligarlos al menos a colocar un valor "razonado" y no un simple copy paste.

la opción que me diste en la que la hoja ya traiga el private sub desde un origen me parece buena alternativa, solo que a la inversa. es decir, que ya tenga por default el valor:

Application.CellDragAndDrop = False

y que al abrir la hoja desde la macro principal cambie el valor a true mediante código y así de ese modo poder realizar los cambios necesarios en las hojas correspondientes del libro x y asi, si  ese valor efectivamente no se guarda, pasará nuevamente a un valor false.

de cualquier manera, dame tiempo de hacer una macro más sencilla para poder pegarla y que me puedas apoyar a replicarla para ver si efectivamente no se guarda esta private sub  por falta de algún parámetro en el código o por alguna configuración en el excel que yo manejo. por cierto, la versión de office con la que estoy trabajando es 2007 por si sirve de referencia 

¿Pero el libro ya está creado como xlsm?

Si ya está creado y le agregas la macro y solamente le pones .save, si lo guarda.

Ya hice la prueba y sí me funcionó.

También mencionas esto:

"

Entonces esta macro crea cada hoja individual que a su vez está dividida en 3 partes.

"

Si tienes una macro que crea una hoja, entonces puedes aplicar lo que te comenté; en el libro que tienes las macro, pon una hoja "modelo", esa hoja la copias al nuevo libro que estás creando y se copiará con todo y macros.

Envíame tu archivo con macros y explícame paso a paso qué es lo que haces, me dices:

Paso1, abres el libro "x",

Paso 2, entras a vba

Paso 3, ejecutas la macro "y"

Etc.

Etc.

Si me explicas con detalle cada uno de los pasos que realizas hasta el punto donde tienes el problema, me será más práctico desarrollar una solución.

Mi correo [email protected]

En el asunto del correo escribe tu nombre de usuario “Juan Sanchez” y el título de esta pregunta.

listo. te acabo de enviar el correo con una descripción breve y el archivo 

quedo en espera de tu respuesta. 

mil gracias