1

I have a spreadsheet that has broad groups in column A and then specific data points in column B (with more data throughout those specific rows). It looks very roughly like this:

  A            B            C           D           E        F
Group         Name        Weight      Gross        Net     Contribution
Equity                      25%        10%          8%       .25
              IBM           5%         15%         12%    
              AAPL          7%         23%         18%
Fixed Income                25%        5%           4%       .17
            10 Yr Bond      10%        7%           5%
Emerging Mrkts

I want my macro to scroll through column A until it finds a "Group" (e.g. Equity) and then have it spit out the name as well as some other data for each specific holding within every group.

I have started writing this macro, and have a procedure (that works) and a function. But I can't get the function to work. Can you take a look and tell me what you see wrong? Thanks

Here is my code:

Sub Demo1()
    Dim Ptr As Integer
    Ptr = 12
    ActiveSheet.Cells(1, Ptr).Select

    Dim Wb As Workbook
    Set Wb = ThisWorkbook
    Dim Wb2 As Workbook

    For Each Wb2 In Application.Workbooks
        Wb2.Activate
    Next
    Set Wb2 = ActiveWorkbook

    ' Set Wb = ActiveWorkbook
    Wb.Activate

    'Sheets("Attribution").Select
    ActiveSheet.Cells(Ptr, 2).Select
    Selection.Copy
    '  Sheets("F2 perf Chart").Range("E7") = ActiveSheet.Cells(12, 2).Value
    'Set Wb2 = ActiveWorkbook
    Wb2.Activate
    ActiveSheet.Cells(1, Ptr).Select
    Wb2.Sheets("F2 perf Chart").Range("E7").Select
    ActiveSheet.Paste

    Set Wb = ActiveWorkbook
    Range("M12").Copy
    Set Wb2 = ActiveWorkbook
    Wb2.Sheets("F2 perf Chart").Range("F7").Select
    ActiveSheet.Paste

    Set Wb = ActiveWorkbook
    Range("D12").Copy
    Set Wb2 = ActiveWorkbook
    Wb2.Sheets("F2 perf Chart").Range("G7").Select
    ActiveSheet.Paste
End Sub

And for the function:

Function NumBlankCells(Rownum As Integer) As Integer
    Dim RetVar As Integer
    Set RetVar = 0
    Dim rRng As Object
    rRng = Sheets("Attribution").Cells(Rownum + 1, 1)
    While IsEmpty(rRng.Value)
        RetVar = RetVar + 1
        rRng = Sheets("Attribution").Cells(Rownum + RetVar + 1, 1)
    Loop


    MsgBox RetVar

    NumBlankCells = RetVar


End Function

EDIT:

So Workbook 1 ("Attribution") is all this data that has been exported from a program. Workbook 2 ("F2 perf Chart") is a template that I use to self-populate a graph that is used in a quarterly report. An example of one of the data subsets I am trying to pull is the Name, the Weight, and then the Net Return("IBM, 5%, 12%") - and then I am pasting it into Workbook 2, in a different order ("IBM, 12%, 5%"). I am trying to get this to run through and copy-paste each data point under the Name column, and my function is an attempt to use the blank spaces in between each group to tell itself that it should skip these blank rows.

HP-12C
  • 11
  • 2
  • I created code for functionality similar to yours. If you find it helpful I can give you more details: [processing by groups](http://codereview.stackexchange.com/questions/93879/select-and-paste-rows-in-a-different-location/94046#94046) – paul bica Jul 08 '15 at 22:55
  • @paulbica Thanks! I did find it helpful, generally (I got lost in a few parts; this is my first time coding not in Java). Would it help you understand my situation more if I attached a screenshot of the workbooks I am using? Thanks again. – HP-12C Jul 09 '15 at 13:24
  • It would help to know more details about the data. The most effective (for me) is to know the source(s) with any exceptions you might have, and the expected result, then we can modify the other version quite easily – paul bica Jul 09 '15 at 13:29
  • `I can't get the function to work`. Please [edit] the question with more detail about what is not working: does it return an error, a bad value, inconsistent values, a picture of a spider? A summary of inputs/expected outputs/actual outputs is generally ideal. Also, if the function is the only problem, it would be better if the question only addresses that issue. – Byron Wall Jul 09 '15 at 14:23
  • @Byron The question has been updated at the bottom. My error is that right now, my Sub works perfectly for the first copy+paste portion, but then won't switch back to the first workbook for copying the rest of the data. It just stays on the destination workbook and copys from cells on the sheet, instead of from the original workbook. – HP-12C Jul 09 '15 at 14:53
  • Oh, that's much clearer. Since you are using `Range("D12")` by itself, you need to be sure that the `ActiveWorkbook` and `ActiveSheet` refer to the objects you expect. I am suspicious of your `Set wb2 = ActiveWorkbook` since that will erase whatever object was referenced before. Should that be `wb2.Activate` instead? Also, you might want to see http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros?s=2|3.4997 to avoid a lot of this `Select` `Activate` business. – Byron Wall Jul 09 '15 at 15:02
  • @Byron I have tried switching it to `wb2.Activate` but that doesn't seem to make a difference. Interestingly, my code works if I manually switch over to (use the mouse to click on) the original workbook anytime before the `Set wb2 = ActiveWorkbook` / `wb2.Activate` line. However, if I don't do that, it just selects `Cells(12,13)` on the destination workbook instead. I will also check out your link. – HP-12C Jul 09 '15 at 15:17

0 Answers0