Compare data from 2 workbook sheets and inserting new/missing data
I am very green to code so apologies if this has already been answered but I am struggling to find a code that covers what i would like to do.
So i have to two workbooks, one has a list of my projects the other also lists these projects but is used for a different purpose, currently defined as "January_2019". Currently i manually enter new rows in the second workbook containing January_2019 and physically copy and paste the new projects names and numbers over from Projects workbook. Simple enough but wondered if this could be automated with VBA.
I need to bring in the project number I allocate (column A in WB1) and paste it in column B in WB2. As well as and the Name (column F in WB1) and paste into column C in WB2.
Can the data set be compared and rows inserted to suit the new data?
I have had a go at the code based on what i have found but all help would be appreciated as i am getting an runtime error 9 for the "else" part.
Sub InsertJobs()
Dim wbkA As Variant
Dim varSheetA As Variant
Dim varSheetB As Variant
Dim strRangeToCheck As String
Dim iRow As Long
Dim iCol As Long
Set wbkA = Workbooks.Open(Filename:="P:Projects.xls")
Set varSheetA = wbkA.Worksheets("Job_List")
strRangeToCheck = "A1:G500"
Debug.Print Now
varSheetA = Worksheets("Job_List").Range(strRangeToCheck)
varSheetB = Worksheets("January_2019").Range(strRangeToCheck)
Debug.Print Now
For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
If varSheetA(iRow, iCol) = varSheetB(iRow, iCol) Then
' Cells are identical.
' Do nothing.
Else
' Cells are different.
Sheets(varSheetA).Range("A" & "F").Copy
Destination:=Sheets("January_2019").Range("B" & "C")
Dim i As Long, r1 As Range, r2 As Range
For i = 4 To 500
Set r1 = Range("A" & i)
Set r2 = Range("B" & i & ":C" & i)
Next i
End If
Next iCol
Next iRow
End Sub
beginner
asked 2 hours ago
Gary PS
1
New contributor
Gary PS is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I am very green to code so apologies if this has already been answered but I am struggling to find a code that covers what i would like to do.
So i have to two workbooks, one has a list of my projects the other also lists these projects but is used for a different purpose, currently defined as "January_2019". Currently i manually enter new rows in the second workbook containing January_2019 and physically copy and paste the new projects names and numbers over from Projects workbook. Simple enough but wondered if this could be automated with VBA.
I need to bring in the project number I allocate (column A in WB1) and paste it in column B in WB2. As well as and the Name (column F in WB1) and paste into column C in WB2.
Can the data set be compared and rows inserted to suit the new data?
I have had a go at the code based on what i have found but all help would be appreciated as i am getting an runtime error 9 for the "else" part.
Sub InsertJobs()
Dim wbkA As Variant
Dim varSheetA As Variant
Dim varSheetB As Variant
Dim strRangeToCheck As String
Dim iRow As Long
Dim iCol As Long
Set wbkA = Workbooks.Open(Filename:="P:Projects.xls")
Set varSheetA = wbkA.Worksheets("Job_List")
strRangeToCheck = "A1:G500"
Debug.Print Now
varSheetA = Worksheets("Job_List").Range(strRangeToCheck)
varSheetB = Worksheets("January_2019").Range(strRangeToCheck)
Debug.Print Now
For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
If varSheetA(iRow, iCol) = varSheetB(iRow, iCol) Then
' Cells are identical.
' Do nothing.
Else
' Cells are different.
Sheets(varSheetA).Range("A" & "F").Copy
Destination:=Sheets("January_2019").Range("B" & "C")
Dim i As Long, r1 As Range, r2 As Range
For i = 4 To 500
Set r1 = Range("A" & i)
Set r2 = Range("B" & i & ":C" & i)
Next i
End If
Next iCol
Next iRow
End Sub
beginner
asked 2 hours ago
Gary PS
1
New contributor
Gary PS is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I am very green to code so apologies if this has already been answered but I am struggling to find a code that covers what i would like to do.
So i have to two workbooks, one has a list of my projects the other also lists these projects but is used for a different purpose, currently defined as "January_2019". Currently i manually enter new rows in the second workbook containing January_2019 and physically copy and paste the new projects names and numbers over from Projects workbook. Simple enough but wondered if this could be automated with VBA.
I need to bring in the project number I allocate (column A in WB1) and paste it in column B in WB2. As well as and the Name (column F in WB1) and paste into column C in WB2.
Can the data set be compared and rows inserted to suit the new data?
I have had a go at the code based on what i have found but all help would be appreciated as i am getting an runtime error 9 for the "else" part.
Sub InsertJobs()
Dim wbkA As Variant
Dim varSheetA As Variant
Dim varSheetB As Variant
Dim strRangeToCheck As String
Dim iRow As Long
Dim iCol As Long
Set wbkA = Workbooks.Open(Filename:="P:Projects.xls")
Set varSheetA = wbkA.Worksheets("Job_List")
strRangeToCheck = "A1:G500"
Debug.Print Now
varSheetA = Worksheets("Job_List").Range(strRangeToCheck)
varSheetB = Worksheets("January_2019").Range(strRangeToCheck)
Debug.Print Now
For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
If varSheetA(iRow, iCol) = varSheetB(iRow, iCol) Then
' Cells are identical.
' Do nothing.
Else
' Cells are different.
Sheets(varSheetA).Range("A" & "F").Copy
Destination:=Sheets("January_2019").Range("B" & "C")
Dim i As Long, r1 As Range, r2 As Range
For i = 4 To 500
Set r1 = Range("A" & i)
Set r2 = Range("B" & i & ":C" & i)
Next i
End If
Next iCol
Next iRow
End Sub
beginner
asked 2 hours ago
Gary PS
1
New contributor
Gary PS is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I am very green to code so apologies if this has already been answered but I am struggling to find a code that covers what i would like to do.
So i have to two workbooks, one has a list of my projects the other also lists these projects but is used for a different purpose, currently defined as "January_2019". Currently i manually enter new rows in the second workbook containing January_2019 and physically copy and paste the new projects names and numbers over from Projects workbook. Simple enough but wondered if this could be automated with VBA.
I need to bring in the project number I allocate (column A in WB1) and paste it in column B in WB2. As well as and the Name (column F in WB1) and paste into column C in WB2.
Can the data set be compared and rows inserted to suit the new data?
I have had a go at the code based on what i have found but all help would be appreciated as i am getting an runtime error 9 for the "else" part.
Sub InsertJobs()
Dim wbkA As Variant
Dim varSheetA As Variant
Dim varSheetB As Variant
Dim strRangeToCheck As String
Dim iRow As Long
Dim iCol As Long
Set wbkA = Workbooks.Open(Filename:="P:Projects.xls")
Set varSheetA = wbkA.Worksheets("Job_List")
strRangeToCheck = "A1:G500"
Debug.Print Now
varSheetA = Worksheets("Job_List").Range(strRangeToCheck)
varSheetB = Worksheets("January_2019").Range(strRangeToCheck)
Debug.Print Now
For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
If varSheetA(iRow, iCol) = varSheetB(iRow, iCol) Then
' Cells are identical.
' Do nothing.
Else
' Cells are different.
Sheets(varSheetA).Range("A" & "F").Copy
Destination:=Sheets("January_2019").Range("B" & "C")
Dim i As Long, r1 As Range, r2 As Range
For i = 4 To 500
Set r1 = Range("A" & i)
Set r2 = Range("B" & i & ":C" & i)
Next i
End If
Next iCol
Next iRow
End Sub
beginner
beginner
asked 2 hours ago
Gary PS
1
New contributor
Gary PS is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 hours ago
Gary PS
1
New contributor
Gary PS is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 hours ago
Gary PS
1
New contributor
Gary PS is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 hours ago
Gary PS
1
asked 2 hours ago
Gary PS
1
1
New contributor
Gary PS is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Gary PS is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Gary PS is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
return StackExchange.using("mathjaxEditing", function () {
StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix) {
StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["\$", "\$"]]);
});
});
}, "mathjax-editing");
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: "196"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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
});
}
});
Gary PS is a new contributor. Be nice, and check out our Code of Conduct.
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%2fcodereview.stackexchange.com%2fquestions%2f210564%2fcompare-data-from-2-workbook-sheets-and-inserting-new-missing-data%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Gary PS is a new contributor. Be nice, and check out our Code of Conduct.
Gary PS is a new contributor. Be nice, and check out our Code of Conduct.
Gary PS is a new contributor. Be nice, and check out our Code of Conduct.
Gary PS is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Code Review Stack Exchange!
- 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.
Use MathJax to format equations. MathJax reference.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2fcodereview.stackexchange.com%2fquestions%2f210564%2fcompare-data-from-2-workbook-sheets-and-inserting-new-missing-data%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
