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

 

[EduardoP]

Hi Jack, sorry for bother you, would you mind helping to add first column (Description) of the Schedules A sheet) to the column Already copied, thanks

 

[JackDanIce]

Do you mean only move if it doesn't already exist? The ask is not clear

 

[EduardoP]

No, in you code when you copy the column that match the sheet name from Schedules A, add as well always the first column as an example
Column A= Description
Column B= AC
Column C-= BD

Then when copying columns from this sheet in sheet AC I need to copy column A and B
In sheet BD I will copy column A and C .....
Hope this clarify, thank you again and sorry for not including this from the beginning didn't think I needed column A

 

[JackDanIce]

No, I'm afraid it doesn't clarify. Can you use an exact example please? I appreciate English may not be your first language

 

[EduardoP]

Thank you so much Jack for all your help, I was able to figure it out how the code works and made the adjustments needed, thanks again for your time

 

[JackDanIce]

You're welcome, glad it's resolved and thanks for the feedback.