Macro to move data

o2coolchris

New Member
Joined
Mar 23, 2007
Messages
11
I need help designing a macro that can take data that comes in looking like this...

A - B - C - D - E - F - G
123 - 5 e. main st. - Anytown - IL - 60635 - emp1 - john smith1
123 - 5 e. main st. - Anytown - IL - 60635 - emp2 - john smith2
123 - 5 e. main st. - Anytown - IL - 60635 - emp3 - john smith3
123 - 5 e. main st. - Anytown - IL - 60635 - emp4 - john smith4
123 - 5 e. main st. - Anytown - IL - 60635 - emp5 - john smith5
321 - 1 e. main st. - Anytown - IL - 60635 - emp1 - john smith6
321 - 1 e. main st. - Anytown - IL - 60635 - emp2 - john smith7
321 - 1 e. main st. - Anytown - IL - 60635 - emp3 - john smith8
321 - 1 e. main st. - Anytown - IL - 60635 - emp4 - john smith9
321 - 1 e. main st. - Anytown - IL - 60635 - emp5 - john smith0

and moves and filters it so that it looks like this...

H - I - J - K - L - M - N - O - P - Q
- - - - - emp1 - emp2 - emp3 - emp4 -emp5
123 - 5 e. main - town - IL - zip - smith1 - smith2 - smith3 - smith4 - smith5
321 - 1 e main - town - IL - zip - smith6 - smith7 - smith8 - smith9 - smith0


So in the initial column F, the employee #s 1-5 will be constant throughout every set that comes in. Each set is 5 rows, and the number of sets varies from day to day. So I need the macro to move the data from the first 5 rows, and then the next 5 rows after that, and the 5 after that one, etc... basically until there is no more data (upto the blank row)

I have a basic "Copy this, paste it transposed here and advanced filter this and paste it here" kind of macro, but as it stands now, it repeats the code 60 times so it can handle 60 sets of data. As you can imagine, this is very slow.

If you need to see that code, i can post it, but i'd rather start from scratch on this one.

Thank yoU!
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Does this completely over simplify?
Still going to take a while but perhaps not as long. Also I made an assumption that you don't necessarily want the data to continue in Column H through Q but I sent it to another sheet... Sheet2. Change Sheet names to suit.

Code:
Sub Test1()
Set Dst = Worksheets("Sheet2")
Set Src = Worksheets("Sheet1")

For x = 1 To Src.Cells(Rows.Count, "A").End(xlUp).Row Step 5
    Src.Range("A" & x & ":E" & x).Copy Dst.Range("A" & Dst.Cells(Rows.Count, "A").End(xlUp).Row + 1)
    Dst.Range("F" & Dst.Cells(Rows.Count, "A").End(xlUp).Row) = Src.Range("G" & x)
    Dst.Range("G" & Dst.Cells(Rows.Count, "A").End(xlUp).Row) = Src.Range("G" & x + 1)
    Dst.Range("H" & Dst.Cells(Rows.Count, "A").End(xlUp).Row) = Src.Range("G" & x + 2)
    Dst.Range("I" & Dst.Cells(Rows.Count, "A").End(xlUp).Row) = Src.Range("G" & x + 3)
    Dst.Range("J" & Dst.Cells(Rows.Count, "A").End(xlUp).Row) = Src.Range("G" & x + 4)
Next x
End Sub
Hope this is helpful.
 
Upvote 0
Actually I do need it on the same page, but I was able to edit your code to do just that.

Worked perfectly!!

Thank you so much!
 
Upvote 0

Forum statistics

Threads
1,223,431
Messages
6,172,073
Members
452,444
Latest member
ShaImran193

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top