Need help with VBA

[EduardoP]

Hi, I have the following code (adapted from a code I found) that create new sheets for each user AC for example, I have as well another sheet called Schedules A where the user names are from C1 to T1. What I need to do is to loop through all sheets look into the Schedules A sheet for the name (sheet names match names in C1:T1) and copy the whole column from the Schedules A sheet into the user sheet name in column AA

Sub SplitNames_in_newSheets()
Const sHelp$ = "AU" '<< Helper column, change as needed
Const sCol$ = "AS" '<<< <NAMES> in Column B, change as needed
Const shN$ = "INVOICES"
 '<<< Source Sheet Name, change as needed
Dim ws As Worksheet, ws1 As Worksheet
Set ws = Sheets(shN)
Dim r As Long, c As Long, x As Long, r1 As Long
Application.ScreenUpdating = False
ws.AutoFilterMode = False
r = ws.Range("A1").CurrentRegion.Rows.Count
c = ws.Range("A1").CurrentRegion.Columns.Count
ws.Range(sCol & ":" & sCol).Copy
ws.Cells(1, sHelp).PasteSpecial xlValues
Application.CutCopyMode = False
ws.Cells(1, sHelp).Resize(r).RemoveDuplicates Columns:=1, Header:=xlYes
r1 = ws.Cells(Rows.Count, sHelp).End(xlUp).Row
ws.Cells(1, sHelp).Resize(r1).Sort key1:=ws.Cells(1, sHelp), Header:=xlYes
For x = 2 To r1
ws.Cells(1, sCol).Resize(r1).AutoFilter Field:=1, Criteria1:=ws.Cells(x, sHelp) '<<edit
Set ws1 = Worksheets.Add(after:=Worksheets(x - 1))
ws1.Name = ws.Cells(x, sHelp).Value
ws.Range("A1").Resize(r, c).SpecialCells(xlCellTypeVisible).Copy
With ws1.Range("A15")
.PasteSpecial Paste:=xlPasteColumnWidths
.PasteSpecial Paste:=xlPasteFormats
.PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False
Next x
With ws
.AutoFilterMode = False
.Cells(1, sHelp).Resize(r).ClearContents
.Activate
End With
Application.ScreenUpdating = True
End Sub

Thank you for your help
Eduardo

 

[JackDanIce]

Welcome to the board.

Try:

Sub M1()
    
    Dim w   As Long
    Dim x   As Long
    Dim y   As Long
    Dim rng As Range
    
    Application.ScreenUpdating = False
    
    With sheets("INVOICES")
        If .AutoFilterMode Then .AutoFilterMode = False
        For x = 3 To 20
            On Error Resume Next
            Set rng = sheets(.Cells(1, x).Value).Cells(1, 27)
            On Error GoTo 0
            If Not rng Is Nothing Then
                y = .Cells(.Rows.Count, x).End(xlUp).Row
                rng.Resize(y).Value = .Cells(1, x).Resize(y).Value
                Set rng = Nothing
            End If
        Next x
    End With
    
    Application.ScreenUpdating = True


End Sub

 

[EduardoP]

Thank you Jack, I think I was not clear, the code open all sheets no problem there, what I cannot get is to match sheet name with the column header in sheet Schedules A and then copy the matching column from Schedules A to the sheet which name was matched, then do the same for the other sheets, if sheet name don't match any header within Schedules A, just do nothing, thank you for your help

 

[JackDanIce]

Did you try the suggested code? It pastes the columns data to sheet with same name into column AA if a match is found

 

[EduardoP]

Yes I did, but the columns to be copied from are under sheet called "Schedules A", no "Invoices". What I am working on is a Commission calculation, sheet Invoices has all the invoices created and the Sales person, Schedules A has how commissions are paid for each Sales person (Every one has a different commission calculation), then what my macro does is to split in different tabs all invoices linked to each salesperson, now what I cannot get is to bring into this new sheets the information for each salesperson about their commission calculation in order to make the calculations for each salesperson, then I will send each sheet to the Sales Manager for control. Thank you

 

[JackDanIce]

You can change that part of the code from Invoices to Schedules A

 

[EduardoP]

Hi Jack, changed the name and run the macro nothing happens any idea, sorry not very handy with VBA, thank you

 

[JackDanIce]

I tested it based on your comments and it works for me, contents of the column with matching sheet name are pased to column AA of named sheet from sheet Schedules A

 

[EduardoP]

Thank you so much, got it working my mistake when changed the name had a blank space, sorry it would be too difficult when copying the information always to include the first column that is the description, thank you again for your patient and have a great weekend

 

[JackDanIce]

You're welcome and glad you have it resolved