Transform table to list

Hyakkivn

New Member
Joined
Jul 28, 2021
Messages
45
Office Version
  1. 2010
Platform
  1. Windows
Greeting everyone
I have a workbook like in picture below.
I work on 4 sheets "KT","Train_Station", "VD" and "Port" (the sheet VD, Port and Train_Station have same structure)
The row in yellow is each day on Month. I would like some help on VBA that work like:
At sheet("Port"): For each cell in day 1, if cell value = 1, then copy the staff ID to Sheet("KT") at range E2; then at range B2 of KT write the date, and at J2 write the full date. And loop til the end of month
Sub transform()
Dim n1 As Range, n2 As Range, n3 As Range, n4 As Range, cel As Range ' ... n31 as Range
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Worksheets("KT")
Set ws2 = Worksheets("Port")
Set n1 = ws2.Range("D5:D" & Cells(Rows.Count).End(xlUp).Row) ' and n2, n3, ...n31 all day in month
For Each cel In n1
If cel = 1 Then
cel.Offset(, -1).Copy 'with n2 cel.offset(,-2) ; n3 cel.offset(,-3)...
ws1.Range("E2").Select
ActiveCell.PasteSpecial xlPasteValues
Range("B2") = 1 '1 is the 1st day of month
End If
Next
End Sub
Thanks for any help :love:
2.png
1.png

2.png

1.png
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,066
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Please don't keep bumping your post that often. You're actually reducing the chances of it being seen, since many of the regular posters start by looking at questions with no replies.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,839
Messages
5,766,725
Members
425,374
Latest member
EgleA

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
Top