Solved

Creating a script and need to generate from 4 columns in Excel

Posted on 2011-03-07
14
190 Views
Last Modified: 2012-05-11
I have 4 columns of data and would like to convert them on the next sheet to row 1,2,3,4..

line1   |  line 2|  line3  | line4  

would like to make it....

line1
line2
line3
line4
0
Comment
Question by:BlakeISS
  • 7
  • 6
14 Comments
 
LVL 10

Expert Comment

by:Makrini
Comment Utility
Sub Macro1()

    Sheets("Sheet1").Range("A1:D1").Copy
    Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Application.CutCopyMode = False
End Sub

Open in new window

0
 

Author Comment

by:BlakeISS
Comment Utility
When I run this macro, it does only the first record. How do I make it run for all the records?
1:
2:
3:
4:
5:
6:
7:
 Sub Macro1()

    Sheets("Sheet1").Range("A1:D1").Copy
    Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Application.CutCopyMode = False
End Sub

Open in new window

0
 
LVL 10

Expert Comment

by:Makrini
Comment Utility
Where is your data, and where you you want it?

is it

row 1 - 1,2,3,4
row 2 - 5,6,7,8

and going to
1,5
2,6
3,7
4,8

or
1
2
3
4
5
6
7
8
0
 
LVL 10

Expert Comment

by:Makrini
Comment Utility
Sub Macro1()  
 Lastrow = Sheets("Sheet1").range("A" & rows.count).end(xlup).row
for i = 0 to lastrow - 1
    Sheets("Sheet1").Range("A1:D1").offset(i,0).Copy  
    Sheets("Sheet2").Range("A1").offset(0,i).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _  
        False, Transpose:=True  
    Application.CutCopyMode = False
next i
End Sub

Open in new window

0
 

Author Comment

by:BlakeISS
Comment Utility
in sheet1
columns are  colunm1: are link special mailboxes desktop t2test14 mailbox column2 put properties "mailboxes:mailbox" 1016 6 0 1321 6 0      column3rename "mailboxes:mailbox" "t2test14s mailbox"       column4 ###


I would like to output to sheet2 to be
row1
row2
row3
row4

link special mailboxes desktop t2test14 mailbox
put properties "mailboxes:mailbox" 1016 6 0 1321 6 0
rename "mailboxes:mailbox" "t2test14s mailbox"
###
0
 

Author Comment

by:BlakeISS
Comment Utility
I have 1400 records to do this with...
0
 
LVL 10

Expert Comment

by:Makrini
Comment Utility
Maybe if you provide an example workbook showing from data on one sheet and to data on second sheet
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:BlakeISS
Comment Utility
Here is a sample
Book1.xls
0
 
LVL 10

Expert Comment

by:Makrini
Comment Utility
In sheet 2 do you want the next row of data into column B?
0
 
LVL 10

Assisted Solution

by:Makrini
Makrini earned 100 total points
Comment Utility
If so...

Sub Macro1()
 lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
For i = 0 To lastrow - 1
    Sheets("Sheet1").Range("A1:D1").Offset(i, 0).Copy
    Sheets("Sheet2").Range("A1").Offset(0, i).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Application.CutCopyMode = False
Next i
End Sub

Open in new window

0
 

Author Comment

by:BlakeISS
Comment Utility
Ok this macro created them horizontally and did not put them into a list.
It did however, try to do the entire list.

1:
2:
3:
4:
5:
6:
7:
8:
9:
 Sub Macro1()
 lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
For i = 0 To lastrow - 1
    Sheets("Sheet1").Range("A1:D1").Offset(i, 0).Copy
    Sheets("Sheet2").Range("A1").Offset(0, i).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Application.CutCopyMode = False
Next i
End Sub

Open in new window

0
 

Author Comment

by:BlakeISS
Comment Utility
0
 

Author Comment

by:BlakeISS
Comment Utility
0
 
LVL 1

Accepted Solution

by:
benyami earned 400 total points
Comment Utility
It looks like your end result will be a text based script file. If each column always starts with the same phrase, you could do the following:

Copy and paste the data into your favorite text editor.
Search and replace every time "put" or "link" appears with a newline character preceding "put" or "link"

Example:
FIND:
   put properties
REPLACE:
                             <---{insert newline character or copy and paste a line return}
   put properties

For each row in your spreadsheet, this will turn:
link special mailboxes desktop t2test14 mailbox      put properties "mailboxes:mailbox" 1016 6 0 1321 6 0      rename "mailboxes:mailbox" "t2test14s mailbox"      ###

into:
link special mailboxes desktop t2test14 mailbox      
put properties "mailboxes:mailbox" 1016 6 0 1321 6 0      
rename "mailboxes:mailbox" "t2test14s mailbox"      
###
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Outlook Free & Paid Tools
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now