Mixing
VBA macro to paste “n” pivot table field values dynamically
I am trying to make a program that reads a pivot table and pastes each field's data to a database with as little hard coding as possible.
The database has categories across the top row and the first day of each month for a year on the left side. The goal here is to take my spending data in a month (aggregated into a pivot table) and paste it to my database dynamically based on date and category.
Right now this program will work for most of the categories. Inexplicably it only wants to work on my one test pivot table. Other sheets or pivot tables are a no go, even if references are changed. It also hates the category "other" in column AJ for some reason and will not post to it, even if I change the name. Sometimes it decides something is no good and just breaks.
If an entry in the pivot table does not match a category it breaks, which is an issue as well.
Once the program breaks, I have to quit the VBA editor and re-open it to make the program stop error-ing.
Things typically break at the ColNum = Sheet5.Cells(2, 1).EntireRow... line, the TypeName field will sometimes have a category name that isn't even in the Pivot table anymore, but was the last test I ran.
Sub PivPasteNameData()
'loop variables
Dim I As Long
Dim j As Long
'pivot table variables
Dim TypeName As String
Dim TypeValue As Double
'match variables
Dim TodayDate As Range
Dim DateRange As Range
Dim CategoryRange As Range
'paste variables
Dim RowNum As Integer
Dim ColNum As Integer
'Setting match variables
'this points to a cell on dashboard with today's date
Set TodayDate = Sheet2.Range("C1")
'this points to the left hand column of the database, it is the first of each month in ascending order
Set DateRange = Sheet5.Range("A1:A100")
'This points to all the categories across the top of the database
Set CategoryRange = Sheet5.Range("A1:AZ2")
'the index # will need to be variable
Set PvTable = Sheet7.PivotTables(1)
'first loop
For I = 1 To PvTable.RowFields.Count
'second loop
For j = 1 To PvTable.RowFields(I).PivotItems.Count
'setting the pivot tables field name (the type of the cost)
TypeName = PvTable.RowFields(I).PivotItems(j)
'getting the value of the cost
TypeValue = Sheet7.PivotTables(1).GetPivotData("cost", "type", TypeName)
'debugging
'MsgBox TypeName
'MsgBox TypeValue
'find the corresponding date row numbner to the current date in the database
RowNum = Application.WorksheetFunction.Match(TodayDate, DateRange, 1)
'find the cost category column number in the database. For some reason I could NOT get Match working horizontally, my next attempt is going to use INDEX MATCH MATCH
ColNum = Sheet5.Cells(2, 1).EntireRow.Find(What:=TypeName, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
'paste cost value in correct date/category
Sheet5.Cells(RowNum, ColNum).Value = TypeValue
Next j
Next I
End Sub
excel vba excel-vba
edited Nov 22 '18 at 13:15
Pᴇʜ
23.6k62951
asked Nov 22 '18 at 13:12
Finlay McNallyFinlay McNally
1
add a comment |
I am trying to make a program that reads a pivot table and pastes each field's data to a database with as little hard coding as possible.
The database has categories across the top row and the first day of each month for a year on the left side. The goal here is to take my spending data in a month (aggregated into a pivot table) and paste it to my database dynamically based on date and category.
Right now this program will work for most of the categories. Inexplicably it only wants to work on my one test pivot table. Other sheets or pivot tables are a no go, even if references are changed. It also hates the category "other" in column AJ for some reason and will not post to it, even if I change the name. Sometimes it decides something is no good and just breaks.
If an entry in the pivot table does not match a category it breaks, which is an issue as well.
Once the program breaks, I have to quit the VBA editor and re-open it to make the program stop error-ing.
Things typically break at the ColNum = Sheet5.Cells(2, 1).EntireRow... line, the TypeName field will sometimes have a category name that isn't even in the Pivot table anymore, but was the last test I ran.
Sub PivPasteNameData()
'loop variables
Dim I As Long
Dim j As Long
'pivot table variables
Dim TypeName As String
Dim TypeValue As Double
'match variables
Dim TodayDate As Range
Dim DateRange As Range
Dim CategoryRange As Range
'paste variables
Dim RowNum As Integer
Dim ColNum As Integer
'Setting match variables
'this points to a cell on dashboard with today's date
Set TodayDate = Sheet2.Range("C1")
'this points to the left hand column of the database, it is the first of each month in ascending order
Set DateRange = Sheet5.Range("A1:A100")
'This points to all the categories across the top of the database
Set CategoryRange = Sheet5.Range("A1:AZ2")
'the index # will need to be variable
Set PvTable = Sheet7.PivotTables(1)
'first loop
For I = 1 To PvTable.RowFields.Count
'second loop
For j = 1 To PvTable.RowFields(I).PivotItems.Count
'setting the pivot tables field name (the type of the cost)
TypeName = PvTable.RowFields(I).PivotItems(j)
'getting the value of the cost
TypeValue = Sheet7.PivotTables(1).GetPivotData("cost", "type", TypeName)
'debugging
'MsgBox TypeName
'MsgBox TypeValue
'find the corresponding date row numbner to the current date in the database
RowNum = Application.WorksheetFunction.Match(TodayDate, DateRange, 1)
'find the cost category column number in the database. For some reason I could NOT get Match working horizontally, my next attempt is going to use INDEX MATCH MATCH
ColNum = Sheet5.Cells(2, 1).EntireRow.Find(What:=TypeName, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
'paste cost value in correct date/category
Sheet5.Cells(RowNum, ColNum).Value = TypeValue
Next j
Next I
End Sub
excel vba excel-vba
edited Nov 22 '18 at 13:15
Pᴇʜ
23.6k62951
asked Nov 22 '18 at 13:12
Finlay McNallyFinlay McNally
1
Please note that row counting variables need to be of typeLongExcel has more rows thanIntegercan handle:Dim RowNum As Long. I recommend always to use Long instead of Integer in VBA since there is no benefit in usingIntegerat all.
– Pᴇʜ
Nov 22 '18 at 13:17
What does "the program breaks" mean? Does it not respond? This can also mean that it is still in one of yourForloops (if there as a big amount ofRowFields. Put aDoEventsright beforeNext jandNext ito keep Excel responsive in case of long loops.
– Pᴇʜ
Nov 22 '18 at 13:20
add a comment |
I am trying to make a program that reads a pivot table and pastes each field's data to a database with as little hard coding as possible.
The database has categories across the top row and the first day of each month for a year on the left side. The goal here is to take my spending data in a month (aggregated into a pivot table) and paste it to my database dynamically based on date and category.
Right now this program will work for most of the categories. Inexplicably it only wants to work on my one test pivot table. Other sheets or pivot tables are a no go, even if references are changed. It also hates the category "other" in column AJ for some reason and will not post to it, even if I change the name. Sometimes it decides something is no good and just breaks.
If an entry in the pivot table does not match a category it breaks, which is an issue as well.
Once the program breaks, I have to quit the VBA editor and re-open it to make the program stop error-ing.
Things typically break at the ColNum = Sheet5.Cells(2, 1).EntireRow... line, the TypeName field will sometimes have a category name that isn't even in the Pivot table anymore, but was the last test I ran.
Sub PivPasteNameData()
'loop variables
Dim I As Long
Dim j As Long
'pivot table variables
Dim TypeName As String
Dim TypeValue As Double
'match variables
Dim TodayDate As Range
Dim DateRange As Range
Dim CategoryRange As Range
'paste variables
Dim RowNum As Integer
Dim ColNum As Integer
'Setting match variables
'this points to a cell on dashboard with today's date
Set TodayDate = Sheet2.Range("C1")
'this points to the left hand column of the database, it is the first of each month in ascending order
Set DateRange = Sheet5.Range("A1:A100")
'This points to all the categories across the top of the database
Set CategoryRange = Sheet5.Range("A1:AZ2")
'the index # will need to be variable
Set PvTable = Sheet7.PivotTables(1)
'first loop
For I = 1 To PvTable.RowFields.Count
'second loop
For j = 1 To PvTable.RowFields(I).PivotItems.Count
'setting the pivot tables field name (the type of the cost)
TypeName = PvTable.RowFields(I).PivotItems(j)
'getting the value of the cost
TypeValue = Sheet7.PivotTables(1).GetPivotData("cost", "type", TypeName)
'debugging
'MsgBox TypeName
'MsgBox TypeValue
'find the corresponding date row numbner to the current date in the database
RowNum = Application.WorksheetFunction.Match(TodayDate, DateRange, 1)
'find the cost category column number in the database. For some reason I could NOT get Match working horizontally, my next attempt is going to use INDEX MATCH MATCH
ColNum = Sheet5.Cells(2, 1).EntireRow.Find(What:=TypeName, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
'paste cost value in correct date/category
Sheet5.Cells(RowNum, ColNum).Value = TypeValue
Next j
Next I
End Sub
excel vba excel-vba
edited Nov 22 '18 at 13:15
Pᴇʜ
23.6k62951
asked Nov 22 '18 at 13:12
Finlay McNallyFinlay McNally
1
I am trying to make a program that reads a pivot table and pastes each field's data to a database with as little hard coding as possible.
The database has categories across the top row and the first day of each month for a year on the left side. The goal here is to take my spending data in a month (aggregated into a pivot table) and paste it to my database dynamically based on date and category.
Right now this program will work for most of the categories. Inexplicably it only wants to work on my one test pivot table. Other sheets or pivot tables are a no go, even if references are changed. It also hates the category "other" in column AJ for some reason and will not post to it, even if I change the name. Sometimes it decides something is no good and just breaks.
If an entry in the pivot table does not match a category it breaks, which is an issue as well.
Once the program breaks, I have to quit the VBA editor and re-open it to make the program stop error-ing.
Things typically break at the ColNum = Sheet5.Cells(2, 1).EntireRow... line, the TypeName field will sometimes have a category name that isn't even in the Pivot table anymore, but was the last test I ran.
Sub PivPasteNameData()
'loop variables
Dim I As Long
Dim j As Long
'pivot table variables
Dim TypeName As String
Dim TypeValue As Double
'match variables
Dim TodayDate As Range
Dim DateRange As Range
Dim CategoryRange As Range
'paste variables
Dim RowNum As Integer
Dim ColNum As Integer
'Setting match variables
'this points to a cell on dashboard with today's date
Set TodayDate = Sheet2.Range("C1")
'this points to the left hand column of the database, it is the first of each month in ascending order
Set DateRange = Sheet5.Range("A1:A100")
'This points to all the categories across the top of the database
Set CategoryRange = Sheet5.Range("A1:AZ2")
'the index # will need to be variable
Set PvTable = Sheet7.PivotTables(1)
'first loop
For I = 1 To PvTable.RowFields.Count
'second loop
For j = 1 To PvTable.RowFields(I).PivotItems.Count
'setting the pivot tables field name (the type of the cost)
TypeName = PvTable.RowFields(I).PivotItems(j)
'getting the value of the cost
TypeValue = Sheet7.PivotTables(1).GetPivotData("cost", "type", TypeName)
'debugging
'MsgBox TypeName
'MsgBox TypeValue
'find the corresponding date row numbner to the current date in the database
RowNum = Application.WorksheetFunction.Match(TodayDate, DateRange, 1)
'find the cost category column number in the database. For some reason I could NOT get Match working horizontally, my next attempt is going to use INDEX MATCH MATCH
ColNum = Sheet5.Cells(2, 1).EntireRow.Find(What:=TypeName, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
'paste cost value in correct date/category
Sheet5.Cells(RowNum, ColNum).Value = TypeValue
Next j
Next I
End Sub
excel vba excel-vba
excel vba excel-vba
edited Nov 22 '18 at 13:15
Pᴇʜ
23.6k62951
asked Nov 22 '18 at 13:12
Finlay McNallyFinlay McNally
1
edited Nov 22 '18 at 13:15
Pᴇʜ
23.6k62951
asked Nov 22 '18 at 13:12
Finlay McNallyFinlay McNally
1
edited Nov 22 '18 at 13:15
Pᴇʜ
23.6k62951
edited Nov 22 '18 at 13:15
Pᴇʜ
23.6k62951
edited Nov 22 '18 at 13:15
Pᴇʜ
23.6k62951
23.6k62951
asked Nov 22 '18 at 13:12
Finlay McNallyFinlay McNally
1
asked Nov 22 '18 at 13:12
Finlay McNallyFinlay McNally
1
asked Nov 22 '18 at 13:12
Finlay McNallyFinlay McNally
1
1
Please note that row counting variables need to be of typeLongExcel has more rows thanIntegercan handle:Dim RowNum As Long. I recommend always to use Long instead of Integer in VBA since there is no benefit in usingIntegerat all.
– Pᴇʜ
Nov 22 '18 at 13:17
What does "the program breaks" mean? Does it not respond? This can also mean that it is still in one of yourForloops (if there as a big amount ofRowFields. Put aDoEventsright beforeNext jandNext ito keep Excel responsive in case of long loops.
– Pᴇʜ
Nov 22 '18 at 13:20
add a comment |
Please note that row counting variables need to be of typeLongExcel has more rows thanIntegercan handle:Dim RowNum As Long. I recommend always to use Long instead of Integer in VBA since there is no benefit in usingIntegerat all.
– Pᴇʜ
Nov 22 '18 at 13:17
What does "the program breaks" mean? Does it not respond? This can also mean that it is still in one of yourForloops (if there as a big amount ofRowFields. Put aDoEventsright beforeNext jandNext ito keep Excel responsive in case of long loops.
– Pᴇʜ
Nov 22 '18 at 13:20
Please note that row counting variables need to be of type
Long Excel has more rows than Integer can handle: Dim RowNum As Long. I recommend always to use Long instead of Integer in VBA since there is no benefit in using Integer at all.– Pᴇʜ
Nov 22 '18 at 13:17
Please note that row counting variables need to be of type
Long Excel has more rows than Integer can handle: Dim RowNum As Long. I recommend always to use Long instead of Integer in VBA since there is no benefit in using Integer at all.– Pᴇʜ
Nov 22 '18 at 13:17
What does "the program breaks" mean? Does it not respond? This can also mean that it is still in one of your
For loops (if there as a big amount of RowFields. Put a DoEvents right before Next j and Next i to keep Excel responsive in case of long loops.– Pᴇʜ
Nov 22 '18 at 13:20
What does "the program breaks" mean? Does it not respond? This can also mean that it is still in one of your
For loops (if there as a big amount of RowFields. Put a DoEvents right before Next j and Next i to keep Excel responsive in case of long loops.– Pᴇʜ
Nov 22 '18 at 13:20
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53431819%2fvba-macro-to-paste-n-pivot-table-field-values-dynamically%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53431819%2fvba-macro-to-paste-n-pivot-table-field-values-dynamically%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Please note that row counting variables need to be of type
LongExcel has more rows thanIntegercan handle:Dim RowNum As Long. I recommend always to use Long instead of Integer in VBA since there is no benefit in usingIntegerat all.– Pᴇʜ
Nov 22 '18 at 13:17
What does "the program breaks" mean? Does it not respond? This can also mean that it is still in one of your
Forloops (if there as a big amount ofRowFields. Put aDoEventsright beforeNext jandNext ito keep Excel responsive in case of long loops.– Pᴇʜ
Nov 22 '18 at 13:20