Need help with Macros / VBA

[ashokpandian]

Hi All,


I am trying to create a DOCU Tracker , in my tracker there are 3 sheets , sheet 2 is a registration sheet , once it was filled by the user the form in the sheet 3 should be generated as new excel file or new sheet in the same excel file. I am using the below script it helps me to create a new copy sheet of sheet 3 , but it force me to save the file again as a new file.


Sub Copier()
Dim x As Integer
x = InputBox("Enter number of times to copy Sheet1")
For numtimes = 1 To x
ActiveWorkbook.Sheets("CAPA Form").Copy _
After:=ActiveWorkbook.Sheets("CAPA Form")
Next
End Sub


Please help me to resolve the issue

 

[Fishboy]

Hi All,


I am trying to create a DOCU Tracker , in my tracker there are 3 sheets , sheet 2 is a registration sheet , once it was filled by the user the form in the sheet 3 should be generated as new excel file or new sheet in the same excel file. I am using the below script it helps me to create a new copy sheet of sheet 3 , but it force me to save the file again as a new file.


Sub Copier()
Dim x As Integer
x = InputBox("Enter number of times to copy Sheet1")
For numtimes = 1 To x
ActiveWorkbook.Sheets("CAPA Form").Copy _
After:=ActiveWorkbook.Sheets("CAPA Form")
Next
End Sub


Please help me to resolve the issue

Hi ashokpandian, welcome to the boards.

Having tested your code, if you are trying to get the document to save after adding the copied sheets but for it not to save as a new file you can add the following line to the end of your code just before the End Sub line:

ActiveWorkbook.Save

So the full code would read:

Sub Copier()
Dim x As Integer
x = InputBox("Enter number of times to copy Sheet1")
For numtimes = 1 To x
ActiveWorkbook.Sheets("CAPA Form").Copy _
After:=ActiveWorkbook.Sheets("CAPA Form")
Next
ActiveWorkbook.Save
End Sub

 

[ashokpandian]

Hi All,


I am trying to create a DOCU Tracker , in my tracker there are 3 sheets , sheet 2 is a registration sheet , once it was filled by the user the form in the sheet 3 should be generated as new excel file or new sheet in the same excel file. I am using the below script it helps me to create a new copy sheet of sheet 3 , but it force me to save the file again as a new file.


Sub Copier()
Dim x As Integer
x = InputBox("Enter number of times to copy Sheet1")
For numtimes = 1 To x
ActiveWorkbook.Sheets("CAPA Form").Copy _
After:=ActiveWorkbook.Sheets("CAPA Form")
Next
End Sub


Please help me to resolve the issue

Thank you!!

 

[ashokpandian]

Hi ,

I am still facing the same issue ,


Sub SavePDF()
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="D:\Users\51441884\Desktop\Tracking tool\CAPA Form.pdf", _
OpenAfterPublish:=True
End Sub
Sub Copier()
Dim x As Integer
x = InputBox("Enter number of times to copy Sheet1")
For numtimes = 1 To x
ActiveWorkbook.Sheets("CAPA Form").Copy _
After:=ActiveWorkbook.Sheets("CAPA Form")
Next
ActiveWorkbook.Save
End Sub



This is the code i have in my worksheet but its not working.


My Requirement is simple. I need to populate the same type of form again and again in the same excel.

Sheet1- Registration Form (To enter the Form1 details and so on)
Sheet2-General Form (This form should be populate again and again depends on the information which entered in the sheet1)


Please explain with some example so that i can modify code for my requirement.


Thank you.

 

[Fishboy]

Hi ,

I am still facing the same issue ,

Sub SavePDF()
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:="D:\Users\51441884\Desktop\Tracking tool\CAPA Form.pdf", _
        OpenAfterPublish:=True
End Sub

Sub Copier()
Dim x As Integer
x = InputBox("Enter number of times to copy Sheet1")
For numtimes = 1 To x
ActiveWorkbook.Sheets("CAPA Form").Copy _
After:=ActiveWorkbook.Sheets("CAPA Form")
Next
ActiveWorkbook.Save
End Sub

This is the code i have in my worksheet but its not working.


My Requirement is simple. I need to populate the same type of form again and again in the same excel.

Sheet1- Registration Form (To enter the Form1 details and so on)
Sheet2-General Form (This form should be populate again and again depends on the information which entered in the sheet1)


Please explain with some example so that i can modify code for my requirement.


Thank you.

As far as I can see this is working as you describe, the only snag I encountered in my test document was if the original workbook wasn't saved as a macro enabled workbook (.xlsm format) then an error popped up relating to macros and not having them enabled.

On the understanding that your original workbook is macro-enabled, the 2 macros you have linked do the following at my end:

Copier:

1. Input box comes up asking how many COPA forms you want to copy
2. The number of copies you specify are made where the main COPA form is copied and added to the right of the main COPA form
3. The document saves over itself (not to a new file as per your request)

SavePDF:

1. Whatever is the active sheet is saved to the specified file path as CAPA Form.pdf
2. The freshly saved document is opened

If you look at both of the above descriptions, can you let me know what is being missed out, or what is happening that shouldn't be happening. As far as I can tell, everything I doing what it should.