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
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
 - 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
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
 509
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
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
 509
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
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
 509
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