0

I want a VBA code solution in which i compile sheets available in one workbook while giving sheet name create as "US" because those target sheets which i want to compile are having "US" in sheetname.

Situation: if i add new sheets then as per below existing code my target sheets got shifted to another sheet position.

Requirement: i want my compilation should be done on the basis of sheetname criteria as the target sheet from which i need to compile my data have "US" specific text in the target sheets name. so that if i add new sheet before or after it should only compile those sheets which have "US" writen in their sheetnames.

Public Sub Compiler()
Dim J As Integer
Dim NEWROW As Long, NEWROW2 As Long, NEWROW3 As Long, NEWROW4 As Long, NEWROW5 As Long

For J = 4 To Sheets.Count

    Sheets(J).Activate
    
    NEWROW = CompiledSheet.Range("F1048576").End(xlUp).Row + 1
    
    Therapyname = Sheets(J).Range("B3").Value
    Therapyname_ = Sheets(J).Range("B2").Value

    Range("B6").Select

    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    
    Selection.Copy
    
    CompiledSheet.Cells(NEWROW, "F").PasteSpecial xlPasteValuesAndNumberFormats

Next
End Sub

Thanks for your help in advance :)

Ashu Raj
  • 31
  • 2
  • Instead of `For J = 4 To Sheets.Count` use `For Each ws In Sheets`. Add a test `If InStr(ws.Name, "US") > 0 And ws.Name <> CompiledSheet.Name Then` – kevin May 05 '23 at 23:37
  • Also, you might want to use the built in powerquery for this. You can get a table of sheets, filter the table to get the ones you want, expand the contents, manipulate that, and then export the result to a different table. ... probably not worth trying to do that in this case now that you're so close to a solution but definitely check it out for future needs. – JSmart523 May 06 '23 at 06:39

0 Answers0