macro modification question

[bluepenink]

Hi all

i have the below macro. it essentially copies data from "master" sheet into "template" and then creates seperate employee ID based sheets - exact copy of master except only containing the employee specific data.

it works fine, however, I am not sure how I can make it also take into account another extra row of data.

Can someone tell me how the logic is working here when it extracts the data to copy/paste i.e. how many rows it copy/paste?

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Sub CreateSheets()
    Dim ms      As Worksheet
    Dim ws      As Worksheet
    Dim sh      As Worksheet
    Dim cell    As Range
    Dim rng     As Range
    Dim oRange  As Range

    Application.ScreenUpdating = False
        Set ms = ThisWorkbook.Sheets("Master")
        Set ws = ThisWorkbook.Sheets("Template")
        Set rng = ms.Range("A5:A" & ms.Cells(Rows.Count, 1).End(xlUp).Row)
    
        For Each sh In ThisWorkbook.Worksheets
            If sh.Name <> ms.Name And sh.Name <> "Template" Then
                Application.DisplayAlerts = False
                    sh.Delete
                Application.DisplayAlerts = True
            End If
        Next sh
    
        For Each cell In rng
            If cell.Value = "" Then
                If oRange Is Nothing Then Set oRange = cell Else Set oRange = Union(oRange, cell)
            End If
        Next cell
        If Not oRange Is Nothing Then oRange.ClearContents
    
        For Each cell In rng.SpecialCells(2).Areas
            ws.Copy After:=ThisWorkbook.Sheets(Sheets.Count)
    
            ActiveSheet.Name = ms.Range(Split(cell.Columns(3).Offset(1).Address, ":")(1)).Value
            cell.Resize(cell.Rows.Count + 1, cell.Columns.Count + 9).Copy
            ActiveSheet.Range("A5").PasteSpecial xlPasteAll
            Application.Goto ActiveSheet.Range("A1")

        Next cell
    
        Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

</code>

 

[JLGWhiz]

Set rng = ms.Range("A5:A" & ms.Cells(Rows.Count, 1).End(xlUp).Row)

The above statement puts everything on the Master Sheet from row 5 downward into the rng variable. All rows are included.

cell.Resize(cell.Rows.Count + 1, cell.Columns.Count + 9).Copy

The above statement creates a 2 row by 10 column range, beginning at whichever row 'cell' happens to be in at the time. To increase the number of rows, change the value of 1.

 

[bluepenink]

thanks for your reply - that definitely works.

question - if i change the value to "2" then it takes data in row below that is not pertaining to the row above.

for example, employee # is listed in rows 1-3 with row 4 being subtotal for currency in CAD. there are instances where row 5 = subtotal for USD. not all ppl have USD, so when I include 2 in the macro, for ppl that do not have USD subtotal, it takes the next person's row.

is there a way to limit this? thxs

 

[JLGWhiz]

thanks for your reply - that definitely works.

question - if i change the value to "2" then it takes data in row below that is not pertaining to the row above.

for example, employee # is listed in rows 1-3 with row 4 being subtotal for currency in CAD. there are instances where row 5 = subtotal for USD. not all ppl have USD, so when I include 2 in the macro, for ppl that do not have USD subtotal, it takes the next person's row.

is there a way to limit this? thxs

Not sure I understand what you are asking, but if you want to skip over a row, it would require a completely different syntax. You can't do that by just changing a number in the current code. You also might want to look at the Offset method which would allow you to use the current 'cell' range as a base and skip rows or columns like: cell.Offset(3).EntireRow
would return the a row three rows beneath where 'cell' currently returns.