Help Updating VBA code to include formulas and condt'l formatting when Pasting Data to Multiple workbooks

[futureexcelguru]

Hi all! I'm needing some help updating an existing code to paste the data into my new sheet hopefully including formulas, data validation, and conditional formatting. Relatively new to VBA and had some help creating the below, so any help is appreciated!

Its set up to create a new sheet based on text in Column B:B on the Q3-Q4 worksheet. For example, Column B has 5 text options (Test 1, Test 2, Test 3, Test 4, Test5) across 5000 rows. The new workbooks created from the below code will create 5 workbooks to a specified folder path per B text with all the data/rows applicable, its just pasting values and i'm losing most of the formatting formulas. *It is pasting the fill/text colors and borders that populated from the conditional formatting entered on the main workbook

Code here:

Option Explicit

Sub Split_Data_in_workbooks()

Application.ScreenUpdating = False

Dim data_sh As Worksheet
Set data_sh = ThisWorkbook.Sheets("Q3-Q4")

Dim setting_Sh As Worksheet
Set setting_Sh = ThisWorkbook.Sheets("Settings")

Dim nwb As Workbook
Dim nsh As Worksheet

setting_Sh.Range("A:A").Clear
data_sh.AutoFilterMode = False
data_sh.Range("B:B").Copy setting_Sh.Range("A1")

setting_Sh.Range("A:A").RemoveDuplicates 1, xlYes

Dim i As Integer

For i = 2 To Application.CountA(setting_Sh.Range("A:A"))

data_sh.UsedRange.AutoFilter 2, setting_Sh.Range("A" & i).Value


Set nwb = Workbooks.Add
Set nsh = nwb.Sheets(1)

data_sh.UsedRange.SpecialCells(xlCellTypeVisible).Copy nsh.Range("A1")
nsh.UsedRange.EntireColumn.ColumnWidth = 15


nwb.SaveAs setting_Sh.Range("H6").Value & "/" & setting_Sh.Range("A" & i).Value & ".xlsx"
nwb.Close False
data_sh.AutoFilterMode = False
Next i

setting_Sh.Range("A:A").Clear

MsgBox "Done"

End Sub

 

[Alex Blakenburg]

I had a couple of issue with the code but not copying across formulas & formatting weren't amongst them.
1) I suggest you change to copy to the settings sheet to copying values only.
If there are any formulas in column B they cause and issue when you are only copying column B

    'data_sh.Range("B:B").Copy setting_Sh.Range("A1")  <--- replaced by the next 2 lines
    data_sh.Range("B:B").Copy
    setting_Sh.Range("A1").PasteSpecial Paste:=xlPasteValues

2) Turn the screen updating back on at the very end of the code

Application.ScreenUpdating = true

3) The code as it stands seems to copy everything - formulas, formatting, conditional formatting, constants etc.
Copying any references that are not self contained in the UsedRange of the sheet Q3-Q4 is going to be an issue.
eg if your data validation list is on a different sheet or the formulas link to a different sheet.

Also if your usedrange on Q3-Q4 doesn't start at A1 it could upset anything that is formula based since you are copying it to A1 in the new workbook. Can you confirm Q3-Q4 has data in A1 ?

Ideally provide us with an XL2BB of your data and we can see if any of the above come in to play.

 

[futureexcelguru]

I had a couple of issue with the code but not copying across formulas & formatting weren't amongst them.
1) I suggest you change to copy to the settings sheet to copying values only.
If there are any formulas in column B they cause and issue when you are only copying column B

    'data_sh.Range("B:B").Copy setting_Sh.Range("A1")  <--- replaced by the next 2 lines
    data_sh.Range("B:B").Copy
    setting_Sh.Range("A1").PasteSpecial Paste:=xlPasteValues

2) Turn the screen updating back on at the very end of the code

Application.ScreenUpdating = true

3) The code as it stands seems to copy everything - formulas, formatting, conditional formatting, constants etc.
Copying any references that are not self contained in the UsedRange of the sheet Q3-Q4 is going to be an issue.
eg if your data validation list is on a different sheet or the formulas link to a different sheet.

Also if your usedrange on Q3-Q4 doesn't start at A1 it could upset anything that is formula based since you are copying it to A1 in the new workbook. Can you confirm Q3-Q4 has data in A1 ?

Ideally provide us with an XL2BB of your data and we can see if any of the above come in to play.

Thank you Alex for your guidance. Here is an xlbb of a test section - i've made the updates to the code that you suggested above, and i can confirm that Q3-Q4 does start on A1.
To your point, the data validation was linked to another sheet.. I updated the data validation dropdown items to the same sheet, and they copied to the new workbooks. However, it only pulled the list in the items that pulled from Row B:B, attached a screenshot for reference.. any suggestions in how to maintain the data validation? The only other thing that was lost are the formulas in the columns

LIP Bonus Split VBA XLBB Test.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	EE ID	Workbooks	Current Status	Hire Date	Prev Term Date	Rehire Date	Term Date	Leave Start	Leave End	Annual $	Position	Starting LIP Eligiblity	Ending LIP Eligiblity	Eligible Days in Month	Actual Days in Mo	% of Month	1/12 Salary	Month	Work Location	LIP Mapped Location	VALIDATION SELECTION REQ'D	Corrected Location, or Pooled Location 1	Pooled Location 2	Pooled Location 3 (If Applicable)	Pooled Location 4 (If Applicable)
2	EE1	Test 1	Active	06/22/1998	00/00/0000	00/00/0000	00/00/0000	04/10/2020	06/16/2021	0	Manager, Operations I	08/01/2021	08/31/2021	31	31	100%	0.00	August	74 FL	74 FL	Column Z Location is INCORRECT, 1 location needs updating	Enter Updated Location
3	EE2	Test 1	Active	03/27/2017	00/00/0000	00/00/0000	00/00/0000	00/00/0000	00/00/0000	50000	Manager, Operations II	08/01/2021	08/31/2021	31	31	100%	4166.67	August	22 CA	22 CA	Column Z Location is CORRECT, only 1 allocation needed
4	EE3	Test 8	Active	12/16/2019	00/00/0000	00/00/0000	00/00/0000	05/23/2020	05/20/2021	70000	Supervisor, Operations	08/01/2021	08/31/2021	31	31	100%	5833.33	August	95 NJ	95 NJ	Pooled Employee	Select Pooled Location 1	Select Pooled Location 2	Select Pooled Location 3	Select Pooled Location 4
5	EE4	Test 1	Active	05/18/2015	00/00/0000	00/00/0000	00/00/0000	00/00/0000	00/00/0000	80000	Supervisor, Inventory Control	08/01/2021	08/31/2021	31	31	100%	6666.67	August	22 CA	22 CA	Column Z Location is CORRECT, only 1 allocation needed
6	EE5	Test 8	Active	07/14/2014	00/00/0000	00/00/0000	00/00/0000	10/01/2020	04/25/2021	90000	Manager, Warehouse	08/01/2021	08/31/2021	31	31	100%	7500.00	August	4 CA	4 CA	Column Z Location is CORRECT, only 1 allocation needed
7	EE6	Test 8	Active	02/15/2002	00/00/0000	00/00/0000	00/00/0000	10/12/2020	01/01/2021	100000	Manager, Operations II	08/01/2021	08/31/2021	31	31	100%	8333.33	August	228 CA	228 CA	Column Z Location is CORRECT, only 1 allocation needed
8	EE7	Test 6	Active	08/18/2014	09/18/2020	02/22/2021	00/00/0000	12/06/2021	01/09/2022	90000	Sr. Manager, Operations	12/01/2021	12/05/2021	5	31	16%	7500.00	December	126 CA	126 CA	Column Z Location is INCORRECT, 1 location needs updating	Enter Updated Location
9	EE8	Test 8	Active	10/29/2018	00/00/0000	00/00/0000	00/00/0000	10/25/2020	01/10/2021	60000	Supervisor, Operations	08/01/2021	08/31/2021	31	31	100%	5000.00	August	6 CA	LOCATION NOT ON VALIDATION LIST	Column Z Location is INCORRECT, 1 location needs updating	Enter Updated Location
10	EE9	Test 8	Active	03/27/2017	00/00/0000	00/00/0000	00/00/0000	11/05/2020	06/27/2021	60000	Supervisor, Processing	08/01/2021	08/31/2021	31	31	100%	5000.00	August	28 CA	28 CA	Column Z Location is CORRECT, only 1 allocation needed
11	EE10	Test 8	Active	04/29/2019	00/00/0000	00/00/0000	00/00/0000	11/10/2020	02/28/2021	50000	Supervisor, Operations	08/01/2021	08/31/2021	31	31	100%	4166.67	August	94 NJ	94 NJ	Column Z Location is CORRECT, only 1 allocation needed
12	EE11	Test 5	Active	05/11/2015	00/00/0000	00/00/0000	00/00/0000	00/00/0000	00/00/0000	60000	Sr. Manager, Operations	08/01/2021	08/31/2021	31	31	100%	5000.00	August	528 CA	528 CA	Column Z Location is CORRECT, only 1 allocation needed
13	EE12	Test 8	Active	08/13/2016	00/00/0000	00/00/0000	00/00/0000	00/00/0000	00/00/0000	80000	Supervisor, Operations	08/01/2021	08/31/2021	31	31	100%	6666.67	August	6 CA	LOCATION NOT ON VALIDATION LIST	Column Z Location is INCORRECT, 1 location needs updating	Enter Updated Location
14	EE13	Test 8	Active	08/10/1998	00/00/0000	00/00/0000	00/00/0000	00/00/0000	00/00/0000	90000	Supervisor, Customer Service	08/01/2021	08/31/2021	31	31	100%	7500.00	August	6 CA	LOCATION NOT ON VALIDATION LIST	Column Z Location is INCORRECT, 1 location needs updating	Enter Updated Location
15	EE14	Test 8	Active	06/01/2015	00/00/0000	00/00/0000	00/00/0000	00/00/0000	00/00/0000	100000	Director, Operations	08/01/2021	08/31/2021	31	31	100%	8333.33	August	112 CA	112 CA	Column Z Location is CORRECT, only 1 allocation needed
Q3-Q4

Cell Formulas
Range	Formula
P2:P15	P2	=N2/O2
Q2:Q15	Q2	=J2/12
V2,V4:V15	V2	=IF($U2="Column Z Location is incorrect, 1 Location Needs updating","Enter Updated Location",IF($U2="Pooled Employee","Select Pooled Location 1"," "))
W2:W15	W2	=IF($U2="Pooled Employee","Select Pooled Location 2"," ")
X2:X15	X2	=IF($U2="Pooled Employee","Select Pooled Location 3"," ")
Y2:Y15	Y2	=IF($U2="Pooled Employee","Select Pooled Location 4"," ")
N2:N15	N2	=M2-L2+1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
V:V	Cell Value	contains "Updated"	text	NO
V:Y	Cell Value	contains "Select"	text	NO
T1,T9,T13:T14,T16:T1048576	Cell Value	contains "Validation"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
U2:U15	List	=Dropdowns!$A$2:$A$4
V2:V15	List	=Dropdowns!$E$2:$E$15
W2:Y15	List	=Dropdowns!$E$2:$E$15

 

[Alex Blakenburg]

To get the best result requires a bit of updating on your main spreadsheet.
I suggest that you keep the validation lists on the Sheet "Dropdowns".

Make each of the Dropdown lists an official Excel Table. Give the table a name, I suggest prefixing them all with "tbl".
Now create a Named Range for each table and point the Named Range at the table. I suggest prefixing the range names with "dd".
Now change all you validations in the main spreadsheet to use the Range Names eg List > ddLocation

This will have the added benefit that whenever you add additional items to the list the table will automatically expand and this will flow through to the validation options.

If you do the above then the code below should copy both the formulas and validation.
Note: It will copy the sheet Dropdowns to the new workbook, we can hide the sheet if that is what you want.

Sub Split_Data_in_workbooks()

    Application.ScreenUpdating = False
    
    Dim data_sh As Worksheet
    Set data_sh = ThisWorkbook.Sheets("Q3-Q4")
    
    Dim setting_Sh As Worksheet
    Set setting_Sh = ThisWorkbook.Sheets("Settings")
    
    Dim nwb As Workbook
    Dim nsh As Worksheet
    
    setting_Sh.Range("A:A").Clear
    data_sh.AutoFilterMode = False
    data_sh.Range("B:B").Copy
    setting_Sh.Range("A1").PasteSpecial Paste:=xlPasteValues
    
    setting_Sh.Range("A:A").RemoveDuplicates 1, xlYes
    
    Dim i As Integer
    
    For i = 2 To Application.CountA(setting_Sh.Range("A:A"))
    
        data_sh.UsedRange.AutoFilter 2, setting_Sh.Range("A" & i).Value
        
        Set nwb = Workbooks.Add
        Set nsh = nwb.Sheets(1)
        nsh.Name = data_sh.Name
        ThisWorkbook.Worksheets("Dropdowns").Copy after:=nwb.Sheets(1)

        Application.DisplayAlerts = False
            data_sh.UsedRange.SpecialCells(xlCellTypeVisible).Copy
            nsh.Range("A1").PasteSpecial Paste:=xlPasteAll
        Application.DisplayAlerts = True
        nsh.UsedRange.EntireColumn.ColumnWidth = 15
    
        Application.CutCopyMode = False
        nsh.Activate
        nsh.Range("A1").Select

        nwb.SaveAs setting_Sh.Range("H6").Value & "/" & setting_Sh.Range("A" & i).Value & ".xlsx"
        nwb.Close False
        data_sh.AutoFilterMode = False
    Next i
    
    setting_Sh.Range("A:A").Clear
    
    MsgBox "Done"
    
    Application.ScreenUpdating = True

End Sub