Copy data from worksheets with partial name relation
I am trying to copy data from every sheet in my workbook that has "ANALYSIS E 000002" and then "ANALYSIS E 000002" in its name, up to "ANALYSIS E 000012". Some have a copy number after this string, i.e. "ANALYSIS E 000002 (3)".
So I started by doing a vba code that will take every sheet in the workbook and add their content in a combined one. It works perfectly but now I need to adapt it to select only the string I specify. I will need to do it for different names.
Started with this code :
Sub Combine_ea()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1:A2").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1:A2")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A3").Select
Selection.CurrentRegion.Select
Selection.Offset(2, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A99999").End(xlUp)(2)
Next
End Sub
The approach I think would be the best is to make a For loop to reach every string that finishes by my Iterator and then do my algorithm. I am not sure how to do adapt my code at this point. Would need help from you guys.
excel vba
asked Nov 14 '18 at 18:32
MrRarriMrRarri
275
add a comment |
I am trying to copy data from every sheet in my workbook that has "ANALYSIS E 000002" and then "ANALYSIS E 000002" in its name, up to "ANALYSIS E 000012". Some have a copy number after this string, i.e. "ANALYSIS E 000002 (3)".
So I started by doing a vba code that will take every sheet in the workbook and add their content in a combined one. It works perfectly but now I need to adapt it to select only the string I specify. I will need to do it for different names.
Started with this code :
Sub Combine_ea()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1:A2").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1:A2")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A3").Select
Selection.CurrentRegion.Select
Selection.Offset(2, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A99999").End(xlUp)(2)
Next
End Sub
The approach I think would be the best is to make a For loop to reach every string that finishes by my Iterator and then do my algorithm. I am not sure how to do adapt my code at this point. Would need help from you guys.
excel vba
asked Nov 14 '18 at 18:32
MrRarriMrRarri
275
add a comment |
I am trying to copy data from every sheet in my workbook that has "ANALYSIS E 000002" and then "ANALYSIS E 000002" in its name, up to "ANALYSIS E 000012". Some have a copy number after this string, i.e. "ANALYSIS E 000002 (3)".
So I started by doing a vba code that will take every sheet in the workbook and add their content in a combined one. It works perfectly but now I need to adapt it to select only the string I specify. I will need to do it for different names.
Started with this code :
Sub Combine_ea()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1:A2").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1:A2")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A3").Select
Selection.CurrentRegion.Select
Selection.Offset(2, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A99999").End(xlUp)(2)
Next
End Sub
The approach I think would be the best is to make a For loop to reach every string that finishes by my Iterator and then do my algorithm. I am not sure how to do adapt my code at this point. Would need help from you guys.
excel vba
asked Nov 14 '18 at 18:32
MrRarriMrRarri
275
I am trying to copy data from every sheet in my workbook that has "ANALYSIS E 000002" and then "ANALYSIS E 000002" in its name, up to "ANALYSIS E 000012". Some have a copy number after this string, i.e. "ANALYSIS E 000002 (3)".
So I started by doing a vba code that will take every sheet in the workbook and add their content in a combined one. It works perfectly but now I need to adapt it to select only the string I specify. I will need to do it for different names.
Started with this code :
Sub Combine_ea()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1:A2").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1:A2")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A3").Select
Selection.CurrentRegion.Select
Selection.Offset(2, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A99999").End(xlUp)(2)
Next
End Sub
The approach I think would be the best is to make a For loop to reach every string that finishes by my Iterator and then do my algorithm. I am not sure how to do adapt my code at this point. Would need help from you guys.
excel vba
excel vba
asked Nov 14 '18 at 18:32
MrRarriMrRarri
275
asked Nov 14 '18 at 18:32
MrRarriMrRarri
275
edited Nov 14 '18 at 18:45
MrRarri
asked Nov 14 '18 at 18:32
MrRarriMrRarri
275
asked Nov 14 '18 at 18:32
MrRarriMrRarri
275
asked Nov 14 '18 at 18:32
MrRarriMrRarri
275
275
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
You can use "like".
For example,
If Sheets(1).name like "Analisis*"
then
--Your code here
End If
answered Nov 14 '18 at 18:45
KarlomanioKarlomanio
30038
Exactly what I was looking for. Thank you sir!
– MrRarri
Nov 14 '18 at 19:19
add a comment |
Ok, as I see, you don´t need to paste all information in a sheet, you can use an input box for asking the user the name of the string he/she wants to look for, and then look for the sheets called that way, and then look for the range that has the word you are looking for. Here´s an example:
Sub example()
Dim res As String
Dim no As Integer
res = InputBox("Write the name you want to look for")
no = Len(res) 'gets the amount of characters in the string
For i = 2 To Worksheets.Count
If Left(Worksheets(i).Name, no) = res Then 'reads the name of the worksheet form left
to right
'gets the number of characters in the variable
no and compares the substring with the string
res
'if you are here it means the worksheets is called as you out in your input box
'from here you can do what you want, use the same structure to look inside the
cells
End If
End Sub
answered Nov 14 '18 at 19:12
Juan JoyaJuan Joya
544
I did something like this but without the part with the inputbox as there is no user in my situation. I made an IF statement for each string I want to compare and then looped in it so I can copy all data from worksheets to my main one.
– MrRarri
Nov 14 '18 at 19:18
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%2f53306673%2fcopy-data-from-worksheets-with-partial-name-relation%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can use "like".
For example,
If Sheets(1).name like "Analisis*"
then
--Your code here
End If
answered Nov 14 '18 at 18:45
KarlomanioKarlomanio
30038
Exactly what I was looking for. Thank you sir!
– MrRarri
Nov 14 '18 at 19:19
add a comment |
You can use "like".
For example,
If Sheets(1).name like "Analisis*"
then
--Your code here
End If
answered Nov 14 '18 at 18:45
KarlomanioKarlomanio
30038
Exactly what I was looking for. Thank you sir!
– MrRarri
Nov 14 '18 at 19:19
add a comment |
You can use "like".
For example,
If Sheets(1).name like "Analisis*"
then
--Your code here
End If
answered Nov 14 '18 at 18:45
KarlomanioKarlomanio
30038
You can use "like".
For example,
If Sheets(1).name like "Analisis*"
then
--Your code here
End If
answered Nov 14 '18 at 18:45
KarlomanioKarlomanio
30038
answered Nov 14 '18 at 18:45
KarlomanioKarlomanio
30038
answered Nov 14 '18 at 18:45
KarlomanioKarlomanio
30038
answered Nov 14 '18 at 18:45
KarlomanioKarlomanio
30038
30038
Exactly what I was looking for. Thank you sir!
– MrRarri
Nov 14 '18 at 19:19
add a comment |
Exactly what I was looking for. Thank you sir!
– MrRarri
Nov 14 '18 at 19:19
Exactly what I was looking for. Thank you sir!
– MrRarri
Nov 14 '18 at 19:19
Exactly what I was looking for. Thank you sir!
– MrRarri
Nov 14 '18 at 19:19
add a comment |
Ok, as I see, you don´t need to paste all information in a sheet, you can use an input box for asking the user the name of the string he/she wants to look for, and then look for the sheets called that way, and then look for the range that has the word you are looking for. Here´s an example:
Sub example()
Dim res As String
Dim no As Integer
res = InputBox("Write the name you want to look for")
no = Len(res) 'gets the amount of characters in the string
For i = 2 To Worksheets.Count
If Left(Worksheets(i).Name, no) = res Then 'reads the name of the worksheet form left
to right
'gets the number of characters in the variable
no and compares the substring with the string
res
'if you are here it means the worksheets is called as you out in your input box
'from here you can do what you want, use the same structure to look inside the
cells
End If
End Sub
answered Nov 14 '18 at 19:12
Juan JoyaJuan Joya
544
I did something like this but without the part with the inputbox as there is no user in my situation. I made an IF statement for each string I want to compare and then looped in it so I can copy all data from worksheets to my main one.
– MrRarri
Nov 14 '18 at 19:18
add a comment |
Ok, as I see, you don´t need to paste all information in a sheet, you can use an input box for asking the user the name of the string he/she wants to look for, and then look for the sheets called that way, and then look for the range that has the word you are looking for. Here´s an example:
Sub example()
Dim res As String
Dim no As Integer
res = InputBox("Write the name you want to look for")
no = Len(res) 'gets the amount of characters in the string
For i = 2 To Worksheets.Count
If Left(Worksheets(i).Name, no) = res Then 'reads the name of the worksheet form left
to right
'gets the number of characters in the variable
no and compares the substring with the string
res
'if you are here it means the worksheets is called as you out in your input box
'from here you can do what you want, use the same structure to look inside the
cells
End If
End Sub
answered Nov 14 '18 at 19:12
Juan JoyaJuan Joya
544
I did something like this but without the part with the inputbox as there is no user in my situation. I made an IF statement for each string I want to compare and then looped in it so I can copy all data from worksheets to my main one.
– MrRarri
Nov 14 '18 at 19:18
add a comment |
Ok, as I see, you don´t need to paste all information in a sheet, you can use an input box for asking the user the name of the string he/she wants to look for, and then look for the sheets called that way, and then look for the range that has the word you are looking for. Here´s an example:
Sub example()
Dim res As String
Dim no As Integer
res = InputBox("Write the name you want to look for")
no = Len(res) 'gets the amount of characters in the string
For i = 2 To Worksheets.Count
If Left(Worksheets(i).Name, no) = res Then 'reads the name of the worksheet form left
to right
'gets the number of characters in the variable
no and compares the substring with the string
res
'if you are here it means the worksheets is called as you out in your input box
'from here you can do what you want, use the same structure to look inside the
cells
End If
End Sub
answered Nov 14 '18 at 19:12
Juan JoyaJuan Joya
544
Ok, as I see, you don´t need to paste all information in a sheet, you can use an input box for asking the user the name of the string he/she wants to look for, and then look for the sheets called that way, and then look for the range that has the word you are looking for. Here´s an example:
Sub example()
Dim res As String
Dim no As Integer
res = InputBox("Write the name you want to look for")
no = Len(res) 'gets the amount of characters in the string
For i = 2 To Worksheets.Count
If Left(Worksheets(i).Name, no) = res Then 'reads the name of the worksheet form left
to right
'gets the number of characters in the variable
no and compares the substring with the string
res
'if you are here it means the worksheets is called as you out in your input box
'from here you can do what you want, use the same structure to look inside the
cells
End If
End Sub
answered Nov 14 '18 at 19:12
Juan JoyaJuan Joya
544
answered Nov 14 '18 at 19:12
Juan JoyaJuan Joya
544
answered Nov 14 '18 at 19:12
Juan JoyaJuan Joya
544
answered Nov 14 '18 at 19:12
Juan JoyaJuan Joya
544
544
I did something like this but without the part with the inputbox as there is no user in my situation. I made an IF statement for each string I want to compare and then looped in it so I can copy all data from worksheets to my main one.
– MrRarri
Nov 14 '18 at 19:18
add a comment |
I did something like this but without the part with the inputbox as there is no user in my situation. I made an IF statement for each string I want to compare and then looped in it so I can copy all data from worksheets to my main one.
– MrRarri
Nov 14 '18 at 19:18
I did something like this but without the part with the inputbox as there is no user in my situation. I made an IF statement for each string I want to compare and then looped in it so I can copy all data from worksheets to my main one.
– MrRarri
Nov 14 '18 at 19:18
I did something like this but without the part with the inputbox as there is no user in my situation. I made an IF statement for each string I want to compare and then looped in it so I can copy all data from worksheets to my main one.
– MrRarri
Nov 14 '18 at 19:18
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%2f53306673%2fcopy-data-from-worksheets-with-partial-name-relation%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
