Excel Macro Help needed

[mustang2010]

I am trying to create a macro that selects 7 contiguous rows in the same column and then select copy then move the cursor to the next row below execute another macro for that cell and then start this first macro down the column.
I need this to work throughout the same column say Column A and have the macro work anywhere in Column A. Decription Example below:

A1 Select cell
A2 Select cell
A3 Select cell
A4 Select cell
A5 Select cell
A6 Select cell
A7 Select cell Copy the selected range of cells
A8 Move cursor here and run a paste special transpose macro already created
A9move cursor 2 rows and start original macro on next 7 rows
A10 Macro 1 start

Thanks!

 

[VoG]

What is the objective? Selecting cells is unnecessary and slows your code down. What is the code for the PasteSpecial macro?

 

[mustang2010]

I have hundreds of Addresses 7 rows of data in a column separated by 2 blank rows I need to transpose to a single line of data for each address so I can clean up the format to create a proper list after this process.

the transpose code is :

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+t
'

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End Sub

Thanks!

 

[VoG]

Try this with a copy of your sheet

Sub test()
Dim LR As Long, i As Long, j As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR - 6 Step 9
    j = j + 1
    Range("B" & j).Resize(, 9).Value = Application.Transpose(Range("A" & i).Resize(9))
Next i
Columns("A").Delete
Columns("A:G").AutoFit
End Sub

 

[mustang2010]

The wont work. I just need a simple command to select seven rows including the active cell copy it, move down one row run macro 2 move down one row and loop the needed macro to select 7 cells.

 

[VoG]

The wont work.

Why is that? Did you even try it?

 

[mustang2010]

Yes I did , Its not exactly what I need on each row after the address I am paste transposing the data from the seven lines above to the eighth line. I need to preserve the original data line to be sorted out after this is mastered.

Thanks!

 

[VoG]

What do you expect the result to be. Use Excel Jeanie and only a few rows needed http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

 

[mustang2010]

<title>Excel Jeanie HTML</title>Sheet1

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Calibri,Arial; font-size: 11pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 290px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td> </td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td> <td>G</td> <td>H</td> <td>I</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td> <td style="font-weight: bold;">Univest Corporation – Skippack Branch</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td>Phone: (610) 222-3923</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td>Address: 4285 Township Line Road</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td style="color: rgb(0, 0, 255); text-decoration: underline;">Schwenksville, Montgomery Twp, Pennsylvania, 19473</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td style="color: rgb(0, 0, 255); text-decoration: underline;">Email: mehlc@univest.net</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td>Contact person: Carrie Mehl</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td>Website:www.univest.com</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">8</td> <td style="font-weight: bold;">Univest Corporation – Skippack Branch</td> <td>Phone: (610) 222-3923</td> <td>Address: 4285 Township Line Road</td> <td style="color: rgb(0, 0, 255); text-decoration: underline;">Schwenksville, Montgomery Twp, Pennsylvania, 19473</td> <td style="color: rgb(0, 0, 255); text-decoration: underline;">Email: mehlc@univest.net</td> <td>Contact person: Carrie Mehl</td> <td>Website:www.univest.com</td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">9</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">10</td> <td style="font-weight: bold;">Univest Corporation – Skippack Branch</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">11</td> <td>Phone: (610) 222-3923</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">12</td> <td>Address: 4285 Township Line Road</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">13</td> <td style="color: rgb(0, 0, 255); text-decoration: underline;">Schwenksville, Montgomery Twp, Pennsylvania, 19473</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">14</td> <td style="color: rgb(0, 0, 255); text-decoration: underline;">Email: mehlc@univest.net</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">15</td> <td>Contact person: Carrie Mehl</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">16</td> <td style="font-weight: bold;">Website:www.univest.com</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">17</td> <td style="font-weight: bold;"> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">18</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">19</td> <td style="font-weight: bold;">Univest Corporation – Skippack Branch</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">20</td> <td>Phone: (610) 222-3923</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">21</td> <td>Address: 4285 Township Line Road</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">22</td> <td style="color: rgb(0, 0, 255); text-decoration: underline;">Schwenksville, Montgomery Twp, Pennsylvania, 19473</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">23</td> <td style="color: rgb(0, 0, 255); text-decoration: underline;">Email: mehlc@univest.net</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">24</td> <td>Contact person: Carrie Mehl</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">25</td> <td style="font-weight: bold;">Website:www.univest.com</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4

 

[VoG]

Before the macro

Excel Workbook
	A
1	Univest Corporation Skippack Branch
2	Phone: (610) 222-3923
3	Address: 4285 Township Line Road
4	Schwenksville, Montgomery Twp, Pennsylvania, 19473
5	Email: mehlc@univest.net
6	Contact person: Carrie Mehl
7	Website:www.univest.com
8
9
10	Univest Corporation Skippack Branch
11	Phone: (610) 222-3923
12	Address: 4285 Township Line Road
13	Schwenksville, Montgomery Twp, Pennsylvania, 19473
14	Email: mehlc@univest.net
15	Contact person: Carrie Mehl
16	Website:www.univest.com
17
18
19	Univest Corporation Skippack Branch
20	Phone: (610) 222-3923
21	Address: 4285 Township Line Road
22	Schwenksville, Montgomery Twp, Pennsylvania, 19473
23	Email: mehlc@univest.net
24	Contact person: Carrie Mehl
25	Website:www.univest.com
Sheet1

After

Excel Workbook
	A	B	C	D	E	F	G
1	Univest Corporation Skippack Branch	Phone: (610) 222-3923	Address: 4285 Township Line Road	Schwenksville, Montgomery Twp, Pennsylvania, 19473	Email: mehlc@univest.net	Contact person: Carrie Mehl	Website:www.univest.com
2	Univest Corporation Skippack Branch	Phone: (610) 222-3923	Address: 4285 Township Line Road	Schwenksville, Montgomery Twp, Pennsylvania, 19473	Email: mehlc@univest.net	Contact person: Carrie Mehl	Website:www.univest.com
3	Univest Corporation Skippack Branch	Phone: (610) 222-3923	Address: 4285 Township Line Road	Schwenksville, Montgomery Twp, Pennsylvania, 19473	Email: mehlc@univest.net	Contact person: Carrie Mehl	Website:www.univest.com
Sheet1