Help with macro?

[bkuu]

slowly learning a bunch of cool formulas and I've created a bunch of templates with Marcos but this particular project got me stumped and I have no idea where to start. I simply have data that I can copy/paste into sheet1 as pictured and want the end result to auto generate sheet2 with Count and Group column auto expanding and merging if there is new data in sheet1 but starting a new group if the timeslot changes. This something difficult to do? I was looking into Vlookup but this seems like a macro thing.

 

[Joe4]

Welcome to the Board!

Just trying to understand your example.
Why did Sally's 9:50 AM record change from Present "No" to "Yes" between sheets 1 and 2?

 

[bkuu]

Welcome to the Board!

Just trying to understand your example.
Why did Sally's 9:50 AM record change from Present "No" to "Yes" between sheets 1 and 2?

hello. Thank you

sorry that was a mistake , the data is the same as sheet 1. I'm just trying to automatically format it to look like sheet2. So I copy and paste into sheet1 and the data changes say there are now more people in 9:50 AM time , it would reflect that in sheet2 and look exactly like that without me having to manually put the Count and Group. Hope that makes sense

 

[Joe4]

OK, making the following assumptions:
- your data begins in cell A1
- you do not have anymore than 26 groups (if you do, you need to tell us what the next group should be after "Z")
This code should do that you want:

Sub MyMacro()

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim lr As Long
    Dim r As Long
    Dim r1 As Long
    Dim r2 As Long
    Dim a As Long
    Dim m As Long
   
    Application.ScreenUpdating = False
   
'   Set worksheets
    Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")

'   Copy data from first sheet to second
    ws1.Cells.Copy
    ws2.Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
   
'   Find last row with data on second sheet
    lr = ws2.Cells(Rows.Count, "A").End(xlUp).Row
   
'   Insert columns A and C and add titles
    Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1") = "Count"
    Range("C1") = "Group"
   
'   Set initial value of row and ASCII code character for group
    r = 2
    a = 65
   
'   Loop through rows
    ws2.Activate
    Do
'       Exit once past row
        If r > lr Then Exit Do
'       Put formula in column A and C
        Cells(r, "A").FormulaR1C1 = "=COUNTIF(C[3],RC[3])"
        Cells(r, "C").Formula = "=CHAR(" & a & ")"
'       Merge cells
        m = Cells(r, "A").Value
        If m > 1 Then
            Range(Cells(r, "A"), Cells(r + m - 1, "A")).Merge
            Range(Cells(r, "C"), Cells(r + m - 1, "C")).Merge
        End If
'       Move to next section and increment group
        r = r + m
        a = a + 1
    Loop
   
'   Center columns A and C
    With Columns("A:A")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
    With Columns("C:C")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
   
    Application.ScreenUpdating = True
   
End Sub

 

[bkuu]

OK, making the following assumptions:
- your data begins in cell A1
- you do not have anymore than 26 groups (if you do, you need to tell us what the next group should be after "Z")
This code should do that you want:

Sub MyMacro()

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim lr As Long
    Dim r As Long
    Dim r1 As Long
    Dim r2 As Long
    Dim a As Long
    Dim m As Long
  
    Application.ScreenUpdating = False
  
'   Set worksheets
    Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")

'   Copy data from first sheet to second
    ws1.Cells.Copy
    ws2.Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
  
'   Find last row with data on second sheet
    lr = ws2.Cells(Rows.Count, "A").End(xlUp).Row
  
'   Insert columns A and C and add titles
    Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1") = "Count"
    Range("C1") = "Group"
  
'   Set initial value of row and ASCII code character for group
    r = 2
    a = 65
  
'   Loop through rows
    ws2.Activate
    Do
'       Exit once past row
        If r > lr Then Exit Do
'       Put formula in column A and C
        Cells(r, "A").FormulaR1C1 = "=COUNTIF(C[3],RC[3])"
        Cells(r, "C").Formula = "=CHAR(" & a & ")"
'       Merge cells
        m = Cells(r, "A").Value
        If m > 1 Then
            Range(Cells(r, "A"), Cells(r + m - 1, "A")).Merge
            Range(Cells(r, "C"), Cells(r + m - 1, "C")).Merge
        End If
'       Move to next section and increment group
        r = r + m
        a = a + 1
    Loop
  
'   Center columns A and C
    With Columns("A:A")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
    With Columns("C:C")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
  
    Application.ScreenUpdating = True
  
End Sub

awesome! That worked. Thank you so much. I'll play around with the code to make some changes here and there and figure out how they work. Thanks again for showing me something new.

 

[Joe4]

You are welcome.

I tried to add lots of comments to the code to explain what each step is doing, but feel free to post any questions you may have if there is something you do not understand.

 

[bkuu]

You are welcome.

I tried to add lots of comments to the code to explain what each step is doing, but feel free to post any questions you may have if there is something you do not understand.

quick question Joe. What if I tried adding more columns after Name into sheet1, which line do I edit so that I can keep the Count and Group merge formula.

 

[bkuu]

I added a last name column but the formula broke - pointing to this line
Cells(r, "A").FormulaR1C1 = "=COUNTIF(D[2],RC[2])"

 

[Joe4]

quick question Joe. What if I tried adding more columns after Name into sheet1, which line do I edit so that I can keep the Count and Group merge formula.

As long as you add those fields after the columns you currently have, and they aren't to be used in determining the Counts or Groupings, you should be fine to add more columns.

 

[Joe4]

I added a last name column but the formula broke - pointing to this line
Cells(r, "A").FormulaR1C1 = "=COUNTIF(D[2],RC[2])"

Are you adding it BEFORE or AFTER running the VBA code?