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]

The transfer expired. I had a water pipe burst in my home, I am just now getting back to this, sorry!

 

[vcoolio]

Refer back to post #19. Just change "Table1" to "Index" and it should all be resolved for you.

Cheerio,
vcoolio.

 

[itzlaforever]

I did that. It just tells me that the subscript is out of range.

 

[itzlaforever]

Refer back to post #19. Just change "Table1" to "Index" and it should all be resolved for you.

Cheerio,
vcoolio.

I'm basically just googling everything trying to get it to work. I say it's a "table" but its just formatted as a table.

 

[itzlaforever]

I figured it out. My Table name was named Table9 for some reason. It worked!! Thank you sooo much!

 

[itzlaforever]

I figured it out. My Table name was named Table9 for some reason. It worked!! Thank you sooo much!

Well the format of the table didn't copy for every column and row. This is super aggravating.

 

[itzlaforever]

If I only want certain columns to show, how could I manipulate your code? I've never used some of this coding, it's more advanced than what I have ever learned.

 

[vcoolio]

Hello Itzlaforever,

I'm now at a total loss as to what you are attempting to do.

Well the format of the table didn't copy for every column and row.

I know it works perfectly with the sample you supplied which I converted to a table. No problems at all.

If I only want certain columns to show, how could I manipulate your code?

Which columns do you want to copy over to the destination sheets?

If you're having problems implementing this code into your actual working workbook, then this suggests that the sample that you supplied isn't an exact replica of your actual workbook. You'd best upload an actual workbook sample to a file sharing site such as the one I used in an earlier post (WeTransfer).
With this, we may be able to solve this for you once and for all. Just post the link to the sample back here.

Cheerio,
vcoolio.

 

[itzlaforever]

My workbook has sensitive information in it now.


We have 3 sheets
Index, IndexDriver, IndexLabor

Let's say I only want columns C D E I H J K to copy to the IndexDriver sheet and only columns C D F L M columns to copy to the IndexLabor sheet.

Scenario:

I have a master worksheet with every employee and their data.
The Drivers (IndexDriver) only need columns A B E I H J
The Laborers (IndexLaborer) only need columns A B C D F

Instead of copying ALL columns to IndexDriver & IndexLabor, I only want certain columns to copy.

If this doesn't help, I can remove all sensitive information and send you just the columns and rows?

 

[vcoolio]

My workbook has sensitive information in it now.

Use dummy data with just a few rows per sheet and upload the sample as previously advised.

Cheerio,
vcoolio.