consolidate or create a new vba code for performing tasks
Below are the steps that i want to perform in a excel file
Step 1: Delete first 6 rows
Step 2: Filter by column A and delete blank rows
Step 3: Based on Column "C" Create a new excel file for uniq values (example if have value as "1" in column C for three rows it have to create new file as "Australia_1" in the drive specified by user or in the same drive where the macro is being run
or if i am not clear on the step 3 if i create a pivot by column c and for each value of the count in need a separate file
to perform above tasks I have gathered some code from various websites
I have about 3 codes taken from different websites
The first ( Sub sbVBS_To_Delete_EntireRow_For_Loop()) macro deletes first 6 rows
The Second one (Sub DeleteRows()) filters Col A and delete blanks
The Third one (parse_data()) copies the data for all the unique values from column C and creates a new sheet
the fourth one (Sub newfile()) create a new work file
I have two questions
1. How can I consolidate all into one code and if possible how can create as an application or UI
2. when creating a new file how can i add a prefix to the file name along with worksheet name (for example every filename starts with "Australia"
Sub sbVBS_To_Delete_EntireRow_For_Loop()Dim iCntrFor iCntr = 1 To 6 Step 1Rows(1).EntireRow.DeleteNextEnd SubSub DeleteRows() With ActiveSheet .AutoFilterMode = False 'remove filter With .Range("A:Q") .AutoFilter field:=1, Criteria1:="=" On Error Resume Next ' for the case when there is no visible rows .Resize(.Rows.Count - 1).Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete On Error GoTo 0 End With .AutoFilterMode = False 'remove filter End WithEnd Sub
Sub newfile()MyPath = ThisWorkbook.PathFor Each sht In ThisWorkbook.Sheetssht.CopyActiveSheet.Cells.CopyActiveSheet.Cells.PasteSpecial Paste:=xlPasteValuesActiveSheet.Cells.PasteSpecial Paste:=xlPasteFormatsActiveWorkbook.SaveAs _Filename:=MyPath & "\" & sht.Name & ".xls"ActiveWorkbook.Close savechanges:=FalseNext shtEnd Sub
>>1. How can I consolidate all into one code and if possible how can create as an application or UI
You can create a userform in Excel.
>>2. when creating a new file how can i add a prefix to the file name along with worksheet name (for example every filename starts with "Australia"
amend this line in Sub newfile() from:
Open in new windowto:
Open in new window?