0

I have a sheet of raw data with the average located at the bottom, I want to run through every column on a summary sheet, and place the average given on the raw data sheet below. what I have:

On Error Resume Next
Application.DisplayAlerts = False
Sheets.Add After:=Worksheets("Raw PivotTable")
ActiveSheet.Name = "Summary"
Application.DisplayAlerts = True
Set SSheet = Worksheets("Summary")
Set RSheet = Worksheets("Raw PivotTable")
Set PSheet = Worksheets("PivotTable")
Dim C As Integer
Dim PSheetVal As Long

C = 2

RSheet.Range("B1:" & ActiveSheet.Range(Cells(1, Columns.Count).End(xlToRight).Address).End(xlUp).Address).Copy
ActiveSheet.Paste Destination:=SSheet.Range("A1")

For Each cell In SSheet.Range("A1:" & SSheet.Range("A1").End(xlToRight).Address)

    PSheetVal = PSheet.Range(Cells(2, C)).End(xlDown).Value
    ' ^^^^ this is causing my issues specifically.

    cell.Offset(1, 0).Value = PSheetVal

    C = C + 1
    Next

For me it is putting 0s instead of the actual values.

Summary Sheet

Table

Tieron
  • 21
  • 3
  • 4
    Could you please elaborate with some screenshots/markdown sample data? Wheather or not this code would ever do what you want, you are using implicit sheet references to the `ActiveSheet` with each `Cells...`, so there is that to start with =) – JvdV Jan 24 '20 at 15:51
  • 3
    agreed. qualify all your variables to the parent object and even create explicit variables for all the objects. sheets, ranges, etc. code will be much easier to write and to follow in the future, should you need adjustments – Scott Holtzman Jan 24 '20 at 15:56
  • There, I did a bit of moving around with the code, the only issue is the for loop. I am now getting values in the summary but it's all 0s now, meaning the issue is with PSheetVal specifically :/ – Tieron Jan 24 '20 at 16:23
  • 1
    `PSheetVal = PSheet.Range(Cells(2, C)).End(xlDown).Value` is the same as `PSheetVal = PSheet.Range(ActiveSheet.Cells(2, C)).End(xlDown).Value` - if `PSheetVal` is **not** the active sheet, then this will give a "Run-time error '1004'" error – Chronocidal Jan 24 '20 at 16:37
  • Does this answer your question? [Why does Range work, but not Cells?](https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells) – BigBen Jan 24 '20 at 17:19
  • No, unfortunately everything suggested in that post still give me the same results @BigBen – Tieron Jan 24 '20 at 18:58
  • 1
    So you actually qualified the worksheet for each `Cells` call? – BigBen Jan 24 '20 at 19:00
  • I used a with call for each statement which should effectively be the same thing I imagine. – Tieron Jan 24 '20 at 19:19
  • @Tieron Not quite: `With Sheet1: MsgBox(Range("A1")): End With` will output cell A1 of the *ActiveSheet*, but `With Sheet1: MsgBox(.Range("A1")): End With` will output cell A1 of `Sheet1` - that `.` before `Range` or `Cells` is **very** important! – Chronocidal Jan 27 '20 at 09:12

2 Answers2

0

Instead of the copy-paste, you might do the following:

cell. ... .Offset(1, 0).Value = PSheet. ... .End(xlDown).Value
Dominique
  • 16,450
  • 15
  • 56
  • 112
  • @BigBen: you're right, I just copied it from the question, but indeed, it makes no sense. I adapted the answer accordingly. – Dominique Jan 24 '20 at 16:11
  • 1
    Took it into consideration and ya, cell is referencing a specific cell in SSheet anyways, don't need SSheet. – Tieron Jan 24 '20 at 16:26
-1

I fixed the issue! Instead of trying to find an exact cell I selected the entire column, and selected the last cell. My code:

 For Each cell In SSheet.Range("A1:" & SSheet.Range("A1").End(xlToRight).Address)

        With Sheets(PSheet)
        PSheetVal = PSheet.Columns(C).End(xlDown).Value
        End With

        With Sheets(SSheet)
        cell.Offset(1, 0).Value = PSheetVal
        End With

        C = C + 1
        Next

Still not quite sure how to select a very specific cell but for my purposes that is not needed, would be helpful if anyone could point me in the right direction for the future!

Tieron
  • 21
  • 3