niklasbp
02-04-2015, 07:48 AM
Hi there
i have a large WB with alot of information that i now want to split up of sorts
the final sheet is called "Rapport" and contains the results. this sheet has a range of names in column A2 going down and it variate. I have made a macro that delete all but the sheets that each person needs e.x. salesman1 only get the final sheet and the sheet about him so that he can't see the others information, and it looks like this:
' for adrian
Application.DisplayAlerts = False
Application.ScreenUpdating = False
For Each sh In ActiveWorkbook.Sheets
If sh.name <> "Rapport" And sh.name <> "Adrian Valve" Then
sh.Delete
End If
Next sh
ActiveWorkbook.SaveAs "Adrian.xlsx"
ActiveWorkbook.Close savechanges:=False
'for Asger Broberg
Workbooks.Open ("C:\Users\Mikkel\Desktop\Rapporter_2015\Februar\Rapport.xlsx")
For Each sh In ActiveWorkbook.Sheets
If sh.name <> "Rapport" And sh.name <> "Asger Broberg" Then
sh.Delete
End If
Next sh
ActiveWorkbook.SaveAs "Asger Broberg.xlsx"
ActiveWorkbook.Close savechanges:=False
as you can see i have made it work but it's "hard coded" meaning i have to manually add all the names right now and i would like to know if there was a way to instead of writing each name in the example "adrian valve" and "asger broberg" make this an variable called name that referer to column A2 and down?
i'm pretty new with VBA and i am trying to get the hang of it =D
/the vba newbie
i have a large WB with alot of information that i now want to split up of sorts
the final sheet is called "Rapport" and contains the results. this sheet has a range of names in column A2 going down and it variate. I have made a macro that delete all but the sheets that each person needs e.x. salesman1 only get the final sheet and the sheet about him so that he can't see the others information, and it looks like this:
' for adrian
Application.DisplayAlerts = False
Application.ScreenUpdating = False
For Each sh In ActiveWorkbook.Sheets
If sh.name <> "Rapport" And sh.name <> "Adrian Valve" Then
sh.Delete
End If
Next sh
ActiveWorkbook.SaveAs "Adrian.xlsx"
ActiveWorkbook.Close savechanges:=False
'for Asger Broberg
Workbooks.Open ("C:\Users\Mikkel\Desktop\Rapporter_2015\Februar\Rapport.xlsx")
For Each sh In ActiveWorkbook.Sheets
If sh.name <> "Rapport" And sh.name <> "Asger Broberg" Then
sh.Delete
End If
Next sh
ActiveWorkbook.SaveAs "Asger Broberg.xlsx"
ActiveWorkbook.Close savechanges:=False
as you can see i have made it work but it's "hard coded" meaning i have to manually add all the names right now and i would like to know if there was a way to instead of writing each name in the example "adrian valve" and "asger broberg" make this an variable called name that referer to column A2 and down?
i'm pretty new with VBA and i am trying to get the hang of it =D
/the vba newbie