Excel VBA to copy a cell(s) and paste with varied criteria, appended.

[Plexus]

Hi,

I have a table that shows products that need to be matched to a set criteria and then pasted into a new table appended. The example below shows the desired result in D&E.

When a product (A) has an action (B) to 'Exclude COS', I want to paste appended the Product & the Macro Area listed in (G) 'STORES COS' to D&E.

When a Product has and action 'Exclude all' I want to paste the product and a repeated row, listing all Macro areas, again appended to D&E.

The 'list of Macro areas' does not need to be a list and can be coded if needed.

A	B		D	E		G
SKU	Action?		SKU	Macro Area		List of Macro areas
AALCMSCN	Exclude All		AALENTEN	STORES COS		STORES COS
AALENTEN	Exclude COS		AALCMSCN	STORES COS		SMB
			AALCMSCN	SMB		Enterprise
			AALCMSCN	Enterprise		Online
			AALCMSCN	Online

I have spent a couple of hours trying to work this out and watching various videos but have not managed it.

Thanks for any help given!

Plex.

 

[Peter_SSs]

Try this with a copy of your data.

Sub Rearrange()
  Dim Data As Variant, MacroAreas As Variant
  Dim i As Long, nr As Long, NumMacroAreas As Long, rws As Long
  
  Data = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value
  MacroAreas = Range("G2", Range("G" & Rows.Count).End(xlUp)).Value
  NumMacroAreas = UBound(MacroAreas)
  nr = 2
  For i = 1 To UBound(Data)
    rws = IIf(LCase(Data(i, 2)) = "exclude all", NumMacroAreas, 1)
    Cells(nr, "D").Resize(rws).Value = Data(i, 1)
    Cells(nr, "E").Resize(rws).Value = MacroAreas
    nr = nr + rws
  Next i
End Sub

 

[Plexus]

That worked perfectly. Thank you so much for your help Peter, I really appreciate it.

 

[Peter_SSs]

That worked perfectly. Thank you so much for your help Peter, I really appreciate it.

No problem. Glad to help. Thanks for the follow-up.