Copy data from one sheet to another using a loop

Question

I have a spreadsheet named Home. It contains information about users in a database. Each user has a manager, which is one of the columns in the sheet.

I've already created a macro that adds a sheet named for every manager in the Home sheet, and a separate sheet with the name of each manager. That sheet is named "Managers" Now I want to filter the Home sheet for each manager, copy the data for that manager, and paste it in the sheet named for that manager. I believe I can use a loop for that but I don't know how to set it up. I have the VBA for filtering the Home sheet for the manager. I just need to know how to set up the loop that will go through the list of managers, filter for that manager on the Home sheet, and then copy and past that info on the corresponding sheet with that managers name.

thanks for your help[

Answer 1

As an alternative, you can use the FILTER function if you have Microsoft 365 or Office 2021.

Answer 2

@hansv mvp

That seems like way more code than I need. I already have a sheet for each Manager so I don't need to create another one.

I just need to go to the Home tab, filter the data, copy that data to the tab of the manager with the same name and then go to the next manager. I have a tab with all the manager names I need something like

Dim I as text

I = the first manager in the list

for I = first manager to the last manager

go to Home tab

filter for the manager

copy the data

Past the data in the tab with the same name

go back to home

Next I

Answer 3

See for example my reply in VBA script for a Macro