Consulting

Results 1 to 4 of 4

Thread: Creating Macro for dynamic range

  1. #1

    Creating Macro for dynamic range

    Hello. New to the forum and to VBA and Macros. I tried doing the research first but couldn't find an example that closely represented the issue I'm having.

    I need to write a macro that groups data in multiple columns based on the group number in the first column. For example:

    Step Area Volume
    1 2.6 80.0
    1 1.7 156.8
    1 3.6 80.0
    2 0.88 326.7
    2 1.75 83.9
    2 2.9 278.4
    2 0.56 894.8
    3 1.16 736.2
    3 4.72 189.0

    I want to organize the Area and Volume data in a separate table based on the associated Step number. In the end, I would ideally have 3 new tables, one for each Step (1,2, and 3) along with the Area and Volume data. For each workbook, however, the number of data points for each Step number will be different (hence dynamic range). How would I go about organizing this information based on the Step number?

    I might be missing something super obvious and easy so sorry in advance if this is the case...

    I appreciate the help!

  2. #2
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    .
    Paste to a Routine Module :

    Option Explicit
    
    
    Sub Copy_Counties()
    Dim StartTime As Double
    Dim MinutesElapsed As String
    
    
    Sheets("Master").Range("G1").Value = ""
    'Remember time when macro starts
    StartTime = Timer 'comment out when the elapsed timer no longer used
      
      
    'Application.ScreenUpdating = False  'uncomment when the elapsed timer no longer used
    Dim i As Long
    Dim Lastrow As Long
    On Error GoTo M
    Lastrow = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row
    Dim ans As String
        For i = 2 To Lastrow
        ans = Sheets("Master").Cells(i, 1).Value
            Sheets("Master").Rows(i).Copy Sheets(ans).Rows(Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row + 1)
            
            'Determine how many seconds code took to run
            MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
            
            Sheets("Master").Range("G1").Value = MinutesElapsed
            
        Next
    'Application.ScreenUpdating = True
    Exit Sub
    M:
    MsgBox "No such sheet as  " & ans & " exist"
    'Application.ScreenUpdating = True
    
    
    'Determine how many seconds code took to run
      MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
      
      'Sheets("Master").Range("G2").Value = Timer
      Sheets("Master").Range("G1").Value = MinutesElapsed   'comment out when elapsed time not required
    
    End Sub
    Attached Files Attached Files

  3. #3
    Awesome! Thanks a bunch!

    -Brendan

  4. #4
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    .
    You are welcome

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •