Previous Topic Next Topic

Macro on a pivot

Closed
ELEE679 Posts 2 Registration date Tuesday July 30, 2013 Status Member Last seen July 31, 2013 - Jul 30, 2013 at 08:03 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - Aug 12, 2013 at 10:24 AM
Hi

I have been trying to find a macro that copies a cell and pastes into a number of cells beneath it until it reaches another cell that has data, copies that cell and pastes into empty cells beneath it etc in the same column.

I've copied and pasted special values of a pivot table and its the the label of the pivot item that i want to copy in the empty cells until the next pivot item etc.

Hope this explanation of my scenario makes sense.

Help if anyone can please :)

4 replies

Answer 1 / 4
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Jul 30, 2013 at 11:03 AM
Hi,

I could help you more specifically if you posted a link to download your file (www.speedyshare.com or ge.tt or ...).

Please show how it is now and how you would like it to be.

Best regards,
Trowa
0
ELEE679 Posts 2 Registration date Tuesday July 30, 2013 Status Member Last seen July 31, 2013
Jul 31, 2013 at 06:19 AM
Thanks TrowaD

Ive uploaded the template at the lnk below

http://speedy.sh/3WTDu/ELEE679.xlsx

Please let me know if you need anything else.

Thanks for the offer to help

Regards
ELEE679
0
Answer 2 / 4
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Aug 1, 2013 at 10:22 AM
Hi ELEE679,

Thanks for uploading your file, it made things a lot clearer.

Here is your code: 
Sub Test()
Dim mRow As Integer

mRow = 0

Do
    mRow = mRow + 5
    Range("A" & mRow).Copy
    Range("A" & mRow + 1).PasteSpecial
    Range("A" & mRow + 2).PasteSpecial
    Range("A" & mRow + 3).PasteSpecial
    Range("A" & mRow + 4).PasteSpecial
Loop Until Range("A" & mRow).Value = vbNullString

Application.CutCopyMode = False
End Sub

Best regards,
Trowa
0
Answer 3 / 4
ELEE
Aug 11, 2013 at 04:41 AM
Hi Trowa

Apologies for the late reply.

I finally managed to get a chance at work to test this and unfortnately it doesnt work.

I don't know what could be wrong.

No macro error messages appear - it just doesn't copy any cell at all to begin with so it pastes nothing.

Not sure what could be wrong.

Regards
0
Answer 4 / 4
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Aug 12, 2013 at 10:24 AM
Hi ELEE679,

Maybe you put the code in the wrong place.

Here is your file with code implemented:
http://wikisend.com/download/424390/ELEE679 - Macro on Pivot.xls

Best regards,
Trowa
0