Macro to move information to different cell

L

lazyluci

I have a .txt file that is submitted to me by a different source eac
month. When I open it in excel, I would like to run a macro to put th
information in the correct places. For example:

Name
Entity No.
License No.

This is how the information is given to me. However, I would like t
run a macro to move the information onto one row with 3 columns. If i
weren't for the fact that it is 39,000 rows, I could do it manually.

Can anybody help?

Thank you. I am attaching the file for your reference
 
F

Frank Kabel

Hi
try something like the following (assumption: sheet1 , col. A conatins
the data
sub move_it()
dim row_index as long
dim t_row as long
dim t_col as integer
dim source_wks as worksheet
dim target_wks as worksheet
application.screenupdating=false
set source_wks=worksheets("sheet1")
set target_wks=worksheets("sheet2")
for row_index=1 to 39000
t_row = CInt((row_index-1)/3)+1
t_col=((row_index-1) mod 3)+1
target_wks.cells(t_row,t_col).value = _
source_wks.cells(row_index,1).value
next
application.screenupdating=true
end sub
 
K

kkknie

You didn't attach the file, but I think I get your drift. I'll assum
that you basically get one large column that you want to split int
three. Something like:

Joe Schmo
45120
134567
Fred Rogers
36324
213424
etc.

Here's some code that will help. Paste it into a module and run i
from the Tools | Macros menu.

Code
-------------------
Sub Change1To3()

Dim i As Long
Dim iRow As Long

Application.ScreenUpdating = False
iRow = 1
For i = 1 To 65536 Step 3
If Range("A" & i).Value = "" Then Exit For 'Quit when a blank is found
Range("B" & iRow).Value = Range("A" & i).Value
Range("C" & iRow).Value = Range("A" & i + 1).Value
Range("D" & iRow).Value = Range("A" & i + 2).Value
iRow = iRow + 1
Next

End Su
 
Top