Is there a reason why VBA code does not work in sequential order?

Multi tool use
I am not entirely sure how to phrase this question. However, I will be able to explain it better here. Below is my code. The purpose of my code is to copy and paste data from one sheet to another.
Everything seems to work fine up until it gets to the very last line of code (excluding "End Sub"). The last line is supposed to fill down to the last row. The issue I am getting is that the code works fine if I break up the With
statement and the final line and run them separately.
I know the last line works but when I run the entire macro, I get a "Run time Error '1004" error message. Why does my code not work?
Sub Data_Table()
Dim Data As Worksheet
Dim Sum As Worksheet
Dim lr As Long
Dim lr2 As Long
Dim lr3 As Long
Dim lr4 As Long
Dim lr5 As Long
Set Data = Worksheets("Data-Tracker")
Set Sum = Worksheets("Summary")
lr = Data.Cells(Rows.Count, "E").End(xlUp).Row
lr2 = Data.Cells(Rows.Count, "A").End(xlUp).Row 'for customer type
lr3 = Data.Cells(Rows.Count, "B").End(xlUp).Row ' for Type
lr4 = Data.Cells(Rows.Count, "C").End(xlUp).Row ' for Rate/Budget
lr5 = Data.Cells(Rows.Count, "D").End(xlUp).Row ' for Date
With Sum
.Range("B6:B12").Copy Destination:=Data.Range("E" & lr).Offset(1, 0)
.Range("C6:C12").Copy Destination:=Data.Range("F" & lr).Offset(1, 0)
.Range("D6:D12").Copy Destination:=Data.Range("G" & lr).Offset(1, 0)
.Range("C2").Copy Destination:=Data.Range("B" & lr3).Offset(1, 0)
.Range("B4").Copy Destination:=Data.Range("C" & lr4).Offset(1, 0)
.Range("B5").Copy Destination:=Data.Range("D" & lr5).Offset(1, 0)
End With
Data.Range("B" & lr3, "D" & lr5).AutoFill Destination:=Data.Range("B" & lr3, "D" & lr)
End Sub
Any help would be greatly appreciated.
EDIT:
To help further explain my point, if I first run my code like this:
With Sum
.Range("B6:B12").Copy Destination:=Data.Range("E" & lr).Offset(1, 0)
.Range("C6:C12").Copy Destination:=Data.Range("F" & lr).Offset(1, 0)
.Range("D6:D12").Copy Destination:=Data.Range("G" & lr).Offset(1, 0)
.Range("C2").Copy Destination:=Data.Range("B" & lr3).Offset(1, 0)
.Range("B4").Copy Destination:=Data.Range("C" & lr4).Offset(1, 0)
.Range("B5").Copy Destination:=Data.Range("D" & lr5).Offset(1, 0)
End With
' Data.Range("B" & lr3 & ":D" & lr5).AutoFill Destination:=Data.Range("B" & lr3, "D" & lr)
And then after run it like:
' With Sum
'
' .Range("B6:B12").Copy Destination:=Data.Range("E" & lr).Offset(1, 0)
' .Range("C6:C12").Copy Destination:=Data.Range("F" & lr).Offset(1, 0)
' .Range("D6:D12").Copy Destination:=Data.Range("G" & lr).Offset(1, 0)
' .Range("C2").Copy Destination:=Data.Range("B" & lr3).Offset(1, 0)
' .Range("B4").Copy Destination:=Data.Range("C" & lr4).Offset(1, 0)
' .Range("B5").Copy Destination:=Data.Range("D" & lr5).Offset(1, 0)
'
'
' End With
Data.Range("B" & lr3 & ":D" & lr5).AutoFill Destination:=Data.Range("B" & lr3, "D" & lr)
I have to add the apostrophes in to cancel the code out in order for it to work. Otherwise I get a Autofill Method error.
excel vba excel-vba
|
show 1 more comment
I am not entirely sure how to phrase this question. However, I will be able to explain it better here. Below is my code. The purpose of my code is to copy and paste data from one sheet to another.
Everything seems to work fine up until it gets to the very last line of code (excluding "End Sub"). The last line is supposed to fill down to the last row. The issue I am getting is that the code works fine if I break up the With
statement and the final line and run them separately.
I know the last line works but when I run the entire macro, I get a "Run time Error '1004" error message. Why does my code not work?
Sub Data_Table()
Dim Data As Worksheet
Dim Sum As Worksheet
Dim lr As Long
Dim lr2 As Long
Dim lr3 As Long
Dim lr4 As Long
Dim lr5 As Long
Set Data = Worksheets("Data-Tracker")
Set Sum = Worksheets("Summary")
lr = Data.Cells(Rows.Count, "E").End(xlUp).Row
lr2 = Data.Cells(Rows.Count, "A").End(xlUp).Row 'for customer type
lr3 = Data.Cells(Rows.Count, "B").End(xlUp).Row ' for Type
lr4 = Data.Cells(Rows.Count, "C").End(xlUp).Row ' for Rate/Budget
lr5 = Data.Cells(Rows.Count, "D").End(xlUp).Row ' for Date
With Sum
.Range("B6:B12").Copy Destination:=Data.Range("E" & lr).Offset(1, 0)
.Range("C6:C12").Copy Destination:=Data.Range("F" & lr).Offset(1, 0)
.Range("D6:D12").Copy Destination:=Data.Range("G" & lr).Offset(1, 0)
.Range("C2").Copy Destination:=Data.Range("B" & lr3).Offset(1, 0)
.Range("B4").Copy Destination:=Data.Range("C" & lr4).Offset(1, 0)
.Range("B5").Copy Destination:=Data.Range("D" & lr5).Offset(1, 0)
End With
Data.Range("B" & lr3, "D" & lr5).AutoFill Destination:=Data.Range("B" & lr3, "D" & lr)
End Sub
Any help would be greatly appreciated.
EDIT:
To help further explain my point, if I first run my code like this:
With Sum
.Range("B6:B12").Copy Destination:=Data.Range("E" & lr).Offset(1, 0)
.Range("C6:C12").Copy Destination:=Data.Range("F" & lr).Offset(1, 0)
.Range("D6:D12").Copy Destination:=Data.Range("G" & lr).Offset(1, 0)
.Range("C2").Copy Destination:=Data.Range("B" & lr3).Offset(1, 0)
.Range("B4").Copy Destination:=Data.Range("C" & lr4).Offset(1, 0)
.Range("B5").Copy Destination:=Data.Range("D" & lr5).Offset(1, 0)
End With
' Data.Range("B" & lr3 & ":D" & lr5).AutoFill Destination:=Data.Range("B" & lr3, "D" & lr)
And then after run it like:
' With Sum
'
' .Range("B6:B12").Copy Destination:=Data.Range("E" & lr).Offset(1, 0)
' .Range("C6:C12").Copy Destination:=Data.Range("F" & lr).Offset(1, 0)
' .Range("D6:D12").Copy Destination:=Data.Range("G" & lr).Offset(1, 0)
' .Range("C2").Copy Destination:=Data.Range("B" & lr3).Offset(1, 0)
' .Range("B4").Copy Destination:=Data.Range("C" & lr4).Offset(1, 0)
' .Range("B5").Copy Destination:=Data.Range("D" & lr5).Offset(1, 0)
'
'
' End With
Data.Range("B" & lr3 & ":D" & lr5).AutoFill Destination:=Data.Range("B" & lr3, "D" & lr)
I have to add the apostrophes in to cancel the code out in order for it to work. Otherwise I get a Autofill Method error.
excel vba excel-vba
Could you provide the full error message, there is something behind 1004.
– VBasic2008
Dec 30 '18 at 22:34
Code does what you tell it to do. If it's not working as intended, then I'd safely assume there is an error with the code..
– alowflyingpig
Dec 30 '18 at 22:46
The last line will fail iflr
is less thanlr5
. Stop the code at End With and hover over the variables lr5 and lr to see their values. Another reason could be the second argument of the Autofill method, when Excel decides how to aufofill.
– VBasic2008
Dec 30 '18 at 22:55
Try only with this One: Data.Range("B" & lr3, "D" & lr5).AutoFill....
– Ferdinando
Dec 30 '18 at 23:32
Sorry for the late response. I get a "AutoFill method of Range class failed" error
– GSC
Dec 31 '18 at 2:49
|
show 1 more comment
I am not entirely sure how to phrase this question. However, I will be able to explain it better here. Below is my code. The purpose of my code is to copy and paste data from one sheet to another.
Everything seems to work fine up until it gets to the very last line of code (excluding "End Sub"). The last line is supposed to fill down to the last row. The issue I am getting is that the code works fine if I break up the With
statement and the final line and run them separately.
I know the last line works but when I run the entire macro, I get a "Run time Error '1004" error message. Why does my code not work?
Sub Data_Table()
Dim Data As Worksheet
Dim Sum As Worksheet
Dim lr As Long
Dim lr2 As Long
Dim lr3 As Long
Dim lr4 As Long
Dim lr5 As Long
Set Data = Worksheets("Data-Tracker")
Set Sum = Worksheets("Summary")
lr = Data.Cells(Rows.Count, "E").End(xlUp).Row
lr2 = Data.Cells(Rows.Count, "A").End(xlUp).Row 'for customer type
lr3 = Data.Cells(Rows.Count, "B").End(xlUp).Row ' for Type
lr4 = Data.Cells(Rows.Count, "C").End(xlUp).Row ' for Rate/Budget
lr5 = Data.Cells(Rows.Count, "D").End(xlUp).Row ' for Date
With Sum
.Range("B6:B12").Copy Destination:=Data.Range("E" & lr).Offset(1, 0)
.Range("C6:C12").Copy Destination:=Data.Range("F" & lr).Offset(1, 0)
.Range("D6:D12").Copy Destination:=Data.Range("G" & lr).Offset(1, 0)
.Range("C2").Copy Destination:=Data.Range("B" & lr3).Offset(1, 0)
.Range("B4").Copy Destination:=Data.Range("C" & lr4).Offset(1, 0)
.Range("B5").Copy Destination:=Data.Range("D" & lr5).Offset(1, 0)
End With
Data.Range("B" & lr3, "D" & lr5).AutoFill Destination:=Data.Range("B" & lr3, "D" & lr)
End Sub
Any help would be greatly appreciated.
EDIT:
To help further explain my point, if I first run my code like this:
With Sum
.Range("B6:B12").Copy Destination:=Data.Range("E" & lr).Offset(1, 0)
.Range("C6:C12").Copy Destination:=Data.Range("F" & lr).Offset(1, 0)
.Range("D6:D12").Copy Destination:=Data.Range("G" & lr).Offset(1, 0)
.Range("C2").Copy Destination:=Data.Range("B" & lr3).Offset(1, 0)
.Range("B4").Copy Destination:=Data.Range("C" & lr4).Offset(1, 0)
.Range("B5").Copy Destination:=Data.Range("D" & lr5).Offset(1, 0)
End With
' Data.Range("B" & lr3 & ":D" & lr5).AutoFill Destination:=Data.Range("B" & lr3, "D" & lr)
And then after run it like:
' With Sum
'
' .Range("B6:B12").Copy Destination:=Data.Range("E" & lr).Offset(1, 0)
' .Range("C6:C12").Copy Destination:=Data.Range("F" & lr).Offset(1, 0)
' .Range("D6:D12").Copy Destination:=Data.Range("G" & lr).Offset(1, 0)
' .Range("C2").Copy Destination:=Data.Range("B" & lr3).Offset(1, 0)
' .Range("B4").Copy Destination:=Data.Range("C" & lr4).Offset(1, 0)
' .Range("B5").Copy Destination:=Data.Range("D" & lr5).Offset(1, 0)
'
'
' End With
Data.Range("B" & lr3 & ":D" & lr5).AutoFill Destination:=Data.Range("B" & lr3, "D" & lr)
I have to add the apostrophes in to cancel the code out in order for it to work. Otherwise I get a Autofill Method error.
excel vba excel-vba
I am not entirely sure how to phrase this question. However, I will be able to explain it better here. Below is my code. The purpose of my code is to copy and paste data from one sheet to another.
Everything seems to work fine up until it gets to the very last line of code (excluding "End Sub"). The last line is supposed to fill down to the last row. The issue I am getting is that the code works fine if I break up the With
statement and the final line and run them separately.
I know the last line works but when I run the entire macro, I get a "Run time Error '1004" error message. Why does my code not work?
Sub Data_Table()
Dim Data As Worksheet
Dim Sum As Worksheet
Dim lr As Long
Dim lr2 As Long
Dim lr3 As Long
Dim lr4 As Long
Dim lr5 As Long
Set Data = Worksheets("Data-Tracker")
Set Sum = Worksheets("Summary")
lr = Data.Cells(Rows.Count, "E").End(xlUp).Row
lr2 = Data.Cells(Rows.Count, "A").End(xlUp).Row 'for customer type
lr3 = Data.Cells(Rows.Count, "B").End(xlUp).Row ' for Type
lr4 = Data.Cells(Rows.Count, "C").End(xlUp).Row ' for Rate/Budget
lr5 = Data.Cells(Rows.Count, "D").End(xlUp).Row ' for Date
With Sum
.Range("B6:B12").Copy Destination:=Data.Range("E" & lr).Offset(1, 0)
.Range("C6:C12").Copy Destination:=Data.Range("F" & lr).Offset(1, 0)
.Range("D6:D12").Copy Destination:=Data.Range("G" & lr).Offset(1, 0)
.Range("C2").Copy Destination:=Data.Range("B" & lr3).Offset(1, 0)
.Range("B4").Copy Destination:=Data.Range("C" & lr4).Offset(1, 0)
.Range("B5").Copy Destination:=Data.Range("D" & lr5).Offset(1, 0)
End With
Data.Range("B" & lr3, "D" & lr5).AutoFill Destination:=Data.Range("B" & lr3, "D" & lr)
End Sub
Any help would be greatly appreciated.
EDIT:
To help further explain my point, if I first run my code like this:
With Sum
.Range("B6:B12").Copy Destination:=Data.Range("E" & lr).Offset(1, 0)
.Range("C6:C12").Copy Destination:=Data.Range("F" & lr).Offset(1, 0)
.Range("D6:D12").Copy Destination:=Data.Range("G" & lr).Offset(1, 0)
.Range("C2").Copy Destination:=Data.Range("B" & lr3).Offset(1, 0)
.Range("B4").Copy Destination:=Data.Range("C" & lr4).Offset(1, 0)
.Range("B5").Copy Destination:=Data.Range("D" & lr5).Offset(1, 0)
End With
' Data.Range("B" & lr3 & ":D" & lr5).AutoFill Destination:=Data.Range("B" & lr3, "D" & lr)
And then after run it like:
' With Sum
'
' .Range("B6:B12").Copy Destination:=Data.Range("E" & lr).Offset(1, 0)
' .Range("C6:C12").Copy Destination:=Data.Range("F" & lr).Offset(1, 0)
' .Range("D6:D12").Copy Destination:=Data.Range("G" & lr).Offset(1, 0)
' .Range("C2").Copy Destination:=Data.Range("B" & lr3).Offset(1, 0)
' .Range("B4").Copy Destination:=Data.Range("C" & lr4).Offset(1, 0)
' .Range("B5").Copy Destination:=Data.Range("D" & lr5).Offset(1, 0)
'
'
' End With
Data.Range("B" & lr3 & ":D" & lr5).AutoFill Destination:=Data.Range("B" & lr3, "D" & lr)
I have to add the apostrophes in to cancel the code out in order for it to work. Otherwise I get a Autofill Method error.
excel vba excel-vba
excel vba excel-vba
edited Dec 31 '18 at 2:55
GSC
asked Dec 30 '18 at 20:21


GSCGSC
123
123
Could you provide the full error message, there is something behind 1004.
– VBasic2008
Dec 30 '18 at 22:34
Code does what you tell it to do. If it's not working as intended, then I'd safely assume there is an error with the code..
– alowflyingpig
Dec 30 '18 at 22:46
The last line will fail iflr
is less thanlr5
. Stop the code at End With and hover over the variables lr5 and lr to see their values. Another reason could be the second argument of the Autofill method, when Excel decides how to aufofill.
– VBasic2008
Dec 30 '18 at 22:55
Try only with this One: Data.Range("B" & lr3, "D" & lr5).AutoFill....
– Ferdinando
Dec 30 '18 at 23:32
Sorry for the late response. I get a "AutoFill method of Range class failed" error
– GSC
Dec 31 '18 at 2:49
|
show 1 more comment
Could you provide the full error message, there is something behind 1004.
– VBasic2008
Dec 30 '18 at 22:34
Code does what you tell it to do. If it's not working as intended, then I'd safely assume there is an error with the code..
– alowflyingpig
Dec 30 '18 at 22:46
The last line will fail iflr
is less thanlr5
. Stop the code at End With and hover over the variables lr5 and lr to see their values. Another reason could be the second argument of the Autofill method, when Excel decides how to aufofill.
– VBasic2008
Dec 30 '18 at 22:55
Try only with this One: Data.Range("B" & lr3, "D" & lr5).AutoFill....
– Ferdinando
Dec 30 '18 at 23:32
Sorry for the late response. I get a "AutoFill method of Range class failed" error
– GSC
Dec 31 '18 at 2:49
Could you provide the full error message, there is something behind 1004.
– VBasic2008
Dec 30 '18 at 22:34
Could you provide the full error message, there is something behind 1004.
– VBasic2008
Dec 30 '18 at 22:34
Code does what you tell it to do. If it's not working as intended, then I'd safely assume there is an error with the code..
– alowflyingpig
Dec 30 '18 at 22:46
Code does what you tell it to do. If it's not working as intended, then I'd safely assume there is an error with the code..
– alowflyingpig
Dec 30 '18 at 22:46
The last line will fail if
lr
is less than lr5
. Stop the code at End With and hover over the variables lr5 and lr to see their values. Another reason could be the second argument of the Autofill method, when Excel decides how to aufofill.– VBasic2008
Dec 30 '18 at 22:55
The last line will fail if
lr
is less than lr5
. Stop the code at End With and hover over the variables lr5 and lr to see their values. Another reason could be the second argument of the Autofill method, when Excel decides how to aufofill.– VBasic2008
Dec 30 '18 at 22:55
Try only with this One: Data.Range("B" & lr3, "D" & lr5).AutoFill....
– Ferdinando
Dec 30 '18 at 23:32
Try only with this One: Data.Range("B" & lr3, "D" & lr5).AutoFill....
– Ferdinando
Dec 30 '18 at 23:32
Sorry for the late response. I get a "AutoFill method of Range class failed" error
– GSC
Dec 31 '18 at 2:49
Sorry for the late response. I get a "AutoFill method of Range class failed" error
– GSC
Dec 31 '18 at 2:49
|
show 1 more comment
2 Answers
2
active
oldest
votes
There are two things you need to do.
The first and the obvious one is to fix the syntax in the line in question: a Range Address needs to be indicated in both Source and Destination, not the corners: ...lr3 & ":D"
... and not this ...lr3, "D"
...
The second one is to make sure that the Destination is always taller than the source, just in case:
If lr > lr5 Then
Data.Range("B" & lr3 & ":D" & lr5).AutoFill ....
End If
Thanks for the answer Gene!
– GSC
Jan 2 at 15:39
add a comment |
I suggest replacing the Range().Copy Destination:=
statements with absolute referencing with relative referencing and the direct assignment of multiple values in one statement.
I think your intent is to append to the Summary
worksheets with values from Data
.
Say you have n
cells under E2
in the Data
page that you want to be copied under the k
-th cell in Summary
page under B2
. You do that with
Sum.Range("B2").Cells(k,1).Resize(n,1).Value = Data.Range("E2").Resize(n,1).Value
The trick is reference one cell and then use the .Resize()
statement to expand the selection into a whole table (multiple rows and columns) if needed. The use the .Value
assignment to move all the values in one statement.
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%2f53981131%2fis-there-a-reason-why-vba-code-does-not-work-in-sequential-order%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
There are two things you need to do.
The first and the obvious one is to fix the syntax in the line in question: a Range Address needs to be indicated in both Source and Destination, not the corners: ...lr3 & ":D"
... and not this ...lr3, "D"
...
The second one is to make sure that the Destination is always taller than the source, just in case:
If lr > lr5 Then
Data.Range("B" & lr3 & ":D" & lr5).AutoFill ....
End If
Thanks for the answer Gene!
– GSC
Jan 2 at 15:39
add a comment |
There are two things you need to do.
The first and the obvious one is to fix the syntax in the line in question: a Range Address needs to be indicated in both Source and Destination, not the corners: ...lr3 & ":D"
... and not this ...lr3, "D"
...
The second one is to make sure that the Destination is always taller than the source, just in case:
If lr > lr5 Then
Data.Range("B" & lr3 & ":D" & lr5).AutoFill ....
End If
Thanks for the answer Gene!
– GSC
Jan 2 at 15:39
add a comment |
There are two things you need to do.
The first and the obvious one is to fix the syntax in the line in question: a Range Address needs to be indicated in both Source and Destination, not the corners: ...lr3 & ":D"
... and not this ...lr3, "D"
...
The second one is to make sure that the Destination is always taller than the source, just in case:
If lr > lr5 Then
Data.Range("B" & lr3 & ":D" & lr5).AutoFill ....
End If
There are two things you need to do.
The first and the obvious one is to fix the syntax in the line in question: a Range Address needs to be indicated in both Source and Destination, not the corners: ...lr3 & ":D"
... and not this ...lr3, "D"
...
The second one is to make sure that the Destination is always taller than the source, just in case:
If lr > lr5 Then
Data.Range("B" & lr3 & ":D" & lr5).AutoFill ....
End If
answered Dec 31 '18 at 15:33
GeneGene
35216
35216
Thanks for the answer Gene!
– GSC
Jan 2 at 15:39
add a comment |
Thanks for the answer Gene!
– GSC
Jan 2 at 15:39
Thanks for the answer Gene!
– GSC
Jan 2 at 15:39
Thanks for the answer Gene!
– GSC
Jan 2 at 15:39
add a comment |
I suggest replacing the Range().Copy Destination:=
statements with absolute referencing with relative referencing and the direct assignment of multiple values in one statement.
I think your intent is to append to the Summary
worksheets with values from Data
.
Say you have n
cells under E2
in the Data
page that you want to be copied under the k
-th cell in Summary
page under B2
. You do that with
Sum.Range("B2").Cells(k,1).Resize(n,1).Value = Data.Range("E2").Resize(n,1).Value
The trick is reference one cell and then use the .Resize()
statement to expand the selection into a whole table (multiple rows and columns) if needed. The use the .Value
assignment to move all the values in one statement.
add a comment |
I suggest replacing the Range().Copy Destination:=
statements with absolute referencing with relative referencing and the direct assignment of multiple values in one statement.
I think your intent is to append to the Summary
worksheets with values from Data
.
Say you have n
cells under E2
in the Data
page that you want to be copied under the k
-th cell in Summary
page under B2
. You do that with
Sum.Range("B2").Cells(k,1).Resize(n,1).Value = Data.Range("E2").Resize(n,1).Value
The trick is reference one cell and then use the .Resize()
statement to expand the selection into a whole table (multiple rows and columns) if needed. The use the .Value
assignment to move all the values in one statement.
add a comment |
I suggest replacing the Range().Copy Destination:=
statements with absolute referencing with relative referencing and the direct assignment of multiple values in one statement.
I think your intent is to append to the Summary
worksheets with values from Data
.
Say you have n
cells under E2
in the Data
page that you want to be copied under the k
-th cell in Summary
page under B2
. You do that with
Sum.Range("B2").Cells(k,1).Resize(n,1).Value = Data.Range("E2").Resize(n,1).Value
The trick is reference one cell and then use the .Resize()
statement to expand the selection into a whole table (multiple rows and columns) if needed. The use the .Value
assignment to move all the values in one statement.
I suggest replacing the Range().Copy Destination:=
statements with absolute referencing with relative referencing and the direct assignment of multiple values in one statement.
I think your intent is to append to the Summary
worksheets with values from Data
.
Say you have n
cells under E2
in the Data
page that you want to be copied under the k
-th cell in Summary
page under B2
. You do that with
Sum.Range("B2").Cells(k,1).Resize(n,1).Value = Data.Range("E2").Resize(n,1).Value
The trick is reference one cell and then use the .Resize()
statement to expand the selection into a whole table (multiple rows and columns) if needed. The use the .Value
assignment to move all the values in one statement.
answered Dec 31 '18 at 15:56


ja72ja72
18.2k349104
18.2k349104
add a comment |
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%2f53981131%2fis-there-a-reason-why-vba-code-does-not-work-in-sequential-order%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
7S2gD,h,I a,WR71r
Could you provide the full error message, there is something behind 1004.
– VBasic2008
Dec 30 '18 at 22:34
Code does what you tell it to do. If it's not working as intended, then I'd safely assume there is an error with the code..
– alowflyingpig
Dec 30 '18 at 22:46
The last line will fail if
lr
is less thanlr5
. Stop the code at End With and hover over the variables lr5 and lr to see their values. Another reason could be the second argument of the Autofill method, when Excel decides how to aufofill.– VBasic2008
Dec 30 '18 at 22:55
Try only with this One: Data.Range("B" & lr3, "D" & lr5).AutoFill....
– Ferdinando
Dec 30 '18 at 23:32
Sorry for the late response. I get a "AutoFill method of Range class failed" error
– GSC
Dec 31 '18 at 2:49