Mixing
VBA loop code and have it recognize multiple sheets to copy to in Excel?
0
I'm attempting to figure out IF it's possible and if so, How to run my loop so that the first time it runs it copy/pastes into Sheet1 and the second time it runs it copy/pastes into Sheet 2. I was thinking maybe an array might be helpful but I'm not familiar with their use or syntax. Any help in the right direction would be appreciated!
For Each a In Range("2:2").SpecialCells(xlCellTypeFormulas).Areas
a.Resize(lrow - 1, a.Columns.Count).FillDown
With Worksheets("Fall 2016")
lrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
With .Range("A3:CU" & lrow)
On Error Resume Next
Set erng = .SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0
If Not erng Is Nothing Then
Intersect(.Parent.Range("A:CU"), erng.EntireRow).Copy
Worksheets("Sheet1").Range("A3").PasteSpecial
End If
End With
End With
Next a
excel vba excel-vba loops
asked Jan 2 at 15:25
DekeDeke
1339
|
show 3 more comments
0
I'm attempting to figure out IF it's possible and if so, How to run my loop so that the first time it runs it copy/pastes into Sheet1 and the second time it runs it copy/pastes into Sheet 2. I was thinking maybe an array might be helpful but I'm not familiar with their use or syntax. Any help in the right direction would be appreciated!
For Each a In Range("2:2").SpecialCells(xlCellTypeFormulas).Areas
a.Resize(lrow - 1, a.Columns.Count).FillDown
With Worksheets("Fall 2016")
lrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
With .Range("A3:CU" & lrow)
On Error Resume Next
Set erng = .SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0
If Not erng Is Nothing Then
Intersect(.Parent.Range("A:CU"), erng.EntireRow).Copy
Worksheets("Sheet1").Range("A3").PasteSpecial
End If
End With
End With
Next a
excel vba excel-vba loops
asked Jan 2 at 15:25
DekeDeke
1339
If it runs a third time would it run onSheet1again, or does it run on whichever sheet isn't theActiveSheet? If you want it to just flip-flop between the two sheets even after closing and reopening I think you'll have to store a number or odd/even pairing on a sheet to check which sheet.
– Darren Bartrup-Cook
Jan 2 at 15:30
Is there something in your sheets which you could use to identify which sheet to paste too? Might be worth providing a screen print of the sheets?
– Zac
Jan 2 at 15:36
@DarrenBartrup-Cook The loop will only ever run twice. So the first time will always be sheet1 and the second time it runs will always be sheet2
– Deke
Jan 2 at 15:38
@Zac Yes There should be a way to do that. The first sheet is always going to be "Lago" the Second would always be "MF". I'll post up an image what what I'm Looking at.
– Deke
Jan 2 at 15:40
2
@Deke - FYI, it's usually better to add information by editing your question instead of doing so in comments
– cybernetic.nomad
Jan 2 at 15:48
|
show 3 more comments
0
0
0
I'm attempting to figure out IF it's possible and if so, How to run my loop so that the first time it runs it copy/pastes into Sheet1 and the second time it runs it copy/pastes into Sheet 2. I was thinking maybe an array might be helpful but I'm not familiar with their use or syntax. Any help in the right direction would be appreciated!
For Each a In Range("2:2").SpecialCells(xlCellTypeFormulas).Areas
a.Resize(lrow - 1, a.Columns.Count).FillDown
With Worksheets("Fall 2016")
lrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
With .Range("A3:CU" & lrow)
On Error Resume Next
Set erng = .SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0
If Not erng Is Nothing Then
Intersect(.Parent.Range("A:CU"), erng.EntireRow).Copy
Worksheets("Sheet1").Range("A3").PasteSpecial
End If
End With
End With
Next a
excel vba excel-vba loops
asked Jan 2 at 15:25
DekeDeke
1339
I'm attempting to figure out IF it's possible and if so, How to run my loop so that the first time it runs it copy/pastes into Sheet1 and the second time it runs it copy/pastes into Sheet 2. I was thinking maybe an array might be helpful but I'm not familiar with their use or syntax. Any help in the right direction would be appreciated!
For Each a In Range("2:2").SpecialCells(xlCellTypeFormulas).Areas
a.Resize(lrow - 1, a.Columns.Count).FillDown
With Worksheets("Fall 2016")
lrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
With .Range("A3:CU" & lrow)
On Error Resume Next
Set erng = .SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0
If Not erng Is Nothing Then
Intersect(.Parent.Range("A:CU"), erng.EntireRow).Copy
Worksheets("Sheet1").Range("A3").PasteSpecial
End If
End With
End With
Next a
excel vba excel-vba loops
excel vba excel-vba loops
asked Jan 2 at 15:25
DekeDeke
1339
asked Jan 2 at 15:25
DekeDeke
1339
edited Jan 2 at 15:42
Deke
asked Jan 2 at 15:25
DekeDeke
1339
asked Jan 2 at 15:25
DekeDeke
1339
asked Jan 2 at 15:25
DekeDeke
1339
1339
If it runs a third time would it run onSheet1again, or does it run on whichever sheet isn't theActiveSheet? If you want it to just flip-flop between the two sheets even after closing and reopening I think you'll have to store a number or odd/even pairing on a sheet to check which sheet.
– Darren Bartrup-Cook
Jan 2 at 15:30
Is there something in your sheets which you could use to identify which sheet to paste too? Might be worth providing a screen print of the sheets?
– Zac
Jan 2 at 15:36
@DarrenBartrup-Cook The loop will only ever run twice. So the first time will always be sheet1 and the second time it runs will always be sheet2
– Deke
Jan 2 at 15:38
@Zac Yes There should be a way to do that. The first sheet is always going to be "Lago" the Second would always be "MF". I'll post up an image what what I'm Looking at.
– Deke
Jan 2 at 15:40
2
@Deke - FYI, it's usually better to add information by editing your question instead of doing so in comments
– cybernetic.nomad
Jan 2 at 15:48
|
show 3 more comments
If it runs a third time would it run onSheet1again, or does it run on whichever sheet isn't theActiveSheet? If you want it to just flip-flop between the two sheets even after closing and reopening I think you'll have to store a number or odd/even pairing on a sheet to check which sheet.
– Darren Bartrup-Cook
Jan 2 at 15:30
Is there something in your sheets which you could use to identify which sheet to paste too? Might be worth providing a screen print of the sheets?
– Zac
Jan 2 at 15:36
@DarrenBartrup-Cook The loop will only ever run twice. So the first time will always be sheet1 and the second time it runs will always be sheet2
– Deke
Jan 2 at 15:38
@Zac Yes There should be a way to do that. The first sheet is always going to be "Lago" the Second would always be "MF". I'll post up an image what what I'm Looking at.
– Deke
Jan 2 at 15:40
2
@Deke - FYI, it's usually better to add information by editing your question instead of doing so in comments
– cybernetic.nomad
Jan 2 at 15:48
If it runs a third time would it run on
Sheet1 again, or does it run on whichever sheet isn't the ActiveSheet? If you want it to just flip-flop between the two sheets even after closing and reopening I think you'll have to store a number or odd/even pairing on a sheet to check which sheet.– Darren Bartrup-Cook
Jan 2 at 15:30
If it runs a third time would it run on
Sheet1 again, or does it run on whichever sheet isn't the ActiveSheet? If you want it to just flip-flop between the two sheets even after closing and reopening I think you'll have to store a number or odd/even pairing on a sheet to check which sheet.– Darren Bartrup-Cook
Jan 2 at 15:30
Is there something in your sheets which you could use to identify which sheet to paste too? Might be worth providing a screen print of the sheets?
– Zac
Jan 2 at 15:36
Is there something in your sheets which you could use to identify which sheet to paste too? Might be worth providing a screen print of the sheets?
– Zac
Jan 2 at 15:36
@DarrenBartrup-Cook The loop will only ever run twice. So the first time will always be sheet1 and the second time it runs will always be sheet2
– Deke
Jan 2 at 15:38
@DarrenBartrup-Cook The loop will only ever run twice. So the first time will always be sheet1 and the second time it runs will always be sheet2
– Deke
Jan 2 at 15:38
@Zac Yes There should be a way to do that. The first sheet is always going to be "Lago" the Second would always be "MF". I'll post up an image what what I'm Looking at.
– Deke
Jan 2 at 15:40
@Zac Yes There should be a way to do that. The first sheet is always going to be "Lago" the Second would always be "MF". I'll post up an image what what I'm Looking at.
– Deke
Jan 2 at 15:40
2
2
@Deke - FYI, it's usually better to add information by editing your question instead of doing so in comments
– cybernetic.nomad
Jan 2 at 15:48
@Deke - FYI, it's usually better to add information by editing your question instead of doing so in comments
– cybernetic.nomad
Jan 2 at 15:48
|
show 3 more comments
1 Answer
1
active
oldest
votes
1
Sorry, I can't see the image so I've cobbled together something that should work.
There's two procedures - RunMe just fires the Test procedure and passes it the first worksheet, the other worksheet is then considered to be the second sheet.
I haven't included the For...Each block as it uses lrow which isn't defined until after it's used, so would default to 0 - not sure what you're trying to do there.
I've added a line of code at the end of Test which will call itself again but passing the other sheet.
Sub RunMe()
Test ThisWorkbook.Worksheets("Lago")
'If not using last line in main code, then
'include this to run second sheet at time of your choosing.
'Test ThisWorkbook.Worksheets("MF")
End Sub
Sub Test(FirstSheet As Worksheet)
Dim SecondSheet As Worksheet
Dim lrow As Long
Dim eRng As Range
If FirstSheet.Name = "Lago" Then
'The "Parent" of a worksheet is the workbook.
Set SecondSheet = FirstSheet.Parent.Worksheets("MF")
Else
Set SecondSheet = FirstSheet.Parent.Worksheets("Lago")
End If
With FirstSheet
lrow = .Cells(.Rows.Count, 1).End(xlUp).Row
With .Range(.Cells(3, 1), .Cells(lrow, 99))
On Error Resume Next
Set eRng = .SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0
If Not eRng Is Nothing Then
Intersect(.Parent.Range("A:CU"), eRng.EntireRow).Copy
SecondSheet.Range("A3").PasteSpecial
End If
End With
End With
'Now run again but with sheets reversed.
'Include this line to run both sheets in one go.
If FirstSheet.Name = "Lago" Then Test SecondSheet
End Sub
answered Jan 2 at 15:52
Darren Bartrup-CookDarren Bartrup-Cook
14k11533
keep getting subscript out of range error on the RunMe sub. Everything seems to line up though so not sure what I'm missing.
– Deke
Jan 2 at 16:06
That suggests you haven't got a sheet calledLago- if you do, check that it doesn't have any spaces before or after it on the tab name.
– Darren Bartrup-Cook
Jan 2 at 16:09
Names all line up. Retyped them to make sure. Moved the RunMe sub to the bottom and now getting a compile error: "Wrong number of arguments or invalid property assignment on the lineIf FirstSheet.Name = "Lago" Then Test SecondSheetI'm playing around with it though. I've gotta be missing something somewhere.
– Deke
Jan 2 at 16:26
Only other thing I can think of - is the code in the same workbook as the worksheets you're manipulating?ThisWorkbookmeans the workbook containing the code. Maybe change it toActiveWorkbookorWorkbooks("MyWorkbook.xlsx").
– Darren Bartrup-Cook
Jan 2 at 16:29
That would be it, the code is being run from a module in a different workbook. (There is a lot of interaction between several workbooks). So looks like I just need to specify the workbook in the module.
– Deke
Jan 2 at 16:47
|
show 1 more 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%2f54008942%2fvba-loop-code-and-have-it-recognize-multiple-sheets-to-copy-to-in-excel%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
1
Sorry, I can't see the image so I've cobbled together something that should work.
There's two procedures - RunMe just fires the Test procedure and passes it the first worksheet, the other worksheet is then considered to be the second sheet.
I haven't included the For...Each block as it uses lrow which isn't defined until after it's used, so would default to 0 - not sure what you're trying to do there.
I've added a line of code at the end of Test which will call itself again but passing the other sheet.
Sub RunMe()
Test ThisWorkbook.Worksheets("Lago")
'If not using last line in main code, then
'include this to run second sheet at time of your choosing.
'Test ThisWorkbook.Worksheets("MF")
End Sub
Sub Test(FirstSheet As Worksheet)
Dim SecondSheet As Worksheet
Dim lrow As Long
Dim eRng As Range
If FirstSheet.Name = "Lago" Then
'The "Parent" of a worksheet is the workbook.
Set SecondSheet = FirstSheet.Parent.Worksheets("MF")
Else
Set SecondSheet = FirstSheet.Parent.Worksheets("Lago")
End If
With FirstSheet
lrow = .Cells(.Rows.Count, 1).End(xlUp).Row
With .Range(.Cells(3, 1), .Cells(lrow, 99))
On Error Resume Next
Set eRng = .SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0
If Not eRng Is Nothing Then
Intersect(.Parent.Range("A:CU"), eRng.EntireRow).Copy
SecondSheet.Range("A3").PasteSpecial
End If
End With
End With
'Now run again but with sheets reversed.
'Include this line to run both sheets in one go.
If FirstSheet.Name = "Lago" Then Test SecondSheet
End Sub
answered Jan 2 at 15:52
Darren Bartrup-CookDarren Bartrup-Cook
14k11533
keep getting subscript out of range error on the RunMe sub. Everything seems to line up though so not sure what I'm missing.
– Deke
Jan 2 at 16:06
That suggests you haven't got a sheet calledLago- if you do, check that it doesn't have any spaces before or after it on the tab name.
– Darren Bartrup-Cook
Jan 2 at 16:09
Names all line up. Retyped them to make sure. Moved the RunMe sub to the bottom and now getting a compile error: "Wrong number of arguments or invalid property assignment on the lineIf FirstSheet.Name = "Lago" Then Test SecondSheetI'm playing around with it though. I've gotta be missing something somewhere.
– Deke
Jan 2 at 16:26
Only other thing I can think of - is the code in the same workbook as the worksheets you're manipulating?ThisWorkbookmeans the workbook containing the code. Maybe change it toActiveWorkbookorWorkbooks("MyWorkbook.xlsx").
– Darren Bartrup-Cook
Jan 2 at 16:29
That would be it, the code is being run from a module in a different workbook. (There is a lot of interaction between several workbooks). So looks like I just need to specify the workbook in the module.
– Deke
Jan 2 at 16:47
|
show 1 more comment
1
Sorry, I can't see the image so I've cobbled together something that should work.
There's two procedures - RunMe just fires the Test procedure and passes it the first worksheet, the other worksheet is then considered to be the second sheet.
I haven't included the For...Each block as it uses lrow which isn't defined until after it's used, so would default to 0 - not sure what you're trying to do there.
I've added a line of code at the end of Test which will call itself again but passing the other sheet.
Sub RunMe()
Test ThisWorkbook.Worksheets("Lago")
'If not using last line in main code, then
'include this to run second sheet at time of your choosing.
'Test ThisWorkbook.Worksheets("MF")
End Sub
Sub Test(FirstSheet As Worksheet)
Dim SecondSheet As Worksheet
Dim lrow As Long
Dim eRng As Range
If FirstSheet.Name = "Lago" Then
'The "Parent" of a worksheet is the workbook.
Set SecondSheet = FirstSheet.Parent.Worksheets("MF")
Else
Set SecondSheet = FirstSheet.Parent.Worksheets("Lago")
End If
With FirstSheet
lrow = .Cells(.Rows.Count, 1).End(xlUp).Row
With .Range(.Cells(3, 1), .Cells(lrow, 99))
On Error Resume Next
Set eRng = .SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0
If Not eRng Is Nothing Then
Intersect(.Parent.Range("A:CU"), eRng.EntireRow).Copy
SecondSheet.Range("A3").PasteSpecial
End If
End With
End With
'Now run again but with sheets reversed.
'Include this line to run both sheets in one go.
If FirstSheet.Name = "Lago" Then Test SecondSheet
End Sub
answered Jan 2 at 15:52
Darren Bartrup-CookDarren Bartrup-Cook
14k11533
keep getting subscript out of range error on the RunMe sub. Everything seems to line up though so not sure what I'm missing.
– Deke
Jan 2 at 16:06
That suggests you haven't got a sheet calledLago- if you do, check that it doesn't have any spaces before or after it on the tab name.
– Darren Bartrup-Cook
Jan 2 at 16:09
Names all line up. Retyped them to make sure. Moved the RunMe sub to the bottom and now getting a compile error: "Wrong number of arguments or invalid property assignment on the lineIf FirstSheet.Name = "Lago" Then Test SecondSheetI'm playing around with it though. I've gotta be missing something somewhere.
– Deke
Jan 2 at 16:26
Only other thing I can think of - is the code in the same workbook as the worksheets you're manipulating?ThisWorkbookmeans the workbook containing the code. Maybe change it toActiveWorkbookorWorkbooks("MyWorkbook.xlsx").
– Darren Bartrup-Cook
Jan 2 at 16:29
That would be it, the code is being run from a module in a different workbook. (There is a lot of interaction between several workbooks). So looks like I just need to specify the workbook in the module.
– Deke
Jan 2 at 16:47
|
show 1 more comment
1
1
1
Sorry, I can't see the image so I've cobbled together something that should work.
There's two procedures - RunMe just fires the Test procedure and passes it the first worksheet, the other worksheet is then considered to be the second sheet.
I haven't included the For...Each block as it uses lrow which isn't defined until after it's used, so would default to 0 - not sure what you're trying to do there.
I've added a line of code at the end of Test which will call itself again but passing the other sheet.
Sub RunMe()
Test ThisWorkbook.Worksheets("Lago")
'If not using last line in main code, then
'include this to run second sheet at time of your choosing.
'Test ThisWorkbook.Worksheets("MF")
End Sub
Sub Test(FirstSheet As Worksheet)
Dim SecondSheet As Worksheet
Dim lrow As Long
Dim eRng As Range
If FirstSheet.Name = "Lago" Then
'The "Parent" of a worksheet is the workbook.
Set SecondSheet = FirstSheet.Parent.Worksheets("MF")
Else
Set SecondSheet = FirstSheet.Parent.Worksheets("Lago")
End If
With FirstSheet
lrow = .Cells(.Rows.Count, 1).End(xlUp).Row
With .Range(.Cells(3, 1), .Cells(lrow, 99))
On Error Resume Next
Set eRng = .SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0
If Not eRng Is Nothing Then
Intersect(.Parent.Range("A:CU"), eRng.EntireRow).Copy
SecondSheet.Range("A3").PasteSpecial
End If
End With
End With
'Now run again but with sheets reversed.
'Include this line to run both sheets in one go.
If FirstSheet.Name = "Lago" Then Test SecondSheet
End Sub
answered Jan 2 at 15:52
Darren Bartrup-CookDarren Bartrup-Cook
14k11533
Sorry, I can't see the image so I've cobbled together something that should work.
There's two procedures - RunMe just fires the Test procedure and passes it the first worksheet, the other worksheet is then considered to be the second sheet.
I haven't included the For...Each block as it uses lrow which isn't defined until after it's used, so would default to 0 - not sure what you're trying to do there.
I've added a line of code at the end of Test which will call itself again but passing the other sheet.
Sub RunMe()
Test ThisWorkbook.Worksheets("Lago")
'If not using last line in main code, then
'include this to run second sheet at time of your choosing.
'Test ThisWorkbook.Worksheets("MF")
End Sub
Sub Test(FirstSheet As Worksheet)
Dim SecondSheet As Worksheet
Dim lrow As Long
Dim eRng As Range
If FirstSheet.Name = "Lago" Then
'The "Parent" of a worksheet is the workbook.
Set SecondSheet = FirstSheet.Parent.Worksheets("MF")
Else
Set SecondSheet = FirstSheet.Parent.Worksheets("Lago")
End If
With FirstSheet
lrow = .Cells(.Rows.Count, 1).End(xlUp).Row
With .Range(.Cells(3, 1), .Cells(lrow, 99))
On Error Resume Next
Set eRng = .SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0
If Not eRng Is Nothing Then
Intersect(.Parent.Range("A:CU"), eRng.EntireRow).Copy
SecondSheet.Range("A3").PasteSpecial
End If
End With
End With
'Now run again but with sheets reversed.
'Include this line to run both sheets in one go.
If FirstSheet.Name = "Lago" Then Test SecondSheet
End Sub
answered Jan 2 at 15:52
Darren Bartrup-CookDarren Bartrup-Cook
14k11533
edited Jan 2 at 16:55
answered Jan 2 at 15:52
Darren Bartrup-CookDarren Bartrup-Cook
14k11533
answered Jan 2 at 15:52
Darren Bartrup-CookDarren Bartrup-Cook
14k11533
answered Jan 2 at 15:52
Darren Bartrup-CookDarren Bartrup-Cook
14k11533
14k11533
keep getting subscript out of range error on the RunMe sub. Everything seems to line up though so not sure what I'm missing.
– Deke
Jan 2 at 16:06
That suggests you haven't got a sheet calledLago- if you do, check that it doesn't have any spaces before or after it on the tab name.
– Darren Bartrup-Cook
Jan 2 at 16:09
Names all line up. Retyped them to make sure. Moved the RunMe sub to the bottom and now getting a compile error: "Wrong number of arguments or invalid property assignment on the lineIf FirstSheet.Name = "Lago" Then Test SecondSheetI'm playing around with it though. I've gotta be missing something somewhere.
– Deke
Jan 2 at 16:26
Only other thing I can think of - is the code in the same workbook as the worksheets you're manipulating?ThisWorkbookmeans the workbook containing the code. Maybe change it toActiveWorkbookorWorkbooks("MyWorkbook.xlsx").
– Darren Bartrup-Cook
Jan 2 at 16:29
That would be it, the code is being run from a module in a different workbook. (There is a lot of interaction between several workbooks). So looks like I just need to specify the workbook in the module.
– Deke
Jan 2 at 16:47
|
show 1 more comment
keep getting subscript out of range error on the RunMe sub. Everything seems to line up though so not sure what I'm missing.
– Deke
Jan 2 at 16:06
That suggests you haven't got a sheet calledLago- if you do, check that it doesn't have any spaces before or after it on the tab name.
– Darren Bartrup-Cook
Jan 2 at 16:09
Names all line up. Retyped them to make sure. Moved the RunMe sub to the bottom and now getting a compile error: "Wrong number of arguments or invalid property assignment on the lineIf FirstSheet.Name = "Lago" Then Test SecondSheetI'm playing around with it though. I've gotta be missing something somewhere.
– Deke
Jan 2 at 16:26
Only other thing I can think of - is the code in the same workbook as the worksheets you're manipulating?ThisWorkbookmeans the workbook containing the code. Maybe change it toActiveWorkbookorWorkbooks("MyWorkbook.xlsx").
– Darren Bartrup-Cook
Jan 2 at 16:29
That would be it, the code is being run from a module in a different workbook. (There is a lot of interaction between several workbooks). So looks like I just need to specify the workbook in the module.
– Deke
Jan 2 at 16:47
keep getting subscript out of range error on the RunMe sub. Everything seems to line up though so not sure what I'm missing.
– Deke
Jan 2 at 16:06
keep getting subscript out of range error on the RunMe sub. Everything seems to line up though so not sure what I'm missing.
– Deke
Jan 2 at 16:06
That suggests you haven't got a sheet called
Lago - if you do, check that it doesn't have any spaces before or after it on the tab name.– Darren Bartrup-Cook
Jan 2 at 16:09
That suggests you haven't got a sheet called
Lago - if you do, check that it doesn't have any spaces before or after it on the tab name.– Darren Bartrup-Cook
Jan 2 at 16:09
Names all line up. Retyped them to make sure. Moved the RunMe sub to the bottom and now getting a compile error: "Wrong number of arguments or invalid property assignment on the line
If FirstSheet.Name = "Lago" Then Test SecondSheet I'm playing around with it though. I've gotta be missing something somewhere.– Deke
Jan 2 at 16:26
Names all line up. Retyped them to make sure. Moved the RunMe sub to the bottom and now getting a compile error: "Wrong number of arguments or invalid property assignment on the line
If FirstSheet.Name = "Lago" Then Test SecondSheet I'm playing around with it though. I've gotta be missing something somewhere.– Deke
Jan 2 at 16:26
Only other thing I can think of - is the code in the same workbook as the worksheets you're manipulating?
ThisWorkbook means the workbook containing the code. Maybe change it to ActiveWorkbook or Workbooks("MyWorkbook.xlsx").– Darren Bartrup-Cook
Jan 2 at 16:29
Only other thing I can think of - is the code in the same workbook as the worksheets you're manipulating?
ThisWorkbook means the workbook containing the code. Maybe change it to ActiveWorkbook or Workbooks("MyWorkbook.xlsx").– Darren Bartrup-Cook
Jan 2 at 16:29
That would be it, the code is being run from a module in a different workbook. (There is a lot of interaction between several workbooks). So looks like I just need to specify the workbook in the module.
– Deke
Jan 2 at 16:47
That would be it, the code is being run from a module in a different workbook. (There is a lot of interaction between several workbooks). So looks like I just need to specify the workbook in the module.
– Deke
Jan 2 at 16:47
|
show 1 more 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%2f54008942%2fvba-loop-code-and-have-it-recognize-multiple-sheets-to-copy-to-in-excel%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
If it runs a third time would it run on
Sheet1again, or does it run on whichever sheet isn't theActiveSheet? If you want it to just flip-flop between the two sheets even after closing and reopening I think you'll have to store a number or odd/even pairing on a sheet to check which sheet.– Darren Bartrup-Cook
Jan 2 at 15:30
Is there something in your sheets which you could use to identify which sheet to paste too? Might be worth providing a screen print of the sheets?
– Zac
Jan 2 at 15:36
@DarrenBartrup-Cook The loop will only ever run twice. So the first time will always be sheet1 and the second time it runs will always be sheet2
– Deke
Jan 2 at 15:38
@Zac Yes There should be a way to do that. The first sheet is always going to be "Lago" the Second would always be "MF". I'll post up an image what what I'm Looking at.
– Deke
Jan 2 at 15:40
2
@Deke - FYI, it's usually better to add information by editing your question instead of doing so in comments
– cybernetic.nomad
Jan 2 at 15:48