VBA question (selecting portions of a spreadsheet based on the first column)

[Balmung]

Hi all,

I'm very new here and with VBA in general, so I need some assistance.

What I'm trying to do is take a spreadsheet with headings (not on row 1) that are also divided into types (meaning merged cells in column A that have basic headings as well, such as Cancel, Endorse, etc) and manipulate it so that I can maneuver each section into its own sheet and do counts of certain criteria in specific columns that I can have put into another workbook entirely in a table. Making the separate tabs may not be necessary, but it was my first idea. I was trying to think of some way to basically make it go "if this value is in Column A, select the section belonging to that word, copy/paste it over, and do the calculations".

Hopefully that made some semblance of sense. Anyone have any suggestions?

 

[steve the fish]

This help at all?

Sub Copy()

Dim myFind As String
Dim myRng As Range
   
myFind = Worksheets("Sheet1").Range("A2").Value

With Worksheets("Sheet1").Range("2:2")
    Set myRng = .Find(What:=myFind, After:=.Cells(.Cells.Count), LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
            
    If Not myRng Is Nothing Then
        Application.Goto myRng, True
    Else: End If

End With

Worksheets("Sheet1").Range(ActiveCell, ActiveCell.End(xlDown)).Copy _
Destination:=Worksheets("Sheet2").Range("A2")
    
End Sub

 

[Balmung]

Yes, it does! It's not a complete solution but that's definitely getting it on the right track, I appreciate it! What it's doing is nearly what I need it to do, although it's not selecting the entire range of data that I need, and I'm not entirely sure how to change the Worksheets.Range part in order to include the entire range I need since it changes every time this report is generated. For example, starting on cell A5 is the Cancel heading (it's for the left headers, not the top) and it goes down to cell A169 as a merged cell, then switches to another heading called Endorse. When I run the code after changing some of the names of the tabs, it will grab the Cancel header and paste it onto the next tab as well as the first line of Endorse. Do you have any other advice? I do sincerely appreciate what you've already given!

 

[Balmung]

Oh and I should probably have mentioned this before (sorry about that) that going across the columns from the Cancel cell could have blanks, but I would still need the program to highlight that entire row then go down to the end of the section. I did find out that if I highlight row 5 where Cancel starts and do Control+Shift+Down, it highlights the section I need without going into the next section, which is Endorse. This only works if the cells in column A are merged.

 

[Balmung]

I've changed the code around a bit to edit what I needed, but I need some way to set the range to whatever it would be doing the Control + Shift + Down function once the Cancel row is highlighted instead of doing a set range. Can anyone assist?

Sub Test1()

Dim myFind As String
Dim myRng As Range

myFind = Worksheets("RTW Policy Exchange Detail.rdl").Range("A5").Value

With Worksheets("RTW Policy Exchange Detail.rdl").Range("5:169")
    Set myRng = .Find(What:=myFind, After:=.Cells(.Cells.Count), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
            
    
            
    If Not myRng Is Nothing Then
        Application.Goto myRng, True
    Else: End If
    
    ' ActiveCell.Offset(1, 0).Range("A5").Select
    Range(Selection, "O" & Selection.Row).Select

End With

Worksheets("RTW Policy Exchange Detail.rdl").Range("5:169").Copy _
Destination:=Worksheets("Sheet1").Range("A2")

With Worksheets("Sheet1")
    Worksheets("Sheet1").Columns("N:O").ColumnWidth = 50
    Worksheets("Sheet1").Columns("H:L").ColumnWidth = 10
    Worksheets("Sheet1").Rows("1:500").RowHeight = 12.75
End With

End Sub

 

[DocAElstein]

Hi “Balmung”,
. I am still a bit new to Excel, but playing around with sorting/ Moving ranges is one thing I am practicing.
. I might be able to help. Or someone else.
. In any case it might not be immediately clear at first glance to someone wanting to help exactly wot you want.

. As always, “A (Good!) Picture paints a thousand words”

. . Can you Try to provide Tables that can be copied into a spreadsheet showing example data but also importantly exactly how the final output should look like in the Excel File based on your actual example data.


. There are various ways to do this. The first is preferred by this Forum for excel files as then everyone can see wot is going on quickly.. The Third method I prefer. - Then one can get on straight away with writing a code for you in the file you provide.

. 1 If you can, try uploading this, https://onedrive.live.com/?cid=8cffd...CE27E813%21189 instructions here MrExcel HTML Maker . This free Excel add-In is good for screen shots here of spreadsheets. Then everyone can quickly see what is going on and follow the Thread easily.
Or
. 2 Up left in the Thread editor is a table icon. Click that, create an appropriately sized table and fill it in. (To get this icon up in the Reply window you may need to click on the “Go Advanced” Button next to the Reply Button)
Or
. 3 Supply us with example Excel files (Can of course be shortened, or made - up data in case any info is sensitive)
. For example send over these 2 free things: FileSnack | Easy file sharing or Box Net,
Remember to select Share after uploading and give us the link they provide.

Alan.