Copying rows from one sheet to 2 other sheets

[itzlaforever]

Hi! Looking for some help with macros. I've never used them before. Basically, I need a list of employees (Index Worksheet) to be split on two separate worksheets (IndexDriver worksheet, IndexLaborer worksheet). I have the code to complete this task, but I need it to not duplicate these rows when the macro is ran again... Sorry if I am being too vague, I am new to this. Here is my code..

Sub CopyRowBasedOnCellValue()

Dim R1 As Range
Dim R2 As Range
Dim I As Long
Dim J As Long
Dim K As Long
I = Worksheets("Index").UsedRange.Rows.Count
J = Worksheets("IndexDriver").UsedRange.Rows.Count
If J = D Then
If Application.WorksheetFunction.CountA(Worksheets("IndexDriver").UsedRange) = 0 Then J = 0
End If
Set R1 = Worksheets("Index").Range("B1:B" & I)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To R1.Count
If CStr(R1(K).Value) = "D" Then
R1(K).EntireRow.Copy Destination:=Worksheets("IndexDriver").Range("A" & J + 1)
J = J + 1
End If
Next
Application.ScreenUpdating = True



I = Worksheets("Index").UsedRange.Rows.Count
J = Worksheets("IndexLaborer").UsedRange.Rows.Count
If J = D Then
If Application.WorksheetFunction.CountA(Worksheets("IndexLaborer").UsedRange) = 0 Then J = 0
End If
Set R1 = Worksheets("Index").Range("B1:B" & I)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To R1.Count
If CStr(R1(K).Value) = "1" Then
R1(K).EntireRow.Copy Destination:=Worksheets("IndexLaborer").Range("A" & J + 1)
J = J + 1
End If
Next
Application.ScreenUpdating = True
End Sub

 

[itzlaforever]

I figured out how to clear the contents with a new "sub".

Sub vba_used_range()

Worksheets("IndexDriver").Cells.ClearContents
Worksheets("IndexDriver").Cells.ClearFormats
Worksheets("IndexLaborer").Cells.ClearContents
Worksheets("IndexLaborer").Cells.ClearFormats

End Sub


However, I would like the task completed in the same "sub".

 

[itzlaforever]

And now the formatting isn't copying over. I give up!

 

[vcoolio]

Hello Itzalaforever,

I hope that you don't mind but I've completely re-written your code, just to streamline it and make it faster (I think!).

Option Explicit
Sub Test()

        Dim wsI As Worksheet, wsD As Worksheet, i As Long
        Dim ar As Variant: ar = [{"IndexDriver","IndexLaborer";"D","1"}]
        Set wsI = Sheets("Index")

Application.ScreenUpdating = False

        For i = 1 To UBound(ar, 2)
                     Set wsD = Sheets(ar(1, i))
                     wsD.UsedRange.Clear
                    
              With wsI.[A1].CurrentRegion
                      .AutoFilter 2, ar(2, i)
                      .Copy wsD.[A1]
                      .AutoFilter
                      wsD.Columns.AutoFit
              End With
        Next i

Application.ScreenUpdating = True

End Sub

I'm assuming that you are taking the headings over to the destination sheets as well.
The code will clear the destination sheets each time that it is run, ready for any new data plus existing data from the Index sheet, meaning that the destination sheets are basically refreshed without duplication.

I hope that this helps.

Cheerio,
vcoolio.

 

[itzlaforever]

I appreciate you taking the time to do that. However, this code does not work. I keep getting an Application-defined or object-defined error. I also do not understand the code you have written, too far past my knowledge I assume.

 

[vcoolio]

Hello Itzlaforevcer,

I created a mock-up file based on how I imagined your actual file is set out using the information from the code you supplied. The code works absolutely fine in the mock-up hence there is something amiss with the set out of your actual workbook which the code is not comprehending (or vice versa).
From here, assuming that you still need help with this, it would be best if you uploaded a sample of your actual file to a file sharing site such as Drop Box or WeTransfer and then post the link to your file back here. Make sure that the sample is an exact replica of your actual file and if your data is sensitive then please use dummy data. A dozen or so rows of data in the "Index" sheet will suffice.

Cheerio,
vcoolio.

 

[itzlaforever]

"C:\Users\Lssla\OneDrive\Documents\Copy of Employee Workbook.xlsm"

Hopefully the link or this photo helps.

 

[itzlaforever]

Microsoft OneDrive - Access files anywhere. Create docs with free Office Online.

Store photos and docs online. Access them from any PC, Mac or phone. Create and work together on Word, Excel or PowerPoint documents.

onedrive.live.com

 

[itzlaforever]

I just need to have one master sheet with drivers & laborers where I can add all of my information, that will update individual sheets showing only drivers & driver columns on the driverindex and only laborers & laborer columns on the laborerindex.

 

[vcoolio]

Hello Itzlaforever,

You've given us the file path not a link to a sample workbook. Further, we can't test from an image.
Please upload your file to a free file sharing site (such as WeTransfer) and post the link to your file back here. Be mindful of sensitive data.
Alternatively, use the XL2BB tool found at the top of the reply box or click on "Upload Mini-sheet" at the bottom of the reply box.

Cheerio,
vcoolio.