spencerp237
02-24-2017, 01:04 PM
I have a workbook with the sheets "Advisors", "DataQuery", and "QueryCriteria". I am running a data query and then exporting the information into an excel worksheet from an online database, and then I copy/paste it into the "DataQuery" worksheet. Right now my macro:
1. Reads through all of the rows of data for certain criteria, if met, the rows are copied to the "Query Criteria" worksheet
2. Goes through the "QueryCriteria" worksheet and deletes all of the columns I do not need, and then moves the remaining columns to the left
i. I need 2 columns from the exported data. One is "Name" and one is "Amount".
3. I then use .Consolidate to read through the remaining data in the "QueryCriteria" worksheet and consolidate based on the "Name" and then sum the "Amount". This data is placed in the "Advisors" worksheet starting in cell A3. In A2, I have a title for the column. In column B starting at B3, the Amount is summed based on the name and placed here.
I can get this part to run fine. Here is my code so far:
Sub AdvisorSort()
Dim i As Long
Dim rowIndex As Long
Dim lastRow As Long
Dim lastRow2 As Long
Sheets("QueryCriteria").Rows("2:" & Rows.Count).ClearContents
Sheets("DataQuery").Select
rowIndex = 2
For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
If Cells(i, "Q") = "TRUE" And Cells(i, "M") = "Client Contribution" Or Cells(i, "M") = "Merge In from Other Account" Then
Rows(i).Copy Destination:=Sheets("QueryCriteria").Rows(rowIndex)
rowIndex = rowIndex + 1
End If
Next i
Sheets("QueryCriteria").Select
Sheets("QueryCriteria").Range(Sheets("QueryCriteria").Columns(1), Sheets("QueryCriteria").Columns(11)).Clear
Sheets("QueryCriteria").Range(Sheets("QueryCriteria").Columns(13), Sheets("QueryCriteria").Columns(14)).Clear
Sheets("QueryCriteria").Range(Sheets("QueryCriteria").Columns(16), Sheets("QueryCriteria").Columns(46)).Clear
Sheets("QueryCriteria").Columns("O:O").Cut
Sheets("QueryCriteria").Columns("A:A").Insert
Sheets("QueryCriteria").Columns("M:M").Cut
Sheets("QueryCriteria").Columns("B:B").Insert
lastRow = Sheets("QueryCriteria").Cells(Rows.Count, 1).End(xlUp).Row
lastRow2 = Sheets("Advisors").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("Advisors").Range("A3").Consolidate Array("QueryCriteria!R2C1:R" & lastRow & "C2", "Advisors!R3:C1;R" & lastRow2 & "C2"), xlSum, TopRow:=False, LeftColumn:=True
End Sub
I need help with the following:
1. This macro will be run monthly. New names could come up in the "Names" column. Each time it is run, it needs to read the Names already added to the "Advisor" worksheet (which will be in column A starting at A3). If the name is not already listed in that column, the name needs to be added to the the list.
a. I have tried adjusting the .Consoliate Array (...) to include both the data from "Query Criteria" and "Advisors", but whenever I tried to include the "Advisors" worksheet in the array and consolidate the data on top of it, I get an error.
2. The data query that is run is set to a date range (by month). I need to a way to title the the B column in the "Advisors" worksheet by month (ex: 12/16, 1/17, 2/17, etc.) in row 2. If the macro has already been run for that month, column B with the sum should be replaced with the new data. If the macro has not been run for that month, I need the sum placed in a new column. For example, if I have the data already in for the months 12/16 and 1/17 (2 separate columns, B & C) but am running the macro for 2/17 for the first time, the sum would be placed in column D, and the heading 2/17 created in cell D2. Also, I need to Total column always on the far right. Before adding 2/17, it would be in column D. After running the macro for 2/17, it would be moved to column E and include the new 2/17 data in column D in its total sum.
I apologize if this is confusing. I have been stuck on this for quite awhile and tried multiple different methods but can't get it to work. Feel free to ask any questions. Anything you can help with would be much appreciated.
1. Reads through all of the rows of data for certain criteria, if met, the rows are copied to the "Query Criteria" worksheet
2. Goes through the "QueryCriteria" worksheet and deletes all of the columns I do not need, and then moves the remaining columns to the left
i. I need 2 columns from the exported data. One is "Name" and one is "Amount".
3. I then use .Consolidate to read through the remaining data in the "QueryCriteria" worksheet and consolidate based on the "Name" and then sum the "Amount". This data is placed in the "Advisors" worksheet starting in cell A3. In A2, I have a title for the column. In column B starting at B3, the Amount is summed based on the name and placed here.
I can get this part to run fine. Here is my code so far:
Sub AdvisorSort()
Dim i As Long
Dim rowIndex As Long
Dim lastRow As Long
Dim lastRow2 As Long
Sheets("QueryCriteria").Rows("2:" & Rows.Count).ClearContents
Sheets("DataQuery").Select
rowIndex = 2
For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
If Cells(i, "Q") = "TRUE" And Cells(i, "M") = "Client Contribution" Or Cells(i, "M") = "Merge In from Other Account" Then
Rows(i).Copy Destination:=Sheets("QueryCriteria").Rows(rowIndex)
rowIndex = rowIndex + 1
End If
Next i
Sheets("QueryCriteria").Select
Sheets("QueryCriteria").Range(Sheets("QueryCriteria").Columns(1), Sheets("QueryCriteria").Columns(11)).Clear
Sheets("QueryCriteria").Range(Sheets("QueryCriteria").Columns(13), Sheets("QueryCriteria").Columns(14)).Clear
Sheets("QueryCriteria").Range(Sheets("QueryCriteria").Columns(16), Sheets("QueryCriteria").Columns(46)).Clear
Sheets("QueryCriteria").Columns("O:O").Cut
Sheets("QueryCriteria").Columns("A:A").Insert
Sheets("QueryCriteria").Columns("M:M").Cut
Sheets("QueryCriteria").Columns("B:B").Insert
lastRow = Sheets("QueryCriteria").Cells(Rows.Count, 1).End(xlUp).Row
lastRow2 = Sheets("Advisors").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("Advisors").Range("A3").Consolidate Array("QueryCriteria!R2C1:R" & lastRow & "C2", "Advisors!R3:C1;R" & lastRow2 & "C2"), xlSum, TopRow:=False, LeftColumn:=True
End Sub
I need help with the following:
1. This macro will be run monthly. New names could come up in the "Names" column. Each time it is run, it needs to read the Names already added to the "Advisor" worksheet (which will be in column A starting at A3). If the name is not already listed in that column, the name needs to be added to the the list.
a. I have tried adjusting the .Consoliate Array (...) to include both the data from "Query Criteria" and "Advisors", but whenever I tried to include the "Advisors" worksheet in the array and consolidate the data on top of it, I get an error.
2. The data query that is run is set to a date range (by month). I need to a way to title the the B column in the "Advisors" worksheet by month (ex: 12/16, 1/17, 2/17, etc.) in row 2. If the macro has already been run for that month, column B with the sum should be replaced with the new data. If the macro has not been run for that month, I need the sum placed in a new column. For example, if I have the data already in for the months 12/16 and 1/17 (2 separate columns, B & C) but am running the macro for 2/17 for the first time, the sum would be placed in column D, and the heading 2/17 created in cell D2. Also, I need to Total column always on the far right. Before adding 2/17, it would be in column D. After running the macro for 2/17, it would be moved to column E and include the new 2/17 data in column D in its total sum.
I apologize if this is confusing. I have been stuck on this for quite awhile and tried multiple different methods but can't get it to work. Feel free to ask any questions. Anything you can help with would be much appreciated.