Copying and pasting to a worksheet who's name does not equal a value within a list?
2
I am trying to copy and paste information to certain worksheets. Most of the worksheet names I already know, but there could be one new worksheet added in, which I wouldn't know the name of.
There are 3 worksheets (let's call them WorkSheet1, WorkSheet2, and WorkSheet3) that I don't want to change. I want to copy some values from WorkSheet2 and paste them in all worksheets except 1-3. So far, I have an IF statement with the worksheets I know the names of (WorksheetX,Y, and Z). I am having trouble with the last instance where I wouldn't know the worksheet's name. I have been thinking of using an Or. Here is what I have so far:
Dim ws As Worksheet
Dim og As Worksheet
Set og = Sheets("WorkSheet2")
For Each ws In Worksheets
If ws.Name = "WorkSheetX" Then
og.Range("A1").Copy
Sheets("WorkSheetX").Range("L4").PasteSpecial
ElseIf ws.Name = "WorkSheetY" Then
og.Range("A1").Copy
Sheets("WorkSheetY").Range("L4").PasteSpecial
ElseIf ws.Name = "WorkSheetZ" Then
og.Range("A1").Copy
Sheets("WorkSheetZ").Range("L4").PasteSpecial
'This is where I am having trouble. I won't know the name of the new sheet
'Just that it wont be WorkSheet1,2,3,X,Y, or Z
ElseIf (ws.Name <> "WorkSheet1" Or ws.Name <> "WorkSheet2"
Or ws.Name <> "WorkSheet3" Or ws.Name <> "WorkSheetX" Or
ws.Name <> "WorkSheetY" Or ws.Name <> "WorkSheetZ") Then
og.Range("A1").Copy
ws.Range("L4").PasteSpecial
End If
Next
excel vba excel-vba
asked Nov 24 '18 at 4:01
tetrikertetriker
274
add a comment |
2
I am trying to copy and paste information to certain worksheets. Most of the worksheet names I already know, but there could be one new worksheet added in, which I wouldn't know the name of.
There are 3 worksheets (let's call them WorkSheet1, WorkSheet2, and WorkSheet3) that I don't want to change. I want to copy some values from WorkSheet2 and paste them in all worksheets except 1-3. So far, I have an IF statement with the worksheets I know the names of (WorksheetX,Y, and Z). I am having trouble with the last instance where I wouldn't know the worksheet's name. I have been thinking of using an Or. Here is what I have so far:
Dim ws As Worksheet
Dim og As Worksheet
Set og = Sheets("WorkSheet2")
For Each ws In Worksheets
If ws.Name = "WorkSheetX" Then
og.Range("A1").Copy
Sheets("WorkSheetX").Range("L4").PasteSpecial
ElseIf ws.Name = "WorkSheetY" Then
og.Range("A1").Copy
Sheets("WorkSheetY").Range("L4").PasteSpecial
ElseIf ws.Name = "WorkSheetZ" Then
og.Range("A1").Copy
Sheets("WorkSheetZ").Range("L4").PasteSpecial
'This is where I am having trouble. I won't know the name of the new sheet
'Just that it wont be WorkSheet1,2,3,X,Y, or Z
ElseIf (ws.Name <> "WorkSheet1" Or ws.Name <> "WorkSheet2"
Or ws.Name <> "WorkSheet3" Or ws.Name <> "WorkSheetX" Or
ws.Name <> "WorkSheetY" Or ws.Name <> "WorkSheetZ") Then
og.Range("A1").Copy
ws.Range("L4").PasteSpecial
End If
Next
excel vba excel-vba
asked Nov 24 '18 at 4:01
tetrikertetriker
274
Are the 4 worksheet the only ones in the workbook?
– GMalc
Nov 24 '18 at 4:04
You could use the index number or CodeName.
– GMalc
Nov 24 '18 at 4:06
There could be anywhere from 4 to 12 worksheets. I know the names for all except one, but they might not all be included in the workbook. So I'm not sure if the index would work, because the amount of known sheets is variable
– tetriker
Nov 24 '18 at 4:10
Loop through all the worksheets in the workbook, Use an IF OR statement to test all worksheets. (Don't include the 4 worksheet names you want). e.g.For each worksheet in workbookIf worksheet is not ws1, or ws2, or etc.Then do your copy.
– GMalc
Nov 24 '18 at 4:20
How should I write out the paste line? I tried this and the issue is that pastes it on the new sheet, but also pastes it on all worksheets. I do the for each ws in worksheets, then an IF with an OR, then the copy then ws.Range("L4").PasteSpecial
– tetriker
Nov 24 '18 at 4:49
add a comment |
2
2
2
I am trying to copy and paste information to certain worksheets. Most of the worksheet names I already know, but there could be one new worksheet added in, which I wouldn't know the name of.
There are 3 worksheets (let's call them WorkSheet1, WorkSheet2, and WorkSheet3) that I don't want to change. I want to copy some values from WorkSheet2 and paste them in all worksheets except 1-3. So far, I have an IF statement with the worksheets I know the names of (WorksheetX,Y, and Z). I am having trouble with the last instance where I wouldn't know the worksheet's name. I have been thinking of using an Or. Here is what I have so far:
Dim ws As Worksheet
Dim og As Worksheet
Set og = Sheets("WorkSheet2")
For Each ws In Worksheets
If ws.Name = "WorkSheetX" Then
og.Range("A1").Copy
Sheets("WorkSheetX").Range("L4").PasteSpecial
ElseIf ws.Name = "WorkSheetY" Then
og.Range("A1").Copy
Sheets("WorkSheetY").Range("L4").PasteSpecial
ElseIf ws.Name = "WorkSheetZ" Then
og.Range("A1").Copy
Sheets("WorkSheetZ").Range("L4").PasteSpecial
'This is where I am having trouble. I won't know the name of the new sheet
'Just that it wont be WorkSheet1,2,3,X,Y, or Z
ElseIf (ws.Name <> "WorkSheet1" Or ws.Name <> "WorkSheet2"
Or ws.Name <> "WorkSheet3" Or ws.Name <> "WorkSheetX" Or
ws.Name <> "WorkSheetY" Or ws.Name <> "WorkSheetZ") Then
og.Range("A1").Copy
ws.Range("L4").PasteSpecial
End If
Next
excel vba excel-vba
asked Nov 24 '18 at 4:01
tetrikertetriker
274
I am trying to copy and paste information to certain worksheets. Most of the worksheet names I already know, but there could be one new worksheet added in, which I wouldn't know the name of.
There are 3 worksheets (let's call them WorkSheet1, WorkSheet2, and WorkSheet3) that I don't want to change. I want to copy some values from WorkSheet2 and paste them in all worksheets except 1-3. So far, I have an IF statement with the worksheets I know the names of (WorksheetX,Y, and Z). I am having trouble with the last instance where I wouldn't know the worksheet's name. I have been thinking of using an Or. Here is what I have so far:
Dim ws As Worksheet
Dim og As Worksheet
Set og = Sheets("WorkSheet2")
For Each ws In Worksheets
If ws.Name = "WorkSheetX" Then
og.Range("A1").Copy
Sheets("WorkSheetX").Range("L4").PasteSpecial
ElseIf ws.Name = "WorkSheetY" Then
og.Range("A1").Copy
Sheets("WorkSheetY").Range("L4").PasteSpecial
ElseIf ws.Name = "WorkSheetZ" Then
og.Range("A1").Copy
Sheets("WorkSheetZ").Range("L4").PasteSpecial
'This is where I am having trouble. I won't know the name of the new sheet
'Just that it wont be WorkSheet1,2,3,X,Y, or Z
ElseIf (ws.Name <> "WorkSheet1" Or ws.Name <> "WorkSheet2"
Or ws.Name <> "WorkSheet3" Or ws.Name <> "WorkSheetX" Or
ws.Name <> "WorkSheetY" Or ws.Name <> "WorkSheetZ") Then
og.Range("A1").Copy
ws.Range("L4").PasteSpecial
End If
Next
excel vba excel-vba
excel vba excel-vba
asked Nov 24 '18 at 4:01
tetrikertetriker
274
asked Nov 24 '18 at 4:01
tetrikertetriker
274
asked Nov 24 '18 at 4:01
tetrikertetriker
274
asked Nov 24 '18 at 4:01
tetrikertetriker
274
asked Nov 24 '18 at 4:01
tetrikertetriker
274
274
Are the 4 worksheet the only ones in the workbook?
– GMalc
Nov 24 '18 at 4:04
You could use the index number or CodeName.
– GMalc
Nov 24 '18 at 4:06
There could be anywhere from 4 to 12 worksheets. I know the names for all except one, but they might not all be included in the workbook. So I'm not sure if the index would work, because the amount of known sheets is variable
– tetriker
Nov 24 '18 at 4:10
Loop through all the worksheets in the workbook, Use an IF OR statement to test all worksheets. (Don't include the 4 worksheet names you want). e.g.For each worksheet in workbookIf worksheet is not ws1, or ws2, or etc.Then do your copy.
– GMalc
Nov 24 '18 at 4:20
How should I write out the paste line? I tried this and the issue is that pastes it on the new sheet, but also pastes it on all worksheets. I do the for each ws in worksheets, then an IF with an OR, then the copy then ws.Range("L4").PasteSpecial
– tetriker
Nov 24 '18 at 4:49
add a comment |
Are the 4 worksheet the only ones in the workbook?
– GMalc
Nov 24 '18 at 4:04
You could use the index number or CodeName.
– GMalc
Nov 24 '18 at 4:06
There could be anywhere from 4 to 12 worksheets. I know the names for all except one, but they might not all be included in the workbook. So I'm not sure if the index would work, because the amount of known sheets is variable
– tetriker
Nov 24 '18 at 4:10
Loop through all the worksheets in the workbook, Use an IF OR statement to test all worksheets. (Don't include the 4 worksheet names you want). e.g.For each worksheet in workbookIf worksheet is not ws1, or ws2, or etc.Then do your copy.
– GMalc
Nov 24 '18 at 4:20
How should I write out the paste line? I tried this and the issue is that pastes it on the new sheet, but also pastes it on all worksheets. I do the for each ws in worksheets, then an IF with an OR, then the copy then ws.Range("L4").PasteSpecial
– tetriker
Nov 24 '18 at 4:49
Are the 4 worksheet the only ones in the workbook?
– GMalc
Nov 24 '18 at 4:04
Are the 4 worksheet the only ones in the workbook?
– GMalc
Nov 24 '18 at 4:04
You could use the index number or CodeName.
– GMalc
Nov 24 '18 at 4:06
You could use the index number or CodeName.
– GMalc
Nov 24 '18 at 4:06
There could be anywhere from 4 to 12 worksheets. I know the names for all except one, but they might not all be included in the workbook. So I'm not sure if the index would work, because the amount of known sheets is variable
– tetriker
Nov 24 '18 at 4:10
There could be anywhere from 4 to 12 worksheets. I know the names for all except one, but they might not all be included in the workbook. So I'm not sure if the index would work, because the amount of known sheets is variable
– tetriker
Nov 24 '18 at 4:10
Loop through all the worksheets in the workbook, Use an IF OR statement to test all worksheets. (Don't include the 4 worksheet names you want). e.g.
For each worksheet in workbook If worksheet is not ws1, or ws2, or etc. Then do your copy.– GMalc
Nov 24 '18 at 4:20
Loop through all the worksheets in the workbook, Use an IF OR statement to test all worksheets. (Don't include the 4 worksheet names you want). e.g.
For each worksheet in workbook If worksheet is not ws1, or ws2, or etc. Then do your copy.– GMalc
Nov 24 '18 at 4:20
How should I write out the paste line? I tried this and the issue is that pastes it on the new sheet, but also pastes it on all worksheets. I do the for each ws in worksheets, then an IF with an OR, then the copy then ws.Range("L4").PasteSpecial
– tetriker
Nov 24 '18 at 4:49
How should I write out the paste line? I tried this and the issue is that pastes it on the new sheet, but also pastes it on all worksheets. I do the for each ws in worksheets, then an IF with an OR, then the copy then ws.Range("L4").PasteSpecial
– tetriker
Nov 24 '18 at 4:49
add a comment |
2 Answers
2
active
oldest
votes
2
Here is a simple code to accomplish your task.
Dim ws As Worksheet
'Change the sheet names you don't want to perform you copy_paste
Sheets(Array("Sheet1", "Sheet2", "Sheet4", "Sheet_n")).Visible = False
For Each ws In Application.ThisWorkbook.Worksheets
If ws.Visible = True Then
ws.Range("L4").Value = ws.Range("A1").Value
End If
Next ws
Sheets(Array("Sheet1", "Sheet2", "Sheet4", "Sheet_n")).Visible = True
answered Nov 24 '18 at 5:21
GMalcGMalc
1,0661410
This also works, thanks
– tetriker
Nov 25 '18 at 3:37
add a comment |
1
A Few Worksheets
Option Explicit
Sub AFewWorksheets()
Dim ws As Worksheet
Dim og As Worksheet
Set og = Sheets("WorkSheet2")
For Each ws In Worksheets
Select Case ws.Name
Case "Worksheet1", "Worksheet2", "Worksheet3"
Case Else
og.Range("A1").Copy
ws.Range("L4").PasteSpecial
End Select
Next
End Sub
answered Nov 24 '18 at 5:09
VBasic2008VBasic2008
3,0422416
This works perfectly, thank you so much!
– tetriker
Nov 24 '18 at 5:19
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
});
}
});
draft saved
draft discarded
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%2f53455047%2fcopying-and-pasting-to-a-worksheet-whos-name-does-not-equal-a-value-within-a-li%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
2
Here is a simple code to accomplish your task.
Dim ws As Worksheet
'Change the sheet names you don't want to perform you copy_paste
Sheets(Array("Sheet1", "Sheet2", "Sheet4", "Sheet_n")).Visible = False
For Each ws In Application.ThisWorkbook.Worksheets
If ws.Visible = True Then
ws.Range("L4").Value = ws.Range("A1").Value
End If
Next ws
Sheets(Array("Sheet1", "Sheet2", "Sheet4", "Sheet_n")).Visible = True
answered Nov 24 '18 at 5:21
GMalcGMalc
1,0661410
This also works, thanks
– tetriker
Nov 25 '18 at 3:37
add a comment |
2
Here is a simple code to accomplish your task.
Dim ws As Worksheet
'Change the sheet names you don't want to perform you copy_paste
Sheets(Array("Sheet1", "Sheet2", "Sheet4", "Sheet_n")).Visible = False
For Each ws In Application.ThisWorkbook.Worksheets
If ws.Visible = True Then
ws.Range("L4").Value = ws.Range("A1").Value
End If
Next ws
Sheets(Array("Sheet1", "Sheet2", "Sheet4", "Sheet_n")).Visible = True
answered Nov 24 '18 at 5:21
GMalcGMalc
1,0661410
This also works, thanks
– tetriker
Nov 25 '18 at 3:37
add a comment |
2
2
2
Here is a simple code to accomplish your task.
Dim ws As Worksheet
'Change the sheet names you don't want to perform you copy_paste
Sheets(Array("Sheet1", "Sheet2", "Sheet4", "Sheet_n")).Visible = False
For Each ws In Application.ThisWorkbook.Worksheets
If ws.Visible = True Then
ws.Range("L4").Value = ws.Range("A1").Value
End If
Next ws
Sheets(Array("Sheet1", "Sheet2", "Sheet4", "Sheet_n")).Visible = True
answered Nov 24 '18 at 5:21
GMalcGMalc
1,0661410
Here is a simple code to accomplish your task.
Dim ws As Worksheet
'Change the sheet names you don't want to perform you copy_paste
Sheets(Array("Sheet1", "Sheet2", "Sheet4", "Sheet_n")).Visible = False
For Each ws In Application.ThisWorkbook.Worksheets
If ws.Visible = True Then
ws.Range("L4").Value = ws.Range("A1").Value
End If
Next ws
Sheets(Array("Sheet1", "Sheet2", "Sheet4", "Sheet_n")).Visible = True
answered Nov 24 '18 at 5:21
GMalcGMalc
1,0661410
answered Nov 24 '18 at 5:21
GMalcGMalc
1,0661410
answered Nov 24 '18 at 5:21
GMalcGMalc
1,0661410
answered Nov 24 '18 at 5:21
GMalcGMalc
1,0661410
1,0661410
This also works, thanks
– tetriker
Nov 25 '18 at 3:37
add a comment |
This also works, thanks
– tetriker
Nov 25 '18 at 3:37
This also works, thanks
– tetriker
Nov 25 '18 at 3:37
This also works, thanks
– tetriker
Nov 25 '18 at 3:37
add a comment |
1
A Few Worksheets
Option Explicit
Sub AFewWorksheets()
Dim ws As Worksheet
Dim og As Worksheet
Set og = Sheets("WorkSheet2")
For Each ws In Worksheets
Select Case ws.Name
Case "Worksheet1", "Worksheet2", "Worksheet3"
Case Else
og.Range("A1").Copy
ws.Range("L4").PasteSpecial
End Select
Next
End Sub
answered Nov 24 '18 at 5:09
VBasic2008VBasic2008
3,0422416
This works perfectly, thank you so much!
– tetriker
Nov 24 '18 at 5:19
add a comment |
1
A Few Worksheets
Option Explicit
Sub AFewWorksheets()
Dim ws As Worksheet
Dim og As Worksheet
Set og = Sheets("WorkSheet2")
For Each ws In Worksheets
Select Case ws.Name
Case "Worksheet1", "Worksheet2", "Worksheet3"
Case Else
og.Range("A1").Copy
ws.Range("L4").PasteSpecial
End Select
Next
End Sub
answered Nov 24 '18 at 5:09
VBasic2008VBasic2008
3,0422416
This works perfectly, thank you so much!
– tetriker
Nov 24 '18 at 5:19
add a comment |
1
1
1
A Few Worksheets
Option Explicit
Sub AFewWorksheets()
Dim ws As Worksheet
Dim og As Worksheet
Set og = Sheets("WorkSheet2")
For Each ws In Worksheets
Select Case ws.Name
Case "Worksheet1", "Worksheet2", "Worksheet3"
Case Else
og.Range("A1").Copy
ws.Range("L4").PasteSpecial
End Select
Next
End Sub
answered Nov 24 '18 at 5:09
VBasic2008VBasic2008
3,0422416
A Few Worksheets
Option Explicit
Sub AFewWorksheets()
Dim ws As Worksheet
Dim og As Worksheet
Set og = Sheets("WorkSheet2")
For Each ws In Worksheets
Select Case ws.Name
Case "Worksheet1", "Worksheet2", "Worksheet3"
Case Else
og.Range("A1").Copy
ws.Range("L4").PasteSpecial
End Select
Next
End Sub
answered Nov 24 '18 at 5:09
VBasic2008VBasic2008
3,0422416
answered Nov 24 '18 at 5:09
VBasic2008VBasic2008
3,0422416
answered Nov 24 '18 at 5:09
VBasic2008VBasic2008
3,0422416
answered Nov 24 '18 at 5:09
VBasic2008VBasic2008
3,0422416
3,0422416
This works perfectly, thank you so much!
– tetriker
Nov 24 '18 at 5:19
add a comment |
This works perfectly, thank you so much!
– tetriker
Nov 24 '18 at 5:19
This works perfectly, thank you so much!
– tetriker
Nov 24 '18 at 5:19
This works perfectly, thank you so much!
– tetriker
Nov 24 '18 at 5:19
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53455047%2fcopying-and-pasting-to-a-worksheet-whos-name-does-not-equal-a-value-within-a-li%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
Are the 4 worksheet the only ones in the workbook?
– GMalc
Nov 24 '18 at 4:04
You could use the index number or CodeName.
– GMalc
Nov 24 '18 at 4:06
There could be anywhere from 4 to 12 worksheets. I know the names for all except one, but they might not all be included in the workbook. So I'm not sure if the index would work, because the amount of known sheets is variable
– tetriker
Nov 24 '18 at 4:10
Loop through all the worksheets in the workbook, Use an IF OR statement to test all worksheets. (Don't include the 4 worksheet names you want). e.g.
For each worksheet in workbookIf worksheet is not ws1, or ws2, or etc.Then do your copy.– GMalc
Nov 24 '18 at 4:20
How should I write out the paste line? I tried this and the issue is that pastes it on the new sheet, but also pastes it on all worksheets. I do the for each ws in worksheets, then an IF with an OR, then the copy then ws.Range("L4").PasteSpecial
– tetriker
Nov 24 '18 at 4:49