Hello All,
I am still in the process of automating my workbook and am trying to work through the kinks of learning how to use VBA to do so. At this point I have the following code that I use to parse out the data for individual account managers and copy and paste that data to a new tab, then name that tab after the account manager who it corresponds with. This was my initial goal, however I have been asked to add in some more features for this workbook so I will walk you through what I am trying to accomplish.
The whole reason behind parsing out the data for each AM is that they are not privileged to see other account managers client data, commission, etc.
Sub AccountManager()
Sheets("2014-2015").Select
Dim lastrow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long
Dim ws As Worksheet
Application.ScreenUpdating = False
With ActiveSheet
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
.Range(.Cells(2, 1), Cells(lastrow, LastCol)).Sort Key1:=Range("J2"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
iStart = 2
For i = 2 To lastrow
If .Range("J" & i).Value <> .Range("J" & i + 1).Value Then
iEnd = i
Sheets.Add after:=Sheets(Sheets.Count)
Set ws = ActiveSheet
On Error Resume Next
ws.Name = .Range("J" & iStart).Value
On Error GoTo 0
ws.Range(Cells(1, 1), Cells(1, LastCol)).Value = .Range(.Cells(1, 1), .Cells(1, LastCol)).Value
.Range(.Cells(iStart, 1), .Cells(iEnd, LastCol)).Copy
ws.Range("A2").PasteSpecial Paste:=xlPasteValues
iStart = iEnd + 1
End If
Next i
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
The issue is, I am now realizing that the pivot tables and slicers that are in the work book run off of the main '2014-2015' tab that all the data is copy and pasted from. This has made me rethink what I am trying to accomplish by automating this workbook, and I have a good idea of what I want to do, I just don't have the technical expertise on how to write the code dynamically to accomplish this.
The original section of code above, reads the column with account managers, cycles through each one, and copies and pastes corresponding data to a new tab for that account manager, and names that tab after them.
I have figured out a way to make it work, but it looks extremely sloppy and requires running a macro to parse the data, then individual macros for each AM, to clear the main tab, and to copy the data back into the '2014-2015' tab (which drives the pivots and slicers). Then refreshing the data before deleting the tabs for the other accounts managers out of that workbook before I can save it down for that individual AM.
However, this code seems to be obsolete for what I am not trying to accomplish, which is as follows:
Ideally, I would like the VBA code to filter for account manager in the '2014-2015' tab, strip out all AM's other than the one selected from that column, refresh the workbook (so slicers and pivots refresh just with that individual AM's data), and save as a new copy with the AM name attached to the end of the workbook.
The issue is, I have no idea how I would do this to loop for each AM from the main tab, and save a new workbook copy for each of them without messing up the data from the master file. As of now there are 7 total AM's but there could be more added in the future.
I am still in the process of automating my workbook and am trying to work through the kinks of learning how to use VBA to do so. At this point I have the following code that I use to parse out the data for individual account managers and copy and paste that data to a new tab, then name that tab after the account manager who it corresponds with. This was my initial goal, however I have been asked to add in some more features for this workbook so I will walk you through what I am trying to accomplish.
The whole reason behind parsing out the data for each AM is that they are not privileged to see other account managers client data, commission, etc.
Sub AccountManager()
Sheets("2014-2015").Select
Dim lastrow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long
Dim ws As Worksheet
Application.ScreenUpdating = False
With ActiveSheet
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
.Range(.Cells(2, 1), Cells(lastrow, LastCol)).Sort Key1:=Range("J2"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
iStart = 2
For i = 2 To lastrow
If .Range("J" & i).Value <> .Range("J" & i + 1).Value Then
iEnd = i
Sheets.Add after:=Sheets(Sheets.Count)
Set ws = ActiveSheet
On Error Resume Next
ws.Name = .Range("J" & iStart).Value
On Error GoTo 0
ws.Range(Cells(1, 1), Cells(1, LastCol)).Value = .Range(.Cells(1, 1), .Cells(1, LastCol)).Value
.Range(.Cells(iStart, 1), .Cells(iEnd, LastCol)).Copy
ws.Range("A2").PasteSpecial Paste:=xlPasteValues
iStart = iEnd + 1
End If
Next i
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
The issue is, I am now realizing that the pivot tables and slicers that are in the work book run off of the main '2014-2015' tab that all the data is copy and pasted from. This has made me rethink what I am trying to accomplish by automating this workbook, and I have a good idea of what I want to do, I just don't have the technical expertise on how to write the code dynamically to accomplish this.
The original section of code above, reads the column with account managers, cycles through each one, and copies and pastes corresponding data to a new tab for that account manager, and names that tab after them.
I have figured out a way to make it work, but it looks extremely sloppy and requires running a macro to parse the data, then individual macros for each AM, to clear the main tab, and to copy the data back into the '2014-2015' tab (which drives the pivots and slicers). Then refreshing the data before deleting the tabs for the other accounts managers out of that workbook before I can save it down for that individual AM.
However, this code seems to be obsolete for what I am not trying to accomplish, which is as follows:
Ideally, I would like the VBA code to filter for account manager in the '2014-2015' tab, strip out all AM's other than the one selected from that column, refresh the workbook (so slicers and pivots refresh just with that individual AM's data), and save as a new copy with the AM name attached to the end of the workbook.
The issue is, I have no idea how I would do this to loop for each AM from the main tab, and save a new workbook copy for each of them without messing up the data from the master file. As of now there are 7 total AM's but there could be more added in the future.
