Duplicate Cells

[jdub21]

When I run my macro based on conditional formatting, it just adds to the bottom when i need it to post in A8. (I highlighted the cells in yellow to show the duplication)

New Vendors by State.xlsm
	C
25
Onboarded

 

[jdub21]

New Vendors by State.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
9	Source	Source City	Company	Date	Name	Phone	Call Result	Trailer	Pricing	Insurance?	Notes	Sourcer
10	Craigslist	Birmingham	Cheap Junk Removal Services			(205) 737-6217	Onboarding - Textline	7yd, 10yd, 15yd, 17yd	325/200	N	7=250, 10=325, 15=400, 17=450 per craigslist ad	Josh
11	Craigslist	Birmingham	One Man and a Pickup			(404) 939-2605	Onboarding - Textline	12x6x6 box truck	Quoted 300/175	SENDING	Regional - Birmingham to Jackson MS to Memphis TN	Josh
12	Google	Huntsville	JunkTruk Junk Removal			(256) 740-7263	Josh	15 CUY	450/200	SENDING		Josh
13	Craigslist	Mobile	Junk Boss			(251) 289-1334	Onboarding - Email	15CUY	425/225	Y	No food, no hazmat, no TVs	Josh
14	Craigslist	Mobile	Eastern Shore Junk Removal			(251) 348-1508	Trent	8CUY	350/200	SENDING	trent5422@gmail - Sent followup email for insurances on 9/8/22	Josh
15	Yelp	Montgomery	Curbside Appeal			(205) 420-2435	LVM - David Baxter	16ft box truck	300/175	Y	Offers daily pickups for apartments and senior living. May do junk?	Josh
16	Yelp	Montgomery	Porter's Property Preservation			(757) 386-7599	Onboarding - Email	8 x 12	350/175	Y	Montgomery only	Josh
17	Craigslist	Birmingham	Cheap Junk Removal Services			(205) 737-6217	Onboarding - Textline	7yd, 10yd, 15yd, 17yd	325/200	N	7=250, 10=325, 15=400, 17=450 per craigslist ad	Josh
18	Craigslist	Birmingham	One Man and a Pickup			(404) 939-2605	Onboarding - Textline	12x6x6 box truck	Quoted 300/175	SENDING	Regional - Birmingham to Jackson MS to Memphis TN	Josh
19	Google	Huntsville	JunkTruk Junk Removal			(256) 740-7263	Josh	15 CUY	450/200	SENDING		Josh
20	Craigslist	Mobile	Junk Boss			(251) 289-1334	Onboarding - Email	15CUY	425/225	Y	No food, no hazmat, no TVs	Josh
21	Craigslist	Mobile	Eastern Shore Junk Removal			(251) 348-1508	Trent	8CUY	350/200	SENDING	trent5422@gmail - Sent followup email for insurances on 9/8/22	Josh
22	Yelp	Montgomery	Curbside Appeal			(205) 420-2435	LVM - David Baxter	16ft box truck	300/175	Y	Offers daily pickups for apartments and senior living. May do junk?	Josh
23	Yelp	Montgomery	Porter's Property Preservation			(757) 386-7599	Onboarding - Email	8 x 12	350/175	Y	Montgomery only	Josh
Onboarded

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F23	Cell Value	duplicates	text	NO
F22	Cell Value	duplicates	text	NO
F21	Cell Value	duplicates	text	NO
F20	Cell Value	duplicates	text	NO
F19	Cell Value	duplicates	text	NO
F18	Cell Value	duplicates	text	NO
F17	Cell Value	duplicates	text	NO
F16	Cell Value	duplicates	text	NO
F15	Cell Value	duplicates	text	NO
F14	Cell Value	duplicates	text	NO
F13	Cell Value	duplicates	text	NO
F12	Cell Value	duplicates	text	NO
F11	Cell Value	duplicates	text	NO
F10	Cell Value	duplicates	text	NO

 

[jdub21]

Sorry about that. I am a little new to using the XL2BB feature to screen capture

 

[jdub21]

Bump - Can someone please help me on this issue?

 

[Joe4]

When I run my macro based on conditional formatting, it just adds to the bottom when i need it to post in A8. (I highlighted the cells in yellow to show the duplication)

I am not sure I understand exactly what the issue is or what you are trying to do.
If there is a problem involving VBA code, it would be a good idea to post that code!

I recommend posting your VBA code, then walk us through an actual example based on the sample data you posted, explaining exactly what you want to happen in that case.

 

[jdub21]

Sorry about that. I thought the XL2BB showed the code. My apologies as I am new to MrExcel..... Getting ready to sign up for the Virtual Conference. Here is the code and the challenge is that it is just adding to the bottom row or essentially duplicating entries versus overwriting or just posting new entries from the multiple sheets.



Sub CopyOnboarded()

Dim TransIDField As Range
Dim TransIDCell As Range
Dim ATransWS As Worksheet
Dim HTransWS As Worksheet

Set ATransWS = Worksheets("AL")
Set TransIDField = ATransWS.Range("A2", ATransWS.Range("A2").End(xlDown))
Set HTransWS = Worksheets("Onboarded")


For Each TransIDCell In TransIDField

If TransIDCell.Interior.Color = RGB(198, 239, 206) Then

'TransIDCell.Resize(1, 12).Copy Destination:= _
'HTransWS.Range("A1").Offset(HTransWS.Rows.Count - 1, 0).End(xlUp).Offset(1, 0)

End If

Next TransIDCell

HTransWS.Columns.AutoFit




Set ATransWS = Worksheets("CA")
Set TransIDField = ATransWS.Range("A2", ATransWS.Range("A2").End(xlDown))
Set HTransWS = Worksheets("Onboarded")


For Each TransIDCell In TransIDField

If TransIDCell.Interior.Color = RGB(198, 239, 206) Then

TransIDCell.Resize(1, 12).Copy Destination:= _
HTransWS.Range("A1").Offset(HTransWS.Rows.Count - 1, 0).End(xlUp).Offset(1, 0)
End If

Next TransIDCell

HTransWS.Columns.AutoFit

 

[Fluff]

You have already been warned about duplicating your threads, please do not do it again.

 

[jdub21]

Fluff - I understand - I was trying to close the other thread - I am new to this so my apologies. I am just trying to find a solution to my issue. Wont happen again.

 

[Joe4]

What you are describing sounds like a relational database to me (lots of interrelated data sheets that you are trying to summarize).
To be frank, I find trying to do this in Excel quite cumbersome, especially if you are new to Excel and/or VBA.
There are better tools already designed to handle stuff like this (like relational database programs like Microsoft Access or SQL). I usually go the Access route (though I am very comfortable with Access).

If you really want to try to do this in Excel, here are some options you may want to consider:
1. Using Filters, especially Advanced Filters which allow you to Filter data to new locations (sheets).
See: How to copy data to another worksheet with Advanced Filter in Excel?
2. Using Power Query, which is an add-on to Excel, which allows you to more easily complete relational database-type tasks in Excel.
There is a a "Power Tools" forum here on this board for those questions, and here is a Sticky with details on Power Query: Here are 30 reasons why you should be considering Power BI
3. Use Pivot Tables in Excel. There are lots of tutorials and example of this that can be found with a Google search.

 

[jdub21]

Joe - Thanks for the insight. I have been buried in macro code for the past few years and this one has me stumped. I have dissected other .xlsm files that I have worked with and do the same thing but have found that they are written to replace the file and not just the cell in that worksheet which seemed odd.

Basically I have a workbook that will have 50 tabs (one for each state) and on these sheets, I will have conditional formatting based on their stage in the onboarding process. I want to be able to pull all of the "Green" into one summary tab when I click on the "refresh" button...... but instead of it going to the bottom or next available row, I want it to start in a specific cell "A8"