Using Loop to copy different column values from a Source tab to 3 different Excel tabs

Hi, I am pretty new to VBA Macros, I have a problem in looping. Need some help. I have described the whole process.

I have a source Excel tab called Shipper VP. It has Shipment values of different months for different years for three different persons called Jason Cox, Ken Sustarsic and Steve Martin.

I want to create a macro using loops. I have done the whole process by recording three different macros for each person. But the code in these macros is very long and most of things are common, I want to use loops and make the code efficient without entering any values manually.

I want to use a loop, For n = 1 to 3; when n = 1,

I want to create an additional sheet and rename it by copying the name of the person from Shipper VP tab which is B4 value in this case. So now an additional sheet is created and it is named as JASON COX.

After that I want to enter different values in the JASON COX tab.

Now in the following step which is the important one, I want to copy different column values from Shipper VP tab to the Jason Cox tab.

Copy values of B9:B17 (from Shipper VP tab) to the cell values D7:D15 (into the JASON COX tab)
Copy values of B19:B30 (from Shipper VP tab) to the cell values D16:D27 (into the JASON COX tab)
Copy values of B32:B43 (from Shipper VP tab) to the cell values D28:D39 (into the JASON COX tab)
Copy values of B45:B56 (from Shipper VP tab) to the cell values D40:D51 (into the JASON COX tab)
Copy values of B58:B69 (from Shipper VP tab) to the cell values D52:D63 (into the JASON COX tab)

After that again lot of other values are entered in the JASON COX tab and bunch of calculations are done and based on the values in two different columns, a chart is created with the heading title JASON COX and chart is renamed as Jason Cox .

Now in the next loop, n= 2,

I want the same exact thing for the second person called Ken Sustarsic.

I want to create a second sheet and rename it by copying the name of the person from Shipper VP tab which is C4 value in this case. So now an additional sheet is created and it is named as KEN SUSTARSIC.

After that I want to enter different values in the KEN SUSTARSIC tab which are exactly as I did for JASON COX.

But now I want to copy different column values from Shipper VP tab to the KEN SUSTARSIC tab.

Copy values of C9:C17 (from Shipper VP tab) to the cell values D7:D15 (into the KEN SUSTARSIC tab)
Copy values of C19:C30 (from Shipper VP tab) to the cell values D16:D27 (into the KEN SUSTARSIC tab)
Copy values of C32:C43 (from Shipper VP tab) to the cell values D28:D39 (into the KEN SUSTARSIC tab)
Copy values of C45:C56 (from Shipper VP tab) to the cell values D40:D51 (into the KEN SUSTARSIC tab)
Copy values of C58:C69 (from Shipper VP tab) to the cell values D52:D63 (into the KEN SUSTARSIC tab)

After that again lot of other new values are entered in the KEN SUSTARSIC tab and bunch of calculations are done and based on the values in two different columns, a chart is created with the heading title KEN SUSTARSIC and chart is renamed as Ken Sustarsic. (This part is same as done before JASON COX).

Now in the next loop, n= 3, I want the same exact thing for the third person called Steve Martin, (only this time all the values will be taken from D column of Shipper VP tab).

I wanted to fully describe the whole process. Any help in the looping code will be really appreciated. Basically the main problem is copying values of different columns from Shipper VP to the individual tabs and incrementing it in the loop. The calculations can be done.



Thanks,
Dip

I got the answer, thanks.

dipjyoti123

Would you mind sharing your solution so people with a similar problem can use your answer.
After all, we all try to help each other

Originally Posted by dipjyoti123

I got the answer, thanks.

This is the code, it works, I got help from another person, please inform me if you have any doubts, Thanks.

Sub Test()
Dim wsSource As Worksheet, ws As Worksheet
Dim rg As Range, c As Range
Dim n As Integer


Set wsSource = Sheets("Shipper VP")
Set rg = wsSource.Range("B4:D4")
n = 1
For Each c In rg
'add sheet and rename
Set ws = Sheets.Add
ws.Name = c.Text


'copy data
With wsSource
.Range("B9:B17").Offset(0, n - 1).Copy ws.Range("D7:D15")
.Range("B19:B30").Offset(0, n - 1).Copy ws.Range("D16:D27")
'etc...
End With


'other calculations
'...

n = n + 1
Next c
End Sub

That's good.
If someone else has a similar problem you be glad to know that you helped that person.
Thanks for sharing