Complex Macros needed to Make and Save Workbooks

**Stefan15** · 07-26-2017, 08:48 AM

Hello VBA Experts

Please I need a VBA code for the routine process below

Please attached 2 files, data and completed, i want a VBA code that i will run on the data file that will give me output in the completed file. The completed file is my desire result.

The process that invovle in creating the completed file from the data file are;

Below are the following steps

1. Make a copy of the YC tab in the data file and change the name of the new tab to YQ
3. On YQ tab, insert column C and name the column Dept
4. Enter a VLOOKUP formula (=VLOOKUP(B2,'BU Map'!A:B,2,FALSE) on column C to get the Dept names from the BU Map tab
5. Apply formula down to the last cell on column C
5. Apply filters on column C and delete all individual rows with the word “Delete DS-IWM CLOSED” appearing in Column C
6. Sums Columns F to L
7. Make a copy of YQ tab and rename the new tab as draft
8. On the draft tab, create two additional columns after Coop Students and label them in the sequence as shown below

Total PT (Column M)
Total FT (Column N)
Apply the formula below down the 2 columns
Total PT (Column M) =Sum (J2:L2)
Total FT (Column N) =Sum (I2+M2)

9. Make a copy of the draft tab and name the new tab as FT
10. In the FT tab Copy the entire columns and paste as values (to remove all formulas)
11. Delete columns F to M
12. Add a column after column G and name the column Delete_Zero and apply this formula on cell H2 =(F2=0,”Delete”,”-“) copy this formula to all rows below.
13. Apply filters on column G
14. Delete all rows appearing with the word ‘Delete’ in column G ‘Delete Zero’ (this is to remove all rows with zero FT numbers)
15. Sum column FT
16. Delete column G
16. Make a copy of draft tab and name it Student
17. On the student tab, delete all these columns "F to I and Column K to N "
18. Repeat steps 12 to 16 above
19. Make a copy of the draft tab and rename the new tab as "INTERNS"
20. On the Interns tab, delete all these columns "F to K and Columns M & N"
22. Repeat steps 12 to 16 above
23. Sum the Intern column
24. Update the Pivot table on the BU Map tab to link the current data on the draft tab

Thank you

**greyangel** · 07-27-2017, 01:31 PM

Please I need a VBA code for the routine process below

Please attached 2 files, data and completed, i want a VBA code that i will run on the data file that will give me output in the completed file. The completed file is my desire result.

The process that invovle in creating the completed file from the data file are;

Below are the following steps

1. Make a copy of the YC tab in the data file and change the name of the new tab to YQ
3. On YQ tab, insert column C and name the column Dept
4. Enter a VLOOKUP formula (=VLOOKUP(B2,'BU Map'!A:B,2,FALSE) on column C to get the Dept names from the BU Map tab
5. Apply formula down to the last cell on column C
5. Apply filters on column C and delete all individual rows with the word “Delete DS-IWM CLOSED” appearing in Column C
6. Sums Columns F to L
7. Make a copy of YQ tab and rename the new tab as draft
8. On the draft tab, create two additional columns after Coop Students and label them in the sequence as shown below

Total PT (Column M)
Total FT (Column N)
Apply the formula below down the 2 columns
Total PT (Column M) =Sum (J2:L2)
Total FT (Column N) =Sum (I2+M2)

9. Make a copy of the draft tab and name the new tab as FT
10. In the FT tab Copy the entire columns and paste as values (to remove all formulas)
11. Delete columns F to M
12. Add a column after column G and name the column Delete_Zero and apply this formula on cell H2 =(F2=0,”Delete”,”-“) copy this formula to all rows below.
13. Apply filters on column G
14. Delete all rows appearing with the word ‘Delete’ in column G ‘Delete Zero’ (this is to remove all rows with zero FT numbers)
15. Sum column FT
16. Delete column G
16. Make a copy of draft tab and name it Student
17. On the student tab, delete all these columns "F to I and Column K to N "
18. Repeat steps 12 to 16 above
19. Make a copy of the draft tab and rename the new tab as "INTERNS"
20. On the Interns tab, delete all these columns "F to K and Columns M & N"
22. Repeat steps 12 to 16 above
23. Sum the Intern column
24. Update the Pivot table on the BU Map tab to link the current data on the draft tab

Thank you

Here you go let me know if this works out. Please follow the code before and let me know if you understand. The pop-up that comes requires you to enter the full workbook path of the file that holds the YC and other tabs. Please note this macro does not update the pivot table. You will have to do that yourself. I am still somewhat of a newb myself.

ThisData.xlsm

**Stefan15** · 07-28-2017, 05:43 AM

Originally Posted by greyangel

Here you go let me know if this works out. Please follow the code before and let me know if you understand. The pop-up that comes requires you to enter the full workbook path of the file that holds the YC and other tabs. Please note this macro does not update the pivot table. You will have to do that yourself. I am still somewhat of a newb myself.

ThisData.xlsm

Hello Greyangel

Many thanks for your help, i treid running the macro but it came up with run-time error "9" and when Debug it seems it could execute a particular line of code. See attached screen shots

Once again, thanks for your help.

Capture.JPG Capture2.JPG

**greyangel** · 07-28-2017, 06:23 AM

1.) In the workbook you selected do you have a sheet named "YQ"? Can you send me the file you selected to run this macro on. In order for this macro to work you need a sheet labeled "YC", "YQ" and "Draft."

2.) Attached to this response is the exact same macro just a different way of selecting your file path. So the Filepath should be the path of the "completed" workbook.

3.) When you see prompts pop-up just click yes.

ThisData.xlsm

**Stefan15** · 07-28-2017, 07:58 AM

Originally Posted by greyangel

1.) In the workbook you selected do you have a sheet named "YQ"? Can you send me the file you selected to run this macro on. In order for this macro to work you need a sheet labeled "YC", "YQ" and "Draft."

2.) Attached to this response is the exact same macro just a different way of selecting your file path. So the Filepath should be the path of the "completed" workbook.

3.) When you see prompts pop-up just click yes.

ThisData.xlsm

Hi Greyangel

I have just 2 tabs in the data file named YC and BU Map. the YC tab contains the raw data, when i make a copy of the YC tab, i now rename the new tab YQ

I would have attach the file for you to see, but there is not attach button in the quick reply bar.

Thanks

**greyangel** · 07-28-2017, 09:52 AM

If you click "Go Advanced" when you click reply you can attach the document. Here try this one out as well and tell me if this is what you are looking for... This makes the "Data" file that you submitted look identical to the "completed" file. It uses the "Q6_Rpt" tab to run. Will your initial tab always be labeled "YC" or does it originally come named as "Q6_Rpt"? At the very end of the code I forgot to type in "End sub" you can go add that to the code at the end though so it runs correctly

.

ThisData.xlsm

**p45cal** · 07-28-2017, 11:21 AM

The attached does things a little differently; it doesn't delete any 'DELETE DS- IWM CLOSED' rows, it filters them out instead.
It only adds the 3 sheets, no additional formulae on the existing sheets.
It doesn't duplicate any sheets.
It temporarily adds three calculated columns (L,M & N) to the raw data sheet but deletes them again afterwards, so the raw data sheet is left as it was.
Regarding updating the pivot table (which I haven't done):
1. The current source data is in a workbook on your system ('\Toronto\wrkgrp\wrkgrp11\Shared Services Reporting Centre\Report Catalogue\Wealth Management\WMC166\FY 2017\08 June\GV3\[02_FTE_Jun 17.xlsx]Calc'!$A$1:$N$172).
2. What you might want to change the Source Data to has a different set of headers so it will screw up.

The attached is simply your Data.xlsx file from your first message with the below code added and a button in the vicinity of cell C4 of ther BU Map sheet to execute it.

Sub blah()
With Sheets("q6_Rpt")  '<<<<change this name to the raw data's sheet name.
  With Intersect(.UsedRange.EntireRow, .Range("L:N"))
    .Formula = Array("=VLOOKUP($B1,'BU Map'!$A:$B,2,0)", "=SUM($I1:$K1)", "=SUM($H1:$K1)")
    .Rows(1).Value = Array("Dept", "PT TOTAL", "FT TOTAL")
    Set SceRng = Intersect(.Parent.UsedRange, .Parent.Range("A:N"))
    SheetNames = Array("FT", "Students", "Interns")
    CriterionHdrs = Array("FT TOTAL", "PT TOTAL", "Coop Students")
    FinalHeader = Array("FT TOTAL", "Students", "Coop Students")
    For i = 0 To 2
      With Sheets.Add(After:=Sheets(Sheets.Count))
        .Range("Q1:R1").Value = Array("Dept", CriterionHdrs(i))
        .Range("Q2:R2").Value = Array("<>DELETE DS- IWM CLOSED", ">0")
        .Range("A1:E1") = Array("DeptNo", "IneaDeptNo", "Dept", "DeptName", "Month")  ', "")
        .Range("F1") = FinalHeader(i)
        SceRng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range("Q1:R2"), CopyToRange:=.Range("A1:F1"), Unique:=False
        .Range("Q1:R2").Clear
        .Name = SheetNames(i)
        With .UsedRange
          .WrapText = False
          .EntireColumn.AutoFit
          .WrapText = True
          If .Rows.Count > 1 Then .Cells(.Cells.Count).Offset(1).FormulaR1C1 = "=SUM(R2C:R" & .Rows.Count & "C)"
        End With
      End With
    Next i
    .EntireColumn.Clear
  End With  'Intersect(UsedRange.EntireRow, .Range("L:N"))
End With  'Sheets("YC").UsedRange
End Sub

**Stefan15** · 07-28-2017, 11:47 AM

Originally Posted by p45cal

The attached does things a little differently; it doesn't delete any 'DELETE DS- IWM CLOSED' rows, it filters them out instead.
It only adds the 3 sheets, no additional formulae on the existing sheets.
It doesn't duplicate any sheets.
It temporarily adds three calculated columns (L,M & N) to the raw data sheet but deletes them again afterwards, so the raw data sheet is left as it was.
Regarding updating the pivot table (which I haven't done):
1. The current source data is in a workbook on your system ('\Toronto\wrkgrp\wrkgrp11\Shared Services Reporting Centre\Report Catalogue\Wealth Management\WMC166\FY 2017\08 June\GV3\[02_FTE_Jun 17.xlsx]Calc'!$A$1:$N$172).
2. What you might want to change the Source Data to has a different set of headers so it will screw up.

The attached is simply your Data.xlsx file from your first message with the below code added and a button in the vicinity of cell C4 of ther BU Map sheet to execute it.

Sub blah()
With Sheets("q6_Rpt")  '<<<<change this name to the raw data's sheet name.
  With Intersect(.UsedRange.EntireRow, .Range("L:N"))
    .Formula = Array("=VLOOKUP($B1,'BU Map'!$A:$B,2,0)", "=SUM($I1:$K1)", "=SUM($H1:$K1)")
    .Rows(1).Value = Array("Dept", "PT TOTAL", "FT TOTAL")
    Set SceRng = Intersect(.Parent.UsedRange, .Parent.Range("A:N"))
    SheetNames = Array("FT", "Students", "Interns")
    CriterionHdrs = Array("FT TOTAL", "PT TOTAL", "Coop Students")
    FinalHeader = Array("FT TOTAL", "Students", "Coop Students")
    For i = 0 To 2
      With Sheets.Add(After:=Sheets(Sheets.Count))
        .Range("Q1:R1").Value = Array("Dept", CriterionHdrs(i))
        .Range("Q2:R2").Value = Array("<>DELETE DS- IWM CLOSED", ">0")
        .Range("A1:E1") = Array("DeptNo", "IneaDeptNo", "Dept", "DeptName", "Month")  ', "")
        .Range("F1") = FinalHeader(i)
        SceRng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range("Q1:R2"), CopyToRange:=.Range("A1:F1"), Unique:=False
        .Range("Q1:R2").Clear
        .Name = SheetNames(i)
        With .UsedRange
          .WrapText = False
          .EntireColumn.AutoFit
          .WrapText = True
          If .Rows.Count > 1 Then .Cells(.Cells.Count).Offset(1).FormulaR1C1 = "=SUM(R2C:R" & .Rows.Count & "C)"
        End With
      End With
    Next i
    .EntireColumn.Clear
  End With  'Intersect(UsedRange.EntireRow, .Range("L:N"))
End With  'Sheets("YC").UsedRange
End Sub

Hello GreyAngel and p45Cal

Thank you so much for your help, the "blah code" seems to work now, except for the YQ tab, draft tab and pivot table update, which i can manually update myself.

Thanks guys, you are the best

Stefan15

**greyangel** · 07-28-2017, 11:53 AM

Originally Posted by Stefan15

Hello GreyAngel and p45Cal

Thank you so much for your help, the "blah code" seems to work now, except for the YQ tab, draft tab and pivot table update, which i can manually update myself.

Thanks guys, you are the best

Stefan15

Hey stefan15 I forgot to close out my vba code in my previous post. So here is the updated one so it runs correctly. This looks exactly like your finished product when ran.

**Stefan15** · 07-28-2017, 12:11 PM

Originally Posted by greyangel

Hey stefan15 I forgot to close out my vba code in my previous post. So here is the updated one so it runs correctly. This looks exactly like your finished product when ran.

Many Thanks Greyangel

**mdmackillop** · 07-30-2017, 05:24 AM

Hi
For the future, please do not quote whole posts as Post #8, post only those lines relevant to your question.
Regards
MD

Thread: Complex Macros needed to Make and Save Workbooks

Thread Tools

Display

Complex Macros needed to Make and Save Workbooks

Posting Permissions