Daily I will pull “Bulk Data” from my company’s server, and it will provide me with multiple columns of data. I will paste that bulk data into one sheet (Bulk Data) of my workbook. The number of rows of data will vary greatly, 1-50 (customers) depending on the day.
The next sheet (Corrected names) will clean up the data (combine first name with middle name and last name).
The third sheet is a template of a company form that needs to be printed for each of the different customers.
Requesting some help with an Excel VBA that will do multiple things. 1. For each row (customer), I need to copy the template form, rename the form by the customer’s name, then I need to copy 4 cells (from the second sheet) and paste the information into the newly created form, and repeat the process for all customer rows that have information and then print them off. I would like to do all of this from one command button.
Once I have created, renamed, copied/pasted and printed, I am thinking about creating a second command button to delete all the newly created company forms (sheets) and leave the 3 original sheets to repeat the next day.
I have been able to create a VBA that will create the company form and rename them with the customer’s name. However, when I attempt to copy the information that I am pulling from the "Corrected names" sheet, it will stop the function and only copy one company form will get created and nothing gets copied/pasted. I know my looping is the issue, but not sure how to correct. Copying and pasting into the 4 required cells into a sheet that is going to be created is really stumping me. Can I do this all in 1 sub, or does it need to be multiple? Using Excel 365.
I am a VBA novice and am just trying to optimize my time and productivity. I appreciate your assistance.
Macro to copy company form, and rename:
'Name macro
Sub CreateSheets()
Application.ScreenUpdating = False
'Dimension variables and declare data types
Dim rng As range
Dim cell As range
'Enable error handling
On Error GoTo Errorhandling
'Show inputbox to user and prompt for a cell range
Set rng = Application.InputBox(Prompt:="Select cell range:", _
Title:="Create sheets", _
Default:=Selection.Address, Type:=8)
'Iterate through cells in selected cell range
For Each cell In rng
'Check if cell is not empty
If cell <> "" Then
Set sourceSheet = ActiveSheet
ActiveCell.Copy
Sheets("Report template").Copy Before:=Sheets(1) 'Copy the Company form
ActiveSheet.Name = cell
End If
'Continue with next cell in cell range
Next cell
'Go here if an error occurs
Errorhandling:
Application.ScreenUpdating = True
'Stop macro
End Sub
The next sheet (Corrected names) will clean up the data (combine first name with middle name and last name).
The third sheet is a template of a company form that needs to be printed for each of the different customers.
Requesting some help with an Excel VBA that will do multiple things. 1. For each row (customer), I need to copy the template form, rename the form by the customer’s name, then I need to copy 4 cells (from the second sheet) and paste the information into the newly created form, and repeat the process for all customer rows that have information and then print them off. I would like to do all of this from one command button.
Once I have created, renamed, copied/pasted and printed, I am thinking about creating a second command button to delete all the newly created company forms (sheets) and leave the 3 original sheets to repeat the next day.
I have been able to create a VBA that will create the company form and rename them with the customer’s name. However, when I attempt to copy the information that I am pulling from the "Corrected names" sheet, it will stop the function and only copy one company form will get created and nothing gets copied/pasted. I know my looping is the issue, but not sure how to correct. Copying and pasting into the 4 required cells into a sheet that is going to be created is really stumping me. Can I do this all in 1 sub, or does it need to be multiple? Using Excel 365.
I am a VBA novice and am just trying to optimize my time and productivity. I appreciate your assistance.
Macro to copy company form, and rename:
'Name macro
Sub CreateSheets()
Application.ScreenUpdating = False
'Dimension variables and declare data types
Dim rng As range
Dim cell As range
'Enable error handling
On Error GoTo Errorhandling
'Show inputbox to user and prompt for a cell range
Set rng = Application.InputBox(Prompt:="Select cell range:", _
Title:="Create sheets", _
Default:=Selection.Address, Type:=8)
'Iterate through cells in selected cell range
For Each cell In rng
'Check if cell is not empty
If cell <> "" Then
Set sourceSheet = ActiveSheet
ActiveCell.Copy
Sheets("Report template").Copy Before:=Sheets(1) 'Copy the Company form
ActiveSheet.Name = cell
End If
'Continue with next cell in cell range
Next cell
'Go here if an error occurs
Errorhandling:
Application.ScreenUpdating = True
'Stop macro
End Sub
