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]
 
Sort by date Sort by votes
Sounds like you have too much data. Add this message box as shown, what does it say
Code: 
  Next
 [COLOR=#ff0000] MsgBox UBound(b)[/COLOR]
  sh2.Range("A2").Resize(UBound(b), 2).Value = b()
 
Upvote 0

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Hi Dante

Thank again,this macro does work (even a funny way!) it gives me an error "Method 'Range' of object'_Worksheet'failed but in the Output sheet I have all the info I need :)

Not sure if it need a tweak but it is working so thank you!
 
Upvote 0
yazzy10 said:
Hi Dante

Thank again,this macro does work (even a funny way!) it gives me an error "Method 'Range' of object'_Worksheet'failed but in the Output sheet I have all the info I need :)

Not sure if it need a tweak but it is working so thank you!
Click to expand...

Hi @yazzy10, I am not sure what the message may mean.But if your data is good and complete, then it was a pleasure to help you.:cool:


Thanks for the feedback.
 
Upvote 0
yazzy10 said:
Thanks fr your input- adding the ms box gives 127944-what does this means?
Click to expand...
It means that there are 127,944 rows of data to be output to sheet, which is fine.
One possible reason for the error you originally got, was if you were trying to output more than 1,048,576 rows.
 
Upvote 0
The following could give us a more accurate result of the final number of rows:

Try this please.
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
[COLOR=#0000ff]  n = WorksheetFunction.CountA(sh1.Range("D2", sh1.Cells(lr, lc)))[/COLOR]
  ReDim b(1 To [COLOR=#0000ff]n[/COLOR], 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()
  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
 
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
287
Abdo
A
A
  • Question Question
Multiple Account Drawdown
Replies
0
Views
766
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,157
Messages
6,170,419
Members
452,325
Latest member
BlahQz

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