Mixing
activeX button to copy a row of data from sheet 1 to Sheet 2 based off cell value
I am new to stackoverflow.com and VBA. I have been searching the web for a VBA that will allow me to copy data from sheet 1 that I input and then paste into sheet 2 based off the a cell value match. Once it is copied, it would then clear the data on Sheet 1 without delete the rows.
I work in a call center, and this would be to update equipment based on the desk it is located at.
So I am hoping that once I input all the data into the fields on sheet 1, I can click an activex button and it will search for the desk number on sheet 2 in column A and then update the row (B:Q) with the data from sheet 1.
I have seen some VBA that will copy the data but it only copies to the next empty row of cells.
Here is the code that I have found but is just not right.
Sub MoveRowBasedOnCellValue()
Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim K As Long
I = Worksheets("Sheet1").UsedRange.Rows.Count
J = Worksheets("Sheet2").UsedRange.Rows.Count
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Sheet2").UsedRange) = 0 Then J = 0
End If
Set xRg = Worksheets("Sheet1").Range("A5:Q5" & I)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.Count
If CStr(xRg(K).Value) = ("A5") Then
xRg(K).EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A" & J + 1)
J = J + 1
End If
Next
Application.ScreenUpdating = True
End Sub
Any help would be great!
Thanks.
vba
edited Jan 2 at 23:04
Wizhi
3,44411131
asked Jan 2 at 22:54
YellowPantherYellowPanther
1
add a comment |
I am new to stackoverflow.com and VBA. I have been searching the web for a VBA that will allow me to copy data from sheet 1 that I input and then paste into sheet 2 based off the a cell value match. Once it is copied, it would then clear the data on Sheet 1 without delete the rows.
I work in a call center, and this would be to update equipment based on the desk it is located at.
So I am hoping that once I input all the data into the fields on sheet 1, I can click an activex button and it will search for the desk number on sheet 2 in column A and then update the row (B:Q) with the data from sheet 1.
I have seen some VBA that will copy the data but it only copies to the next empty row of cells.
Here is the code that I have found but is just not right.
Sub MoveRowBasedOnCellValue()
Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim K As Long
I = Worksheets("Sheet1").UsedRange.Rows.Count
J = Worksheets("Sheet2").UsedRange.Rows.Count
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Sheet2").UsedRange) = 0 Then J = 0
End If
Set xRg = Worksheets("Sheet1").Range("A5:Q5" & I)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.Count
If CStr(xRg(K).Value) = ("A5") Then
xRg(K).EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A" & J + 1)
J = J + 1
End If
Next
Application.ScreenUpdating = True
End Sub
Any help would be great!
Thanks.
vba
edited Jan 2 at 23:04
Wizhi
3,44411131
asked Jan 2 at 22:54
YellowPantherYellowPanther
1
add a comment |
I am new to stackoverflow.com and VBA. I have been searching the web for a VBA that will allow me to copy data from sheet 1 that I input and then paste into sheet 2 based off the a cell value match. Once it is copied, it would then clear the data on Sheet 1 without delete the rows.
I work in a call center, and this would be to update equipment based on the desk it is located at.
So I am hoping that once I input all the data into the fields on sheet 1, I can click an activex button and it will search for the desk number on sheet 2 in column A and then update the row (B:Q) with the data from sheet 1.
I have seen some VBA that will copy the data but it only copies to the next empty row of cells.
Here is the code that I have found but is just not right.
Sub MoveRowBasedOnCellValue()
Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim K As Long
I = Worksheets("Sheet1").UsedRange.Rows.Count
J = Worksheets("Sheet2").UsedRange.Rows.Count
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Sheet2").UsedRange) = 0 Then J = 0
End If
Set xRg = Worksheets("Sheet1").Range("A5:Q5" & I)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.Count
If CStr(xRg(K).Value) = ("A5") Then
xRg(K).EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A" & J + 1)
J = J + 1
End If
Next
Application.ScreenUpdating = True
End Sub
Any help would be great!
Thanks.
vba
edited Jan 2 at 23:04
Wizhi
3,44411131
asked Jan 2 at 22:54
YellowPantherYellowPanther
1
I am new to stackoverflow.com and VBA. I have been searching the web for a VBA that will allow me to copy data from sheet 1 that I input and then paste into sheet 2 based off the a cell value match. Once it is copied, it would then clear the data on Sheet 1 without delete the rows.
I work in a call center, and this would be to update equipment based on the desk it is located at.
So I am hoping that once I input all the data into the fields on sheet 1, I can click an activex button and it will search for the desk number on sheet 2 in column A and then update the row (B:Q) with the data from sheet 1.
I have seen some VBA that will copy the data but it only copies to the next empty row of cells.
Here is the code that I have found but is just not right.
Sub MoveRowBasedOnCellValue()
Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim K As Long
I = Worksheets("Sheet1").UsedRange.Rows.Count
J = Worksheets("Sheet2").UsedRange.Rows.Count
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Sheet2").UsedRange) = 0 Then J = 0
End If
Set xRg = Worksheets("Sheet1").Range("A5:Q5" & I)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.Count
If CStr(xRg(K).Value) = ("A5") Then
xRg(K).EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A" & J + 1)
J = J + 1
End If
Next
Application.ScreenUpdating = True
End Sub
Any help would be great!
Thanks.
vba
vba
edited Jan 2 at 23:04
Wizhi
3,44411131
asked Jan 2 at 22:54
YellowPantherYellowPanther
1
edited Jan 2 at 23:04
Wizhi
3,44411131
asked Jan 2 at 22:54
YellowPantherYellowPanther
1
edited Jan 2 at 23:04
Wizhi
3,44411131
edited Jan 2 at 23:04
Wizhi
3,44411131
edited Jan 2 at 23:04
Wizhi
3,44411131
3,44411131
asked Jan 2 at 22:54
YellowPantherYellowPanther
1
asked Jan 2 at 22:54
YellowPantherYellowPanther
1
asked Jan 2 at 22:54
YellowPantherYellowPanther
1
1
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Something like the code below? I assumed the desk number are on column A sheet1 starting at row 2. You will need to adjust the end rows for both sheet though.
Sub MoveRowBasedOnCellValue()
Dim s1 As Sheet1
Set s1 = Sheet1
Dim s2 As Sheet2
Set s2 = Sheet2
Dim s1StartRow As Integer
Dim s1EndRow As Integer
Dim s2StartRow As Integer
Dim s2EndRow As Integer
s1StartRow = 2
s1EndRow = 8
s2StartRow = 2
s2EndRow = 10
Application.ScreenUpdating = False
For i = s1StartRow To s1EndRow
For j = s2StartRow To s2EndRow
If s1.Cells(i, 1) = s2.Cells(j, 1) Then
s1.Range("B" & i & ":Q" & i).Copy
s2.Cells(j, 2).PasteSpecial xlPasteAll
Application.CutCopyMode = False
End If
Next j
Next i
Application.ScreenUpdating = True
End Sub
answered Jan 3 at 4:25
RCLRCL
26028
I applied the above code and made the adjustment. Here is how I currently have it.
– YellowPanther
Jan 3 at 13:16
Sorry, New to the site, didn't know about the 5 minute update rule here. Thank you for your input. This comes back with a Run-time Error '424': Object required message. The desk number is in Column A row 5 only for Sheet1 (Update) and Column A Row 3:1009 for Sheet2 (Floor). I have also change s1.Range("B"....to s1.Range ("A"....along with Row start/end to 1/17 (A:Q).
– YellowPanther
Jan 3 at 13:23
Hi if your Column A row starts at 5 then your rowstart for sheet1 should start at 5 also. I won't be able to troubleshoot the Error without seeing your full code. If you copy and paste the code above without replacing anything it returns an error for you?
– RCL
Jan 3 at 21:58
Yes, Thank you so very much. I miss read the code at first but now I have it working. Thanks again. Super helpful.
– YellowPanther
Jan 4 at 14:46
Happy to help, and welcome to Stack Overflow. If this answer or any other one solved your issue, please mark it as accepted.
– RCL
Jan 5 at 0:29
add a comment |
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%2f54014243%2factivex-button-to-copy-a-row-of-data-from-sheet-1-to-sheet-2-based-off-cell-valu%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Something like the code below? I assumed the desk number are on column A sheet1 starting at row 2. You will need to adjust the end rows for both sheet though.
Sub MoveRowBasedOnCellValue()
Dim s1 As Sheet1
Set s1 = Sheet1
Dim s2 As Sheet2
Set s2 = Sheet2
Dim s1StartRow As Integer
Dim s1EndRow As Integer
Dim s2StartRow As Integer
Dim s2EndRow As Integer
s1StartRow = 2
s1EndRow = 8
s2StartRow = 2
s2EndRow = 10
Application.ScreenUpdating = False
For i = s1StartRow To s1EndRow
For j = s2StartRow To s2EndRow
If s1.Cells(i, 1) = s2.Cells(j, 1) Then
s1.Range("B" & i & ":Q" & i).Copy
s2.Cells(j, 2).PasteSpecial xlPasteAll
Application.CutCopyMode = False
End If
Next j
Next i
Application.ScreenUpdating = True
End Sub
answered Jan 3 at 4:25
RCLRCL
26028
I applied the above code and made the adjustment. Here is how I currently have it.
– YellowPanther
Jan 3 at 13:16
Sorry, New to the site, didn't know about the 5 minute update rule here. Thank you for your input. This comes back with a Run-time Error '424': Object required message. The desk number is in Column A row 5 only for Sheet1 (Update) and Column A Row 3:1009 for Sheet2 (Floor). I have also change s1.Range("B"....to s1.Range ("A"....along with Row start/end to 1/17 (A:Q).
– YellowPanther
Jan 3 at 13:23
Hi if your Column A row starts at 5 then your rowstart for sheet1 should start at 5 also. I won't be able to troubleshoot the Error without seeing your full code. If you copy and paste the code above without replacing anything it returns an error for you?
– RCL
Jan 3 at 21:58
Yes, Thank you so very much. I miss read the code at first but now I have it working. Thanks again. Super helpful.
– YellowPanther
Jan 4 at 14:46
Happy to help, and welcome to Stack Overflow. If this answer or any other one solved your issue, please mark it as accepted.
– RCL
Jan 5 at 0:29
add a comment |
Something like the code below? I assumed the desk number are on column A sheet1 starting at row 2. You will need to adjust the end rows for both sheet though.
Sub MoveRowBasedOnCellValue()
Dim s1 As Sheet1
Set s1 = Sheet1
Dim s2 As Sheet2
Set s2 = Sheet2
Dim s1StartRow As Integer
Dim s1EndRow As Integer
Dim s2StartRow As Integer
Dim s2EndRow As Integer
s1StartRow = 2
s1EndRow = 8
s2StartRow = 2
s2EndRow = 10
Application.ScreenUpdating = False
For i = s1StartRow To s1EndRow
For j = s2StartRow To s2EndRow
If s1.Cells(i, 1) = s2.Cells(j, 1) Then
s1.Range("B" & i & ":Q" & i).Copy
s2.Cells(j, 2).PasteSpecial xlPasteAll
Application.CutCopyMode = False
End If
Next j
Next i
Application.ScreenUpdating = True
End Sub
answered Jan 3 at 4:25
RCLRCL
26028
I applied the above code and made the adjustment. Here is how I currently have it.
– YellowPanther
Jan 3 at 13:16
Sorry, New to the site, didn't know about the 5 minute update rule here. Thank you for your input. This comes back with a Run-time Error '424': Object required message. The desk number is in Column A row 5 only for Sheet1 (Update) and Column A Row 3:1009 for Sheet2 (Floor). I have also change s1.Range("B"....to s1.Range ("A"....along with Row start/end to 1/17 (A:Q).
– YellowPanther
Jan 3 at 13:23
Hi if your Column A row starts at 5 then your rowstart for sheet1 should start at 5 also. I won't be able to troubleshoot the Error without seeing your full code. If you copy and paste the code above without replacing anything it returns an error for you?
– RCL
Jan 3 at 21:58
Yes, Thank you so very much. I miss read the code at first but now I have it working. Thanks again. Super helpful.
– YellowPanther
Jan 4 at 14:46
Happy to help, and welcome to Stack Overflow. If this answer or any other one solved your issue, please mark it as accepted.
– RCL
Jan 5 at 0:29
add a comment |
Something like the code below? I assumed the desk number are on column A sheet1 starting at row 2. You will need to adjust the end rows for both sheet though.
Sub MoveRowBasedOnCellValue()
Dim s1 As Sheet1
Set s1 = Sheet1
Dim s2 As Sheet2
Set s2 = Sheet2
Dim s1StartRow As Integer
Dim s1EndRow As Integer
Dim s2StartRow As Integer
Dim s2EndRow As Integer
s1StartRow = 2
s1EndRow = 8
s2StartRow = 2
s2EndRow = 10
Application.ScreenUpdating = False
For i = s1StartRow To s1EndRow
For j = s2StartRow To s2EndRow
If s1.Cells(i, 1) = s2.Cells(j, 1) Then
s1.Range("B" & i & ":Q" & i).Copy
s2.Cells(j, 2).PasteSpecial xlPasteAll
Application.CutCopyMode = False
End If
Next j
Next i
Application.ScreenUpdating = True
End Sub
answered Jan 3 at 4:25
RCLRCL
26028
Something like the code below? I assumed the desk number are on column A sheet1 starting at row 2. You will need to adjust the end rows for both sheet though.
Sub MoveRowBasedOnCellValue()
Dim s1 As Sheet1
Set s1 = Sheet1
Dim s2 As Sheet2
Set s2 = Sheet2
Dim s1StartRow As Integer
Dim s1EndRow As Integer
Dim s2StartRow As Integer
Dim s2EndRow As Integer
s1StartRow = 2
s1EndRow = 8
s2StartRow = 2
s2EndRow = 10
Application.ScreenUpdating = False
For i = s1StartRow To s1EndRow
For j = s2StartRow To s2EndRow
If s1.Cells(i, 1) = s2.Cells(j, 1) Then
s1.Range("B" & i & ":Q" & i).Copy
s2.Cells(j, 2).PasteSpecial xlPasteAll
Application.CutCopyMode = False
End If
Next j
Next i
Application.ScreenUpdating = True
End Sub
answered Jan 3 at 4:25
RCLRCL
26028
answered Jan 3 at 4:25
RCLRCL
26028
answered Jan 3 at 4:25
RCLRCL
26028
answered Jan 3 at 4:25
RCLRCL
26028
26028
I applied the above code and made the adjustment. Here is how I currently have it.
– YellowPanther
Jan 3 at 13:16
Sorry, New to the site, didn't know about the 5 minute update rule here. Thank you for your input. This comes back with a Run-time Error '424': Object required message. The desk number is in Column A row 5 only for Sheet1 (Update) and Column A Row 3:1009 for Sheet2 (Floor). I have also change s1.Range("B"....to s1.Range ("A"....along with Row start/end to 1/17 (A:Q).
– YellowPanther
Jan 3 at 13:23
Hi if your Column A row starts at 5 then your rowstart for sheet1 should start at 5 also. I won't be able to troubleshoot the Error without seeing your full code. If you copy and paste the code above without replacing anything it returns an error for you?
– RCL
Jan 3 at 21:58
Yes, Thank you so very much. I miss read the code at first but now I have it working. Thanks again. Super helpful.
– YellowPanther
Jan 4 at 14:46
Happy to help, and welcome to Stack Overflow. If this answer or any other one solved your issue, please mark it as accepted.
– RCL
Jan 5 at 0:29
add a comment |
I applied the above code and made the adjustment. Here is how I currently have it.
– YellowPanther
Jan 3 at 13:16
Sorry, New to the site, didn't know about the 5 minute update rule here. Thank you for your input. This comes back with a Run-time Error '424': Object required message. The desk number is in Column A row 5 only for Sheet1 (Update) and Column A Row 3:1009 for Sheet2 (Floor). I have also change s1.Range("B"....to s1.Range ("A"....along with Row start/end to 1/17 (A:Q).
– YellowPanther
Jan 3 at 13:23
Hi if your Column A row starts at 5 then your rowstart for sheet1 should start at 5 also. I won't be able to troubleshoot the Error without seeing your full code. If you copy and paste the code above without replacing anything it returns an error for you?
– RCL
Jan 3 at 21:58
Yes, Thank you so very much. I miss read the code at first but now I have it working. Thanks again. Super helpful.
– YellowPanther
Jan 4 at 14:46
Happy to help, and welcome to Stack Overflow. If this answer or any other one solved your issue, please mark it as accepted.
– RCL
Jan 5 at 0:29
I applied the above code and made the adjustment. Here is how I currently have it.
– YellowPanther
Jan 3 at 13:16
I applied the above code and made the adjustment. Here is how I currently have it.
– YellowPanther
Jan 3 at 13:16
Sorry, New to the site, didn't know about the 5 minute update rule here. Thank you for your input. This comes back with a Run-time Error '424': Object required message. The desk number is in Column A row 5 only for Sheet1 (Update) and Column A Row 3:1009 for Sheet2 (Floor). I have also change s1.Range("B"....to s1.Range ("A"....along with Row start/end to 1/17 (A:Q).
– YellowPanther
Jan 3 at 13:23
Sorry, New to the site, didn't know about the 5 minute update rule here. Thank you for your input. This comes back with a Run-time Error '424': Object required message. The desk number is in Column A row 5 only for Sheet1 (Update) and Column A Row 3:1009 for Sheet2 (Floor). I have also change s1.Range("B"....to s1.Range ("A"....along with Row start/end to 1/17 (A:Q).
– YellowPanther
Jan 3 at 13:23
Hi if your Column A row starts at 5 then your rowstart for sheet1 should start at 5 also. I won't be able to troubleshoot the Error without seeing your full code. If you copy and paste the code above without replacing anything it returns an error for you?
– RCL
Jan 3 at 21:58
Hi if your Column A row starts at 5 then your rowstart for sheet1 should start at 5 also. I won't be able to troubleshoot the Error without seeing your full code. If you copy and paste the code above without replacing anything it returns an error for you?
– RCL
Jan 3 at 21:58
Yes, Thank you so very much. I miss read the code at first but now I have it working. Thanks again. Super helpful.
– YellowPanther
Jan 4 at 14:46
Yes, Thank you so very much. I miss read the code at first but now I have it working. Thanks again. Super helpful.
– YellowPanther
Jan 4 at 14:46
Happy to help, and welcome to Stack Overflow. If this answer or any other one solved your issue, please mark it as accepted.
– RCL
Jan 5 at 0:29
Happy to help, and welcome to Stack Overflow. If this answer or any other one solved your issue, please mark it as accepted.
– RCL
Jan 5 at 0:29
add a comment |
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%2f54014243%2factivex-button-to-copy-a-row-of-data-from-sheet-1-to-sheet-2-based-off-cell-valu%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
