How Do I adjust VBA code to only combine 2 worksheets rather than all worksheets...

[NHagedorn]

Trying to only combine sheet ("XPO" and "DDD"). I tried this exclusion
If ws.Name <> "Stuff" And ws.Name <> "Data" And ws.Name <> "Next Injection" And ws.Name <> "Pull Through" And ws.Name <> "CDR" And ws.Name <> "Acct-W-Syr" And ws.Name <> "Pres-W-Syr" Then

but I'm getting error and it is still adding the last 2 (Acct-W-Syr and Pres-W-Syr)

How can I adjust the VBA code?



Sub CDRCombine()
Application.ScreenUpdating = False
Dim wsCDR As Worksheet
Dim LastRowWs As Long
Dim LastRowCDR As Long
Dim StartRowCDR As Long

Set wsCDR = ThisWorkbook.Worksheets("CDR")
LastRowCDR = wsCDR.Cells(wsCDR.Rows.Count, "A").End(xlUp).Row + 1
wsCDR.Range("A2:R" & LastRowCDR).Clear

For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Stuff" And ws.Name <> "Data" And ws.Name <> "Next Injection" And ws.Name <> "Pull Through" And ws.Name <> "CDR" And ws.Name <> "Acct-W-Syr-Prolia" And ws.Name <> "Pres-W-Syr-Prolia" Then

LastRowWs = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
StartRowCDR = wsCDR.Cells(wsCDR.Rows.Count, "A").End(xlUp).Row + 1 'first empty row
ws.Range("A2:R" & LastRowWs).Copy Destination:=wsCDR.Range("A" & StartRowCDR)
LastRowCDR = wsCDR.Cells(wsCDR.Rows.Count, "A").End(xlUp).Row
wsCDR.Range("E" & StartRowCDR & ":E" & LastRowCDR) = ws.Name
End If
Next
Application.ScreenUpdating = True
End Sub

 

[arunsjain]

Code in module is

If ws.Name <> "Acct-W-Syr-Prolia" And ws.Name <> "Pres-W-Syr-Prolia"

But your worksheet name is "Acct-W-Syr" and "Pres-W-Syr" instead of "Acct-W-Syr-Prolia" and "Pres-W-Syr-Prolia" respectively. Either add "Prolia" in your worksheet name or remove it from your code. Then it will work.

 

[Trebor76]

Try this line:

If ws.Name = "XPO" Or ws.Name = "DDD" Then

HTH

Robert