Macro to copy values across rows and transposing them and add the user id

Y

yazzy10

New Member
Joined
Nov 11, 2019
Messages
9
Hi,


I wonder if someone can help me with this problem with amacro.

I have a table of data that appears below(input data) whichhas usernames down a few rows and one or more accounts across 70 columns.

On a regular basis, I would need to able to copy all theaccount numbers that are present in each cell and copy it into a column (see output data)

Also I would need to add the username in a second column(see output data).

Given that this task could involve hundreds of usernames oraccount numbers, I would need a macro to cut down the time to process thisfile.

I would really appreciate the help!

Many thanks,





Input Data

[TABLE="width: 5244"]
<colgroup><col width="138" style="width: 104pt; mso-width-source: userset; mso-width-alt: 5046;"> <col width="139" style="width: 104pt; mso-width-source: userset; mso-width-alt: 5083;"> <col width="162" style="width: 122pt; mso-width-source: userset; mso-width-alt: 5924;"> <col width="88" style="width: 66pt; mso-width-source: userset; mso-width-alt: 3218;" span="9"> <col width="96" style="width: 72pt; mso-width-source: userset; mso-width-alt: 3510;" span="60"> <tbody>[TR]
[TD="width: 138, bgcolor: transparent"]Username[/TD]
[TD="width: 139, bgcolor: transparent"]Laat Login Date/Time
[/TD]
[TD="width: 162, bgcolor: transparent"]No. Registered Premises[/TD]
[TD="width: 88, bgcolor: transparent"]Account No 1[/TD]
[TD="width: 88, bgcolor: transparent"]Account No 2[/TD]
[TD="width: 88, bgcolor: transparent"]Account No 3[/TD]
[TD="width: 88, bgcolor: transparent"]Account No 4[/TD]
[TD="width: 88, bgcolor: transparent"]Account No 5[/TD]
[TD="width: 88, bgcolor: transparent"]Account No 6[/TD]
[TD="width: 88, bgcolor: transparent"]Account No 7[/TD]
[TD="width: 88, bgcolor: transparent"]Account No 8[/TD]
[TD="width: 88, bgcolor: transparent"]Account No 9[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 10[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 11[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 12[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 13[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 14[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 15[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 16[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 17[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 18[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 19[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 20[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 21[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 22[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 23[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 24[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 25[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 26[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 27[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 28[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 29[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 30[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 31[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 32[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 33[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 34[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 35[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 36[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 37[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 38[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 39[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 40[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 41[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 42[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 43[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 44[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 45[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 46[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 47[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 48[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 49[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 50[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 51[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 52[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 53[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 54[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 55[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 56[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 57[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 58[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 59[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 60[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 61[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 62[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 63[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 64[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 65[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 66[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 67[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 68[/TD]
[TD="width: 96, bgcolor: transparent"]Account No 69[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]user1@btmail.com[/TD]
[TD="bgcolor: transparent"]31/07/2014 11:55[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]77777777[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]user2@hotmaiz.com[/TD]
[TD="bgcolor: transparent"]05/08/2014 07:57[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]88888888[/TD]
[TD="bgcolor: transparent"]63047667[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]user3@infinity.co.uk[/TD]
[TD="bgcolor: transparent"]07/08/2014 10:12[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]33333333[/TD]
[TD="bgcolor: transparent"]63577302[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]user4@gmain.co.in[/TD]
[TD="bgcolor: transparent"]03/09/2014 14:22[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]10232284[/TD]
[TD="bgcolor: transparent"]10785397[/TD]
[TD="bgcolor: transparent"]11151490[/TD]
[TD="bgcolor: transparent"]11440526[/TD]
[TD="bgcolor: transparent"]11607435[/TD]
[TD="bgcolor: transparent"]11607883[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]12149027[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]12885213[/TD]
[TD="bgcolor: transparent"]12910505[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]13180598[/TD]
[TD="bgcolor: transparent"]13187596[/TD]
[TD="bgcolor: transparent"]13417730[/TD]
[TD="bgcolor: transparent"]14424676[/TD]
[TD="bgcolor: transparent"]14564348[/TD]
[TD="bgcolor: transparent"]14647086[/TD]
[TD="bgcolor: transparent"]1468423&[/TD]
[TD="bgcolor: transparent"]14803502[/TD]
[TD="bgcolor: transparent"]1551641&[/TD]
[TD="bgcolor: transparent"]15612261[/TD]
[TD="bgcolor: transparent"]15612548[/TD]
[TD="bgcolor: transparent"]15847958[/TD]
[TD="bgcolor: transparent"]15947863[/TD]
[TD="bgcolor: transparent"]16105148[/TD]
[TD="bgcolor: transparent"]16440107[/TD]
[TD="bgcolor: transparent"]16756737[/TD]
[TD="bgcolor: transparent"]16907931[/TD]
[TD="bgcolor: transparent"]17136016[/TD]
[TD="bgcolor: transparent"]17318737[/TD]
[TD="bgcolor: transparent"]17632812[/TD]
[TD="bgcolor: transparent"]1772301&[/TD]
[TD="bgcolor: transparent"]17766300[/TD]
[TD="bgcolor: transparent"]17940358[/TD]
[TD="bgcolor: transparent"]18373186[/TD]
[TD="bgcolor: transparent"]18463559[/TD]
[TD="bgcolor: transparent"]19040073[/TD]
[TD="bgcolor: transparent"]19066580[/TD]
[TD="bgcolor: transparent"]19214967[/TD]
[TD="bgcolor: transparent"]19819836[/TD]
[TD="bgcolor: transparent"]19949322[/TD]
[TD="bgcolor: transparent"]20153068[/TD]
[TD="bgcolor: transparent"]20156206[/TD]
[TD="bgcolor: transparent"]20474099[/TD]
[TD="bgcolor: transparent"]21029187[/TD]
[TD="bgcolor: transparent"]30559698[/TD]
[TD="bgcolor: transparent"]30724894[/TD]
[TD="bgcolor: transparent"]31810617[/TD]
[TD="bgcolor: transparent"]31965052[/TD]
[TD="bgcolor: transparent"]33100497[/TD]
[TD="bgcolor: transparent"]36517050[/TD]
[TD="bgcolor: transparent"]40538897[/TD]
[TD="bgcolor: transparent"]4920494&[/TD]
[TD="bgcolor: transparent"]50189801[/TD]
[TD="bgcolor: transparent"]50572297[/TD]
[TD="bgcolor: transparent"]63059010[/TD]
[TD="bgcolor: transparent"]63072394[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]63632481[/TD]
[TD="bgcolor: transparent"]71491640[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]91347316[/TD]
[TD="bgcolor: transparent"]91513302[/TD]
[TD="bgcolor: transparent"]91519584[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]user5@topaz..uk[/TD]
[TD="bgcolor: transparent"]08/09/2014 12:13[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]11750080[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]12637498[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]12924166[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]71492154[/TD]
[TD="bgcolor: transparent"]91207976[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]user6@yahool.co.uk[/TD]
[TD="bgcolor: transparent"]12/09/2014 11:55[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]11640187[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]6309404&[/TD]
[TD="bgcolor: transparent"]63135369[/TD]
[TD="bgcolor: transparent"]63530898[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]


Output Data


[TABLE="width: 150"]
<colgroup><col width="64" style="width: 48pt;"> <col width="136" style="width: 102pt; mso-width-source: userset; mso-width-alt: 4973;"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Account[/TD]
[TD="width: 136, bgcolor: transparent"]Username
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]77777777[/TD]
[TD="bgcolor: transparent"]user1@btmail.com[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]88888888[/TD]
[TD="bgcolor: transparent"]user2@hotmaiz.com[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]63047667[/TD]
[TD="bgcolor: transparent"]user2@hotmaiz.com[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
Try this, change "Input" and "Output" for the name of your sheets

Code: 
Sub copy_values_across()
  Dim sh1 As Worksheet, sh2 As Worksheet, a() As Variant, b() As Variant
  Dim lr As Long, lc As Long, i As Long, j As Long, n As Long
  Set sh1 = Sheets("[COLOR=#0000ff]Input[/COLOR]")
  Set sh2 = Sheets("[COLOR=#0000ff]Output[/COLOR]")
  sh2.Rows("2:" & Rows.Count).ClearContents
  lr = sh1.Range("A" & Rows.Count).End(xlUp).Row
  lc = sh1.Cells(1, Columns.Count).End(xlToLeft).Column
  a = sh1.Range("A2", sh1.Cells(lr, lc)).Value
  ReDim b(1 To (UBound(a) * lc), 1 To 2)
  n = 1
  For i = 1 To UBound(a, 1)
    For j = 4 To lc
      If a(i, j) <> "" Then
        b(n, 1) = a(i, 1)
        b(n, 2) = a(i, j)
        n = n + 1
      End If
    Next
  Next
  sh2.Range("A2").Resize(UBound(b), 2).Value = b()
End Sub
 
Upvote 0
Hi Dante,

Thank you forposing you reply.

Although im having a runtime error 1004 on the last line of code:

sh2.Range("A2").Resize(UBound(b), 2).Value = b()

Would you know what is the cause of this?

Again many thanks or helping out
 
Upvote 0
What does the error message say?
Do you have formulas on the sheet?
The formulas have error?
Do you have merged cells?
Is the output sheet protected?
Do You have empty rows?

The macro assumes that you have headings in row 1 and that your data begins in row 2 in column A.
Maybe you can share more details of how your information is on the sheets.
 
Upvote 0
What does the error message say?

9PlI0ELams 4AOCLYMYFIDkELgDJIXABSA6BC0ByCFwAkkPgApAcAheA5BC4ACSHwAUgOQQuAMkhcAFIDoELQHIIXACSQ ACkBwCF4DkELgAJIfABSA5BC4AySFwAUiMyP95itAhXw6bqAAAAABJRU5ErkJggg==


Do you have formulas on the sheet? No
The formulas have error? No
Do you have merged cells?No
Is the output sheet protected?No
Do You have empty rows?No

The macro assumes that you have headings in row 1 and that your data begins in row 2 in column A.-The layout is exactly the same in the example above so data begins in row 2 in column A.

Many thanks
 
Upvote 0
Did you modify some of the macro?

What version of excel and office do you have?
 
Last edited:
Upvote 0
Try this

Code: 
Sub copy_values_across()
  Dim sh1 As Worksheet, sh2 As Worksheet, a() As Variant, b() As Variant
  Dim lr As Long, lc As Long, i As Long, j As Long, n As Long
  Set sh1 = Sheets("Input")
  Set sh2 = Sheets("Output")
  sh2.Rows("2:" & Rows.Count).ClearContents
  lr = sh1.Range("A" & Rows.Count).End(xlUp).Row
  lc = sh1.Cells(1, Columns.Count).End(xlToLeft).Column
  a = sh1.Range("A2", sh1.Cells(lr, lc)).Value
  ReDim b(1 To (UBound(a) * lc), 1 To 2)
  n = 1
  For i = 1 To UBound(a, 1)
    For j = 4 To lc
      If a(i, j) <> "" Then
        b(n, 1) = a(i, 1)
        b(n, 2) = a(i, j)
        n = n + 1
      End If
    Next
  Next
  
  For i = 1 To UBound(b)
    sh2.Range("A" & i).Value = b(i, 1)
    sh2.Range("B" & i).Value = b(i, 2)
  Next
End Sub

Some instructions are different for version 365.
If the above does not work, we will have to wait for someone else who can help, my version is Excel 2007.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
A
  • Question Question
code to copy last row & exclude zero value of the data in last row for each name
Replies
0
Views
291
Abdo
A
A
  • Question Question
Multiple Account Drawdown
Replies
0
Views
771
Autonomous
A
H
  • Question Question
Macros - Auto Copy paste row when cell value changes
Replies
7
Views
6K
Fluff
Fluff
U
  • Question Question
VBA - Copy Row Data Based on Criteria
Replies
2
Views
1K
unknownymous
U

Forum statistics

Threads
1,223,886
Messages
6,175,191
Members
452,616
Latest member
intern444

Share this page

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