StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMicrosoft Excel 2010 Copy/Paste Edit Ideas & Help Request On Minor Detail
text
Body
copied!<p>Yes, I have written this Macro for Microsoft Excel (2010 if that helps), with the assistance of someone else. I am wondering if anyone has a way of shortening it, and making it more efficient. However still getting the same outcome it gave before? An example of the CSV format I have to work with can be found here... And yes, unfortunately, they do have to be put into those columns and cells..</p> <p>The only issue I am really stumped on is this: on <code>.Cell(2, 3)</code> for example... If you notice, on each section it would copy and paste, it has a new row.. I want it to do that.. I am novice at this, and could not find a way to make it just paste each one to the next available row.. So my solution for this was to do 2, 3, 4, 5.. And so on.. If anyone knows how to change this as well to make this.. Loop? Per say, that would be great help. Just loop for the amount of data that is being copied though, and not repeat. </p> <p>Here is an example CSV: <a href="http://www.mediafire.com/?x5fg4ln4568h7pz" rel="nofollow">Media Fire</a> It's Clean, I promise. Thank you for your time.</p> <p>Macro Code to copy certain Column/Row data Cells from one sheet to another to specific Cells </p> <pre><code>Sub FormatData() Dim col As Integer For col = 1 To 1 With Worksheets(2) .Cells(2, 2) = Cells(1, col) .Cells(2, 3) = Cells(2, col) & ". " & Cells(3, col) & ". " & Cells(4, col) & ". " & Cells(5, col) & "." .Cells(2, 4) = Cells(7, col) .Cells(2, 5) = Cells(10, col) End With Next col For col = 2 To 2 With Worksheets(2) .Cells(3, 2) = Cells(1, col) .Cells(3, 3) = Cells(2, col) & ". " & Cells(3, col) & ". " & Cells(4, col) & ". " & Cells(5, col) & "." .Cells(3, 4) = Cells(7, col) .Cells(3, 5) = Cells(10, col) End With Next col For col = 3 To 3 With Worksheets(2) .Cells(4, 2) = Cells(1, col) .Cells(4, 3) = Cells(2, col) & ". " & Cells(3, col) & ". " & Cells(4, col) & ". " & Cells(5, col) & "." .Cells(4, 4) = Cells(7, col) .Cells(4, 5) = Cells(10, col) End With Next col For col = 4 To 4 With Worksheets(2) .Cells(5, 2) = Cells(1, col) .Cells(5, 3) = Cells(2, col) & ". " & Cells(3, col) & ". " & Cells(4, col) & ". " & Cells(5, col) & "." .Cells(5, 4) = Cells(7, col) .Cells(5, 5) = Cells(10, col) End With Next col For col = 1 To 1 With Worksheets(2) .Cells(6, 2) = Cells(13, col) .Cells(6, 3) = Cells(14, col) & ". " & Cells(15, col) & ". " & Cells(16, col) & ". " & Cells(17, col) & "." .Cells(6, 4) = Cells(19, col) .Cells(6, 5) = Cells(22, col) End With Next col For col = 2 To 2 With Worksheets(2) .Cells(7, 2) = Cells(13, col) .Cells(7, 3) = Cells(14, col) & ". " & Cells(15, col) & ". " & Cells(16, col) & ". " & Cells(17, col) & "." .Cells(7, 4) = Cells(19, col) .Cells(7, 5) = Cells(22, col) End With Next col For col = 3 To 3 With Worksheets(2) .Cells(8, 2) = Cells(13, col) .Cells(8, 3) = Cells(14, col) & ". " & Cells(15, col) & ". " & Cells(16, col) & ". " & Cells(17, col) & "." .Cells(8, 4) = Cells(19, col) .Cells(8, 5) = Cells(22, col) End With Next col For col = 4 To 4 With Worksheets(2) .Cells(9, 2) = Cells(13, col) .Cells(9, 3) = Cells(14, col) & ". " & Cells(15, col) & ". " & Cells(16, col) & ". " & Cells(17, col) & "." .Cells(9, 4) = Cells(19, col) .Cells(9, 5) = Cells(22, col) End With Next col For col = 1 To 1 With Worksheets(2) .Cells(10, 2) = Cells(25, col) .Cells(10, 3) = Cells(26, col) & ". " & Cells(27, col) & ". " & Cells(28, col) & ". " & Cells(29, col) & "." .Cells(10, 4) = Cells(31, col) .Cells(10, 5) = Cells(34, col) End With Next col For col = 2 To 2 With Worksheets(2) .Cells(11, 2) = Cells(25, col) .Cells(11, 3) = Cells(26, col) & ". " & Cells(27, col) & ". " & Cells(28, col) & ". " & Cells(29, col) & "." .Cells(11, 4) = Cells(31, col) .Cells(11, 5) = Cells(34, col) End With Next col For col = 3 To 3 With Worksheets(2) .Cells(12, 2) = Cells(25, col) .Cells(12, 3) = Cells(26, col) & ". " & Cells(27, col) & ". " & Cells(28, col) & ". " & Cells(29, col) & "." .Cells(12, 4) = Cells(31, col) .Cells(12, 5) = Cells(34, col) End With Next col For col = 4 To 4 With Worksheets(2) .Cells(13, 2) = Cells(25, col) .Cells(13, 3) = Cells(26, col) & ". " & Cells(27, col) & ". " & Cells(28, col) & ". " & Cells(29, col) & "." .Cells(13, 4) = Cells(31, col) .Cells(13, 5) = Cells(34, col) End With Next col For col = 1 To 1 With Worksheets(2) .Cells(14, 2) = Cells(37, col) .Cells(14, 3) = Cells(38, col) & ". " & Cells(39, col) & ". " & Cells(40, col) & ". " & Cells(41, col) & "." .Cells(14, 4) = Cells(43, col) .Cells(14, 5) = Cells(46, col) End With Next col For col = 2 To 2 With Worksheets(2) .Cells(15, 2) = Cells(37, col) .Cells(15, 3) = Cells(38, col) & ". " & Cells(39, col) & ". " & Cells(40, col) & ". " & Cells(41, col) & "." .Cells(15, 4) = Cells(43, col) .Cells(15, 5) = Cells(46, col) End With Next col For col = 3 To 3 With Worksheets(2) .Cells(16, 2) = Cells(37, col) .Cells(16, 3) = Cells(38, col) & ". " & Cells(39, col) & ". " & Cells(40, col) & ". " & Cells(41, col) & "." .Cells(16, 4) = Cells(43, col) .Cells(16, 5) = Cells(46, col) End With Next col For col = 4 To 4 With Worksheets(2) .Cells(17, 2) = Cells(37, col) .Cells(17, 3) = Cells(38, col) & ". " & Cells(39, col) & ". " & Cells(40, col) & ". " & Cells(41, col) & "." .Cells(17, 4) = Cells(43, col) .Cells(17, 5) = Cells(46, col) End With Next col For col = 1 To 1 With Worksheets(2) .Cells(18, 2) = Cells(49, col) .Cells(18, 3) = Cells(50, col) & ". " & Cells(51, col) & ". " & Cells(52, col) & ". " & Cells(53, col) & "." .Cells(18, 4) = Cells(55, col) .Cells(18, 5) = Cells(58, col) End With Next col For col = 2 To 2 With Worksheets(2) .Cells(19, 2) = Cells(49, col) .Cells(19, 3) = Cells(50, col) & ". " & Cells(51, col) & ". " & Cells(52, col) & ". " & Cells(53, col) & "." .Cells(19, 4) = Cells(55, col) .Cells(19, 5) = Cells(58, col) End With Next col For col = 3 To 3 With Worksheets(2) .Cells(20, 2) = Cells(49, col) .Cells(20, 3) = Cells(50, col) & ". " & Cells(51, col) & ". " & Cells(52, col) & ". " & Cells(53, col) & "." .Cells(20, 4) = Cells(55, col) .Cells(20, 5) = Cells(58, col) End With Next col For col = 4 To 4 With Worksheets(2) .Cells(21, 2) = Cells(49, col) .Cells(21, 3) = Cells(50, col) & ". " & Cells(51, col) & ". " & Cells(52, col) & ". " & Cells(53, col) & "." .Cells(21, 4) = Cells(55, col) .Cells(21, 5) = Cells(58, col) End With Next col For col = 1 To 1 With Worksheets(2) .Cells(22, 2) = Cells(61, col) .Cells(22, 3) = Cells(62, col) & ". " & Cells(63, col) & ". " & Cells(64, col) & ". " & Cells(65, col) & "." .Cells(22, 4) = Cells(67, col) .Cells(22, 5) = Cells(70, col) End With Next col For col = 2 To 2 With Worksheets(2) .Cells(23, 2) = Cells(61, col) .Cells(23, 3) = Cells(62, col) & ". " & Cells(63, col) & ". " & Cells(64, col) & ". " & Cells(65, col) & "." .Cells(23, 4) = Cells(67, col) .Cells(23, 5) = Cells(70, col) End With Next col For col = 3 To 3 With Worksheets(2) .Cells(24, 2) = Cells(61, col) .Cells(24, 3) = Cells(62, col) & ". " & Cells(63, col) & ". " & Cells(64, col) & ". " & Cells(65, col) & "." .Cells(24, 4) = Cells(67, col) .Cells(24, 5) = Cells(70, col) End With Next col For col = 4 To 4 With Worksheets(2) .Cells(25, 2) = Cells(61, col) .Cells(25, 3) = Cells(62, col) & ". " & Cells(63, col) & ". " & Cells(64, col) & ". " & Cells(65, col) & "." .Cells(25, 4) = Cells(67, col) .Cells(25, 5) = Cells(70, col) End With Next col For col = 1 To 1 With Worksheets(2) .Cells(26, 2) = Cells(73, col) .Cells(26, 3) = Cells(74, col) & ". " & Cells(75, col) & ". " & Cells(76, col) & ". " & Cells(77, col) & "." .Cells(26, 4) = Cells(79, col) .Cells(26, 5) = Cells(82, col) End With Next col For col = 2 To 2 With Worksheets(2) .Cells(27, 2) = Cells(73, col) .Cells(27, 3) = Cells(74, col) & ". " & Cells(75, col) & ". " & Cells(76, col) & ". " & Cells(77, col) & "." .Cells(27, 4) = Cells(79, col) .Cells(27, 5) = Cells(82, col) End With Next col For col = 3 To 3 With Worksheets(2) .Cells(28, 2) = Cells(73, col) .Cells(28, 3) = Cells(74, col) & ". " & Cells(75, col) & ". " & Cells(76, col) & ". " & Cells(77, col) & "." .Cells(28, 4) = Cells(79, col) .Cells(28, 5) = Cells(82, col) End With Next col For col = 4 To 4 With Worksheets(2) .Cells(29, 2) = Cells(73, col) .Cells(29, 3) = Cells(74, col) & ". " & Cells(75, col) & ". " & Cells(76, col) & ". " & Cells(77, col) & "." .Cells(29, 4) = Cells(79, col) .Cells(29, 5) = Cells(82, col) End With Next col For col = 1 To 1 With Worksheets(2) .Cells(30, 2) = Cells(85, col) .Cells(30, 3) = Cells(86, col) & ". " & Cells(87, col) & ". " & Cells(88, col) & ". " & Cells(89, col) & "." .Cells(30, 4) = Cells(91, col) .Cells(30, 5) = Cells(94, col) End With Next col For col = 2 To 2 With Worksheets(2) .Cells(31, 2) = Cells(85, col) .Cells(31, 3) = Cells(86, col) & ". " & Cells(87, col) & ". " & Cells(88, col) & ". " & Cells(89, col) & "." .Cells(31, 4) = Cells(91, col) .Cells(31, 5) = Cells(94, col) End With Next col For col = 3 To 3 With Worksheets(2) .Cells(32, 2) = Cells(85, col) .Cells(32, 3) = Cells(86, col) & ". " & Cells(87, col) & ". " & Cells(88, col) & ". " & Cells(89, col) & "." .Cells(32, 4) = Cells(91, col) .Cells(32, 5) = Cells(94, col) End With Next col For col = 4 To 4 With Worksheets(2) .Cells(33, 2) = Cells(85, col) .Cells(33, 3) = Cells(86, col) & ". " & Cells(87, col) & ". " & Cells(88, col) & ". " & Cells(89, col) & "." .Cells(33, 4) = Cells(91, col) .Cells(33, 5) = Cells(94, col) End With Next col End Sub </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload