VBA - copy multiple column data in to a list

[rashid67]

I need a vba code to copy multiple columns from sheet1 and paste as list in sheet2

Here is current data format in sheet1

[TABLE="width: 534"]
<tbody>[TR]
[TD][/TD]
[TD] H[/TD]
[TD]G[/TD]
[TD] H[/TD]
[TD] I[/TD]
[TD] J[/TD]
[TD] K[/TD]
[TD]L[/TD]
[TD] M[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD][/TD]
[TD]001/2018[/TD]
[TD][/TD]
[TD]002/2018[/TD]
[TD][/TD]
[TD]003/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD][/TD]
[TD][/TD]
[TD]8000[/TD]
[TD][/TD]
[TD]9000[/TD]
[TD][/TD]
[TD]9500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD][/TD]
[TD]London[/TD]
[TD][/TD]
[TD]NewYork[/TD]
[TD][/TD]
[TD]Paris[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD] GL acct[/TD]
[TD]Name[/TD]
[TD] Plan[/TD]
[TD] Actual[/TD]
[TD] Plan[/TD]
[TD] Actual[/TD]
[TD] Plan[/TD]
[TD] Actual[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD] 1000[/TD]
[TD]Salary[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]450[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]390[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD] 1001[/TD]
[TD]Travel[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]135[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]117[/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD] 1002[/TD]
[TD]Meal[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]39[/TD]
[/TR]
</tbody>[/TABLE]


New format in sheet2 -list

[TABLE="width: 512"]
<tbody>[TR]
[TD="width: 64"]Date[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]Site no[/TD]
[TD="width: 64"]Site[/TD]
[TD="width: 64"]GL acct[/TD]
[TD="width: 64"]Name[/TD]
[TD="width: 64"]Plan[/TD]
[TD="width: 64"]Actual[/TD]
[/TR]
[TR]
[TD]001/2018[/TD]
[TD][/TD]
[TD]8000[/TD]
[TD]London[/TD]
[TD]1000[/TD]
[TD]Salary[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]450[/TD]
[/TR]
[TR]
[TD]001/2018[/TD]
[TD][/TD]
[TD]8000[/TD]
[TD]London[/TD]
[TD]1001[/TD]
[TD]Travel[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]135[/TD]
[/TR]
[TR]
[TD]001/2018[/TD]
[TD][/TD]
[TD]8000[/TD]
[TD]London[/TD]
[TD]1002[/TD]
[TD]Meal[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]002/2018[/TD]
[TD][/TD]
[TD]9000[/TD]
[TD]NewYork[/TD]
[TD]1000[/TD]
[TD]Salary[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]400[/TD]
[/TR]
[TR]
[TD]002/2018[/TD]
[TD][/TD]
[TD]9000[/TD]
[TD]NewYork[/TD]
[TD]1001[/TD]
[TD]Travel[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]002/2018[/TD]
[TD][/TD]
[TD]9000[/TD]
[TD]NewYork[/TD]
[TD]1002[/TD]
[TD]Meal[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]003/2018[/TD]
[TD][/TD]
[TD]9500[/TD]
[TD]Paris[/TD]
[TD]1000[/TD]
[TD]Salary[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]390[/TD]
[/TR]
[TR]
[TD]003/2018[/TD]
[TD][/TD]
[TD]9500[/TD]
[TD]Paris[/TD]
[TD]1001[/TD]
[TD]Travel[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]117[/TD]
[/TR]
[TR]
[TD]003/2018[/TD]
[TD][/TD]
[TD]9500[/TD]
[TD]Paris[/TD]
[TD]1002[/TD]
[TD]Meal[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]39[/TD]
[/TR]
</tbody>[/TABLE]

 

[footoo]

Are the data in rows 16:17 in columns H, J, L ; or in columns G,I,K and centered or merged?

 

[rashid67]

data in row 16:18 starts from column I and it is not merged or centered

data in row 20:22 starts from column G

Thanks.

 

[footoo]

data in row 16:18 starts from column I and it is not merged or centered

data in row 20:22 starts from column G

Thanks.

That doesn't match what you posted.

Are the columns like this :
Col F - GL acct
Col G - Name
Col H - Plan
Col I - Actual
Col J - Plan
Col K - Actual
Col L - Plan
Col M - Actual

Or like this :
Col G - GL acct
Col H - Name
Col I - Plan
Col J - Actual
Col K - Plan
Col L - Actual
Col M - Plan
Col N - Actual

 

[rashid67]

its like this

ol G - GL acct
Col H - Name
Col I - Plan
Col J - Actual
Col K - Plan
Col L - Actual
Col M - Plan
Col N - Actual

 

[rashid67]

its like this

Col G - GL acct
Col H - Name
Col I - Plan
Col J - Actual
Col K - Plan
Col L - Actual
Col M - Plan
Col N - Actual

 

[footoo]

Sub FT()
Dim rng As Range, c%
Application.ScreenUpdating = False
ActiveSheet.Copy After:=ActiveSheet
[A:C].Delete
[A:C].ClearContents
[A19] = "Date"
[B19] = "Site no"
[C19] = "Site"
Set rng = Range([F16], Cells(16, Columns.Count).End(xlToLeft))
For c = 1 To rng.Columns.Count Step 3
    rng(1, c).Resize(3).Copy
    Cells(Rows.Count, "A").End(xlUp)(2).Resize(3, 3).PasteSpecial Paste:=xlPasteAll, Transpose:=True
Next
Set rng = Range([H20], Cells(20, Columns.Count).End(xlToLeft))
For c = 1 To rng.Columns.Count Step 3
    rng(1, c).Resize(3, 3).Copy Cells(Rows.Count, "E").End(xlUp)(2)
Next
Rows("1:18").Delete
rng.EntireColumn.Delete
[D2:D4].Copy Range([D5], Cells(Rows.Count, "C").End(xlUp)(1, 2))
[A:C].EntireColumn.AutoFit
End Sub