dynamic range selection

tamtadada

New Member
Joined
Mar 20, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi guys,
here`s the scene: I work for a work agency and I have a set of data containing name, salary etc. which belong to workers for different companies who use our outsourcing services. there`s about 50 of them and after each months end I need to provide a workbook where each company account has a separate sheet with data related to the workers.
I have a pivot table with the data I need to fill in each of the sheets ("pivot") and also another pivot table which serves as a list of our internal numbers of the company accounts used in the current month ("DED")
I have figured out a code which will pick up the individual company account numbers and create a new sheet for each of them

What I can`t figure out is how to copy relevant data for each account from the "pivot" sheet, ie. go to the pivot, search for the account number (which is in rows) and copy all the workers data which belong to the account (the number of workers - the number of rows is different every time, the number of columns is 6 - there are usually empty cells within the selected range) into the relevant account tab where I have a template which needs filled in and the pasting should start in cell B9.

I can add empty lines between each of the account data to cut the pivot into small pivots thus making it possible to use the xlDown selection possible.

Here`s the code I`m using for the part that I have figured out - I suppose it`s best to add more code into this loop rather than making it a separate loop but whatever works is much appreciated!

VBA Code:
Dim LR As Integer, i As Integer
    Application.ScreenUpdating = False
    With Sheets("DED")
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        For i = 1 To LR
            Sheets("Template").Copy After:=Sheets("Template")
            ActiveSheet.Name = .Range("A" & i).Value
            Range("B4").Value = .Range("A" & i).Value
    Next i
    End With
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
why not start from the raw data instead of the pivot? Then you can just run through all the workers in a loop adding them to the relevant worksheet depending on the company they work for
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top