Complex copy & paste from pivot table data

[Chestnuttgirl98]

I need to figure out how to automatically look for a cell with data, copy it, and paste it in all the blank cells...until it gets to the next filled/occupied cell, wash rinse repeat.

I have a pivot table with column 1 = project name, column 2 = resource name, all the other columns are the hours per month that person spent on the project. I need to build on top of the macro I already have that selects the pivot table and copies and paste special the values into a new sheet. ***I'm using that data to run some calculations on other sheets*** It's labor intensive to come back and copy down all the project names in all the blank cells, and I hope there's a way to find the data, copy and paste into the sequential blank cells, and then copy the next data, and so on.

 

[XLfriend777]

Hi there Chestnuttgirl98. As nobody has responded this may help get things going. This is rather complicated so it may be helpful to break this down into steps. The first step - in the code below - is to look for a cell with data and copy it. Take a look and see if that makes sense. Hope this helps get you on the right track. Good luck and stay safe.

Sub FindCopy2()
Dim rng As Range
Dim copied As String
Dim cell As Variant

Set rng = Range("A1:A100")
For Each cell In rng
    If cell.Value = "" Then 'Skip if empty

    Else 'Copy first data found
copied = cell.Value 'Save cell value in the variable copied to paste later
Exit Sub
    End If

Next
End Sub

 

[Chestnuttgirl98]

Hi there Chestnuttgirl98. As nobody has responded this may help get things going. This is rather complicated so it may be helpful to break this down into steps. The first step - in the code below - is to look for a cell with data and copy it. Take a look and see if that makes sense. Hope this helps get you on the right track. Good luck and stay safe.

Sub FindCopy2()
Dim rng As Range
Dim copied As String
Dim cell As Variant

Set rng = Range("A1:A100")
For Each cell In rng
    If cell.Value = "" Then 'Skip if empty

    Else 'Copy first data found
copied = cell.Value 'Save cell value in the variable copied to paste later
Exit Sub
    End If

Next
End Sub

thank you, i'll try this out and see how it goes. stay tuned!!