[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2011-03-07
14
Medium Priority
?
203 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
14 Comments
 
LVL 10

Expert Comment

by:Makrini
ID: 35063461
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
ID: 35064060
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
ID: 35064073
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 10

Expert Comment

by:Makrini
ID: 35064113
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
ID: 35064115
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
ID: 35064116
I have 1400 records to do this with...
0
 
LVL 10

Expert Comment

by:Makrini
ID: 35064127
Maybe if you provide an example workbook showing from data on one sheet and to data on second sheet
0
 

Author Comment

by:BlakeISS
ID: 35064158
Here is a sample
Book1.xls
0
 
LVL 10

Expert Comment

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

Assisted Solution

by:Makrini
Makrini earned 400 total points
ID: 35064261
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
ID: 35068551
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
ID: 35068644
0
 

Author Comment

by:BlakeISS
ID: 35068654
0
 
LVL 1

Accepted Solution

by:
benyami earned 1600 total points
ID: 35071069
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

650 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