The account manager visits a lot of customers every month, the company requires a visit record summary table and to make a visit to the customer's sub-table, so that the supervisor spot checks, the structure of a table. At this time, if a customer fills in a customer, it is very annoying and time consuming. We can do a VBA button, each month as long as the summary part is filled out, just click on the button, automatically generate and customer information of the table, labor-saving and worry.
Figure A
Paste the code First:
SubCfsheet ()DimRng asRange, Sht asWorksheetSetRNG = Application.inputbox ("Please select a column to split","split Save as sheet ...", , , , , ,8)SetSht =rng. Parent'Get sheet name withSht rng. entirecolumn.copy [AZ1] Range ("Az:az"). RemoveDuplicates (1) End with 'Delete a worksheetApplication.DisplayAlerts =False fori = Sheets.count to 3 Step-1Sheets (i). DeleteNextApplication.DisplayAlerts=True 'New worksheetApplication.ScreenUpdating =FalseRW= [az1000]. End (3). Row fori =2 toRW Shtname= Sht. Range ("AZ"&i). Value Sheets.add (after:=sheets (Sheets.count)). Name =shtname Sheets ("visit the Record form"). Activate cells.copy Sheets (i+1). [A1] Sheets (i+1). Range ("B2") = Sheets ("Summary Table"). Range ("a"&i) Sheets (i+1). Range ("D2") = Sheets ("Summary Table"). Range ("b"&i) Sheets (i+1). Range ("B3") = Sheets ("Summary Table"). Range ("C"&i) Sheets (i+1). Range ("D3") = Sheets ("Summary Table"). Range ("D"&i) Sheets (i+1). Range ("B4") = Sheets ("Summary Table"). Range ("e"&i) Sheets (i+1). Range ("b5") = Sheets ("Summary Table"). Range ("F"&i) Sheets (i+1). Range ("b6") = Sheets ("Summary Table"). Range ("g"&i) Sheets (i+1). Range ("B7") = Sheets ("Summary Table"). Range ("h"&i) Sheets (i+1). Range ("B8") = Sheets ("Summary Table"). Range ("I"&i)Nextapplication.screenupdating=TrueSheets ("Summary Table"). Range ("Az:az"). ClearEnd Sub
Export the code as an add-on and add it to the toolbar, and you'll be OK with a little report every month.
Exel VBA Split sheet