Excel Macro: Dynamic Range Function

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

aem9266

Thread Starter
Joined
Jun 18, 2017
Messages
5
I am attempting to create a dynamic range based off the "Payroll" chart data so that if personnel are added, they are included in the macros powered updates. Currently, I am simply updating the individual ranges, but I am looking for a more long-term solution.

I have attempted to use the: RangeObject.CurrentRegion
Which I had changed to: Range("A3").CurrentRegion.Select
This simply selected from A3:S154. Which is not the range I wanted (A3:S82)
I have tried a few other formulas with no real luck and am attempting to not have to create individual sheets instead of a summary sheet.
I can post my current macro if needed, but it does not have any of the CurrentRegion selection in it. It is simply a range function based argument
upload_2017-6-19_13-55-15.png
 
Joined
Jan 15, 2004
Messages
772
Not enough information!

What is "Macros Powered Updates" ?

without having the code and possibly a copy of the workbook it is difficult to assist, we would need you to give us as much information as possible about the setup of the workbook and the code so we can try to help.
 

aem9266

Thread Starter
Joined
Jun 18, 2017
Messages
5
I apologize. I attached both the spreadsheet and the specific code that would need to have the updated ranges. The only other macro that uses the range specified (WT from A3:S82) and can update the ranges is the CrewLists code and it uses If statements to read whether or not there are crew initials in column S. The only way I can think to apply that code is to put New or some other word in the people without crews as it won't properly read blank cells (since there are multiple instances when that row is blank). I've included the Sub CrewLists () if that will help determine a solution

Thanks for your help

Code:
Sub CrewLists()

k = 3
y = 12

lst = Sheets("WT").UsedRange.Rows.Count


For i = 3 To 150

If Sheets("WT").Range("S" & i) = "SC" Then

Sheets("SC SI").Range("A" & y) = Sheets("WT").Range("A" & i)
Sheets("SC SI").Range("B" & y) = Sheets("WT").Range("B" & i)

k = k + 1
y = y + 1

End If
    Sheet28.Range("$A$12:$B$29").RemoveDuplicates Columns:=Array(1, 2), _
        Header:=xlNo
Next
k = 3
y = 12

lst = Sheets("WT").UsedRange.Rows.Count


For i = 3 To 150

If Sheets("WT").Range("S" & i) = "JM" Then

Sheets("JM SI").Range("A" & y) = Sheets("WT").Range("A" & i)
Sheets("JM SI").Range("B" & y) = Sheets("WT").Range("B" & i)

k = k + 1
y = y + 1

End If
   Sheet8.Range("$A$12:$B$29").RemoveDuplicates Columns:=Array(1, 2), _
        Header:=xlNo
Next
k = 3
y = 12

lst = Sheets("WT").UsedRange.Rows.Count


For i = 3 To 150

If Sheets("WT").Range("S" & i) = "AC" Then

Sheets("AC SI").Range("A" & y) = Sheets("WT").Range("A" & i)
Sheets("AC SI").Range("B" & y) = Sheets("WT").Range("B" & i)

k = k + 1
y = y + 1

End If
    Sheet10.Range("$A$12:$B$29").RemoveDuplicates Columns:=Array(1, 2), _
        Header:=xlNo
Next
k = 3
y = 12

lst = Sheets("WT").UsedRange.Rows.Count


For i = 3 To 150

If Sheets("WT").Range("S" & i) = "EG" Then

Sheets("EG SI").Range("A" & y) = Sheets("WT").Range("A" & i)
Sheets("EG SI").Range("B" & y) = Sheets("WT").Range("B" & i)

k = k + 1
y = y + 1

End If
    Sheet12.Range("$A$12:$B$29").RemoveDuplicates Columns:=Array(1, 2), _
        Header:=xlNo
Next
k = 3
y = 12

lst = Sheets("WT").UsedRange.Rows.Count


For i = 3 To 150

If Sheets("WT").Range("S" & i) = "RR" Then

Sheets("RR SI").Range("A" & y) = Sheets("WT").Range("A" & i)
Sheets("RR SI").Range("B" & y) = Sheets("WT").Range("B" & i)

k = k + 1
y = y + 1

End If
    Sheet14.Range("$A$12:$B$29").RemoveDuplicates Columns:=Array(1, 2), _
        Header:=xlNo
Next
k = 3
y = 12

lst = Sheets("WT").UsedRange.Rows.Count


For i = 3 To 150

If Sheets("WT").Range("S" & i) = "JG" Then

Sheets("JG SI").Range("A" & y) = Sheets("WT").Range("A" & i)
Sheets("JG SI").Range("B" & y) = Sheets("WT").Range("B" & i)

k = k + 1
y = y + 1

End If
    Sheet16.Range("$A$12:$B$29").RemoveDuplicates Columns:=Array(1, 2), _
        Header:=xlNo
Next
k = 3
y = 12

lst = Sheets("WT").UsedRange.Rows.Count


For i = 3 To 150

If Sheets("WT").Range("S" & i) = "JMc" Then

Sheets("JMc SI").Range("A" & y) = Sheets("WT").Range("A" & i)
Sheets("JMc SI").Range("B" & y) = Sheets("WT").Range("B" & i)

k = k + 1
y = y + 1

End If
    Sheet18.Range("$A$12:$B$29").RemoveDuplicates Columns:=Array(1, 2), _
        Header:=xlNo
Next
k = 3
y = 12

lst = Sheets("WT").UsedRange.Rows.Count


For i = 3 To 150
   Sheet8.Range("$A$12:$B$29").RemoveDuplicates Columns:=Array(1, 2), _
        Header:=xlNo
If Sheets("WT").Range("S" & i) = "EH" Then

Sheets("EH SI").Range("A" & y) = Sheets("WT").Range("A" & i)
Sheets("EH SI").Range("B" & y) = Sheets("WT").Range("B" & i)

k = k + 1
y = y + 1

End If
    Sheet20.Range("$A$12:$B$29").RemoveDuplicates Columns:=Array(1, 2), _
        Header:=xlNo
Next
k = 3
y = 12

lst = Sheets("WT").UsedRange.Rows.Count


For i = 3 To 150

If Sheets("WT").Range("S" & i) = "BS" Then

Sheets("BS SI").Range("A" & y) = Sheets("WT").Range("A" & i)
Sheets("BS SI").Range("B" & y) = Sheets("WT").Range("B" & i)

k = k + 1
y = y + 1
End If
    Sheet22.Range("$A$12:$B$29").RemoveDuplicates Columns:=Array(1, 2), _
        Header:=xlNo
Next
    ActiveWorkbook.Worksheets("AC SI").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("AC SI").Sort.SortFields.Add Key:=Range("A12"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("AC SI").Sort
        .SetRange Range("A12:B29")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("BS SI").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("BS SI").Sort.SortFields.Add Key:=Range("A12"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("BS SI").Sort
        .SetRange Range("A12:B31")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("EG SI").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("EG SI").Sort.SortFields.Add Key:=Range("A12"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("EG SI").Sort
        .SetRange Range("A12:B28")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("EH SI").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("EH SI").Sort.SortFields.Add Key:=Range("A12"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("EH SI").Sort
        .SetRange Range("A13:B31")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("JG SI").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("JG SI").Sort.SortFields.Add Key:=Range("A12"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("JG SI").Sort
        .SetRange Range("A12:B29")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("JMc SI").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("JMc SI").Sort.SortFields.Add Key:=Range("A12"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("JMc SI").Sort
        .SetRange Range("A12:B29")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("RG SI").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("RG SI").Sort.SortFields.Add Key:=Range("A12"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("RG SI").Sort
        .SetRange Range("A12:B31")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("RR SI").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("RR SI").Sort.SortFields.Add Key:=Range("A12"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("RR SI").Sort
        .SetRange Range("A12:B29")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("SC SI").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("SC SI").Sort.SortFields.Add Key:=Range("A12"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("SC SI").Sort
        .SetRange Range("A12:B30")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Sheets("AC SI").Range("A12:M29").borders.LineStyle = xlContinuous
Sheets("BS SI").Range("A12:M29").borders.LineStyle = xlContinuous
Sheets("EG SI").Range("A12:M29").borders.LineStyle = xlContinuous
Sheets("EH SI").Range("A12:M29").borders.LineStyle = xlContinuous
Sheets("JG SI").Range("A12:M29").borders.LineStyle = xlContinuous
Sheets("JM SI").Range("A12:M29").borders.LineStyle = xlContinuous
Sheets("JMc SI").Range("A12:M29").borders.LineStyle = xlContinuous
Sheets("RG SI").Range("A12:M29").borders.LineStyle = xlContinuous
Sheets("RR SI").Range("A12:M29").borders.LineStyle = xlContinuous
Sheets("SC SI").Range("A12:M29").borders.LineStyle = xlContinuous
    Range("A1").Select
    Application.CutCopyMode = False
End Sub
 

Attachments

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,656
Current.Region will 'end' the moment it encounters an empty row.
So it will not cover the entire worksheet
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top