Need help with VBA

EduardoP

New Member
Joined
Sep 27, 2018
Messages
8
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

Code:
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
 
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
 
Upvote 0

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Do you mean only move if it doesn't already exist? The ask is not clear
 
Upvote 0
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
 
Upvote 0
No, I'm afraid it doesn't clarify. Can you use an exact example please? I appreciate English may not be your first language
 
Upvote 0
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
 
Upvote 0
You're welcome, glad it's resolved and thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,204
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top