PDA

View Full Version : Creating Macro for dynamic range



bwinnacott
11-30-2017, 02:25 PM
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!

Logit
11-30-2017, 04:47 PM
.
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

bwinnacott
12-01-2017, 04:07 PM
Awesome! Thanks a bunch!

-Brendan

Logit
12-01-2017, 06:26 PM
.
You are welcome