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

Multi tool use
Multi tool use












1















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.










share|improve this question

























  • 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 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











  • Sorry for the late response. I get a "AutoFill method of Range class failed" error

    – GSC
    Dec 31 '18 at 2:49
















1















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.










share|improve this question

























  • 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 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











  • Sorry for the late response. I get a "AutoFill method of Range class failed" error

    – GSC
    Dec 31 '18 at 2:49














1












1








1


0






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.










share|improve this question
















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








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 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











  • 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











  • 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













  • 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












2 Answers
2






active

oldest

votes


















0














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





share|improve this answer
























  • Thanks for the answer Gene!

    – GSC
    Jan 2 at 15:39



















0














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.






share|improve this answer























    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


















    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









    0














    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





    share|improve this answer
























    • Thanks for the answer Gene!

      – GSC
      Jan 2 at 15:39
















    0














    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





    share|improve this answer
























    • Thanks for the answer Gene!

      – GSC
      Jan 2 at 15:39














    0












    0








    0







    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





    share|improve this answer













    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






    share|improve this answer












    share|improve this answer



    share|improve this answer










    answered Dec 31 '18 at 15:33









    GeneGene

    35216




    35216













    • 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





    Thanks for the answer Gene!

    – GSC
    Jan 2 at 15:39













    0














    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.






    share|improve this answer




























      0














      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.






      share|improve this answer


























        0












        0








        0







        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.






        share|improve this answer













        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.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Dec 31 '18 at 15:56









        ja72ja72

        18.2k349104




        18.2k349104






























            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














            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





















































            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
            am,6,qb4gtM0ri8,Nq dCXD eb,GZbaxZQZpA BkG Js Y kKI OAV,LNeZIv4jDKK6,8Q

            Popular posts from this blog

            Monofisismo

            Angular Downloading a file using contenturl with Basic Authentication

            Olmecas