Copy and paste a formula using a loop

-1

What I'm trying to do is copy a formula from cell 'Q9' and paste it from "Q10 to Q11", then select from "Q10 to Q11" and paste it as value. Then repeat it from the next empty cell copy a formula from cell 'Q9' once again and paste it from "Q12 to Q22" finally select from "Q12 to Q22" and paste it as value and so on and so on... until hit the target row as "Q200".

In the meantime I have tried to resolve it using the following logic, but is not working as expected:

Public Sub CopyRows(startRow As Long, startColumn As Integer, _ 

        numberOfRows As Integer, numberOfColumns As Integer, repeatCount As Integer) 

    Dim targetRow As Long 

    Dim i As Integer 



    ActiveSheet.Range(Cells(startRow, startColumn) _ 

            , Cells(startRow + numberOfRows - 1, _ 

            startColumn + numberOfColumns - 1)).Copy 

    For i = 1 To repeatCount 

        targetRow = startRow + (numberOfRows * i) 

        ActiveSheet.Cells(targetRow, startColumn).Paste

        ActiveSheet.Cells(targetRow, startColumn).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

                :=False, Transpose:=False

    Next I

End Sub

Hopefully somebody can help me to figure it out...

edited Jan 22 at 18:05

Scott

15.8k113990

asked Jan 22 at 16:24

user988433

Welcome to Super User! Please note that superuser.com is not a free script/code writing service. If you tell us what you have tried so far (include the scripts/code you are already using) and where you are stuck then we can try to help with specific problems. You should also read How do I ask a good question?.

– DavidPostill♦
Jan 22 at 16:25

VBA can do this, but if that's all you need... And if you are trying to copy the SAME formula down a column... then preceded your cell references with a $. This makes the references absolute rather relative. Dragging a formula with reference to A$1 will lock the row reference to row 1. You can do this for any combination of rows and columns. (A$1 or $A1 or $A$1). Then drag the formula to the desired distance (using the "bead" in the lower right of cell) or double click the bead to automatically drag to the last row. Then you can copy and paste values.

– primohacker
Jan 22 at 18:16

I'm trying to copy a formula, look for the next empty cell within same column and paste it in buckets of 10 rows, then copy & paste it as value, after that, look for the next empty cell within the column and repeat the procedure...

– user988433
Jan 22 at 18:31

You may have some typos in the question because the row numbers in the description don't match this, making the question confusing.

– fixer1234
Jan 23 at 0:13

add a comment |

-1

In the meantime I have tried to resolve it using the following logic, but is not working as expected:

Public Sub CopyRows(startRow As Long, startColumn As Integer, _ 

        numberOfRows As Integer, numberOfColumns As Integer, repeatCount As Integer) 

    Dim targetRow As Long 

    Dim i As Integer 



    ActiveSheet.Range(Cells(startRow, startColumn) _ 

            , Cells(startRow + numberOfRows - 1, _ 

            startColumn + numberOfColumns - 1)).Copy 

    For i = 1 To repeatCount 

        targetRow = startRow + (numberOfRows * i) 

        ActiveSheet.Cells(targetRow, startColumn).Paste

        ActiveSheet.Cells(targetRow, startColumn).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

                :=False, Transpose:=False

    Next I

End Sub

Hopefully somebody can help me to figure it out...

edited Jan 22 at 18:05

Scott

15.8k113990

asked Jan 22 at 16:24

user988433

Welcome to Super User! Please note that superuser.com is not a free script/code writing service. If you tell us what you have tried so far (include the scripts/code you are already using) and where you are stuck then we can try to help with specific problems. You should also read How do I ask a good question?.

– DavidPostill♦
Jan 22 at 16:25

VBA can do this, but if that's all you need... And if you are trying to copy the SAME formula down a column... then preceded your cell references with a $. This makes the references absolute rather relative. Dragging a formula with reference to A$1 will lock the row reference to row 1. You can do this for any combination of rows and columns. (A$1 or $A1 or $A$1). Then drag the formula to the desired distance (using the "bead" in the lower right of cell) or double click the bead to automatically drag to the last row. Then you can copy and paste values.

– primohacker
Jan 22 at 18:16

I'm trying to copy a formula, look for the next empty cell within same column and paste it in buckets of 10 rows, then copy & paste it as value, after that, look for the next empty cell within the column and repeat the procedure...

– user988433
Jan 22 at 18:31

You may have some typos in the question because the row numbers in the description don't match this, making the question confusing.

– fixer1234
Jan 23 at 0:13

add a comment |

-1

In the meantime I have tried to resolve it using the following logic, but is not working as expected:

Public Sub CopyRows(startRow As Long, startColumn As Integer, _ 

        numberOfRows As Integer, numberOfColumns As Integer, repeatCount As Integer) 

    Dim targetRow As Long 

    Dim i As Integer 



    ActiveSheet.Range(Cells(startRow, startColumn) _ 

            , Cells(startRow + numberOfRows - 1, _ 

            startColumn + numberOfColumns - 1)).Copy 

    For i = 1 To repeatCount 

        targetRow = startRow + (numberOfRows * i) 

        ActiveSheet.Cells(targetRow, startColumn).Paste

        ActiveSheet.Cells(targetRow, startColumn).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

                :=False, Transpose:=False

    Next I

End Sub

Hopefully somebody can help me to figure it out...

edited Jan 22 at 18:05

Scott

15.8k113990

asked Jan 22 at 16:24

user988433

In the meantime I have tried to resolve it using the following logic, but is not working as expected:

Public Sub CopyRows(startRow As Long, startColumn As Integer, _ 

        numberOfRows As Integer, numberOfColumns As Integer, repeatCount As Integer) 

    Dim targetRow As Long 

    Dim i As Integer 



    ActiveSheet.Range(Cells(startRow, startColumn) _ 

            , Cells(startRow + numberOfRows - 1, _ 

            startColumn + numberOfColumns - 1)).Copy 

    For i = 1 To repeatCount 

        targetRow = startRow + (numberOfRows * i) 

        ActiveSheet.Cells(targetRow, startColumn).Paste

        ActiveSheet.Cells(targetRow, startColumn).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

                :=False, Transpose:=False

    Next I

End Sub

Hopefully somebody can help me to figure it out...

microsoft-excel vba

edited Jan 22 at 18:05

Scott

15.8k113990

asked Jan 22 at 16:24

user988433

edited Jan 22 at 18:05

Scott

15.8k113990

asked Jan 22 at 16:24

user988433

edited Jan 22 at 18:05

Scott

15.8k113990

edited Jan 22 at 18:05

Scott

15.8k113990

edited Jan 22 at 18:05

Scott

15.8k113990

asked Jan 22 at 16:24

user988433

asked Jan 22 at 16:24

user988433

asked Jan 22 at 16:24

user988433

Welcome to Super User! Please note that superuser.com is not a free script/code writing service. If you tell us what you have tried so far (include the scripts/code you are already using) and where you are stuck then we can try to help with specific problems. You should also read How do I ask a good question?.

– DavidPostill♦
Jan 22 at 16:25

VBA can do this, but if that's all you need... And if you are trying to copy the SAME formula down a column... then preceded your cell references with a $. This makes the references absolute rather relative. Dragging a formula with reference to A$1 will lock the row reference to row 1. You can do this for any combination of rows and columns. (A$1 or $A1 or $A$1). Then drag the formula to the desired distance (using the "bead" in the lower right of cell) or double click the bead to automatically drag to the last row. Then you can copy and paste values.

– primohacker
Jan 22 at 18:16

I'm trying to copy a formula, look for the next empty cell within same column and paste it in buckets of 10 rows, then copy & paste it as value, after that, look for the next empty cell within the column and repeat the procedure...

– user988433
Jan 22 at 18:31

You may have some typos in the question because the row numbers in the description don't match this, making the question confusing.

– fixer1234
Jan 23 at 0:13

add a comment |

Welcome to Super User! Please note that superuser.com is not a free script/code writing service. If you tell us what you have tried so far (include the scripts/code you are already using) and where you are stuck then we can try to help with specific problems. You should also read How do I ask a good question?.

– DavidPostill♦
Jan 22 at 16:25

VBA can do this, but if that's all you need... And if you are trying to copy the SAME formula down a column... then preceded your cell references with a $. This makes the references absolute rather relative. Dragging a formula with reference to A$1 will lock the row reference to row 1. You can do this for any combination of rows and columns. (A$1 or $A1 or $A$1). Then drag the formula to the desired distance (using the "bead" in the lower right of cell) or double click the bead to automatically drag to the last row. Then you can copy and paste values.

– primohacker
Jan 22 at 18:16

I'm trying to copy a formula, look for the next empty cell within same column and paste it in buckets of 10 rows, then copy & paste it as value, after that, look for the next empty cell within the column and repeat the procedure...

– user988433
Jan 22 at 18:31

You may have some typos in the question because the row numbers in the description don't match this, making the question confusing.

– fixer1234
Jan 23 at 0:13

Welcome to Super User! Please note that superuser.com is not a free script/code writing service. If you tell us what you have tried so far (include the scripts/code you are already using) and where you are stuck then we can try to help with specific problems. You should also read How do I ask a good question?.

– DavidPostill♦
Jan 22 at 16:25

VBA can do this, but if that's all you need... And if you are trying to copy the SAME formula down a column... then preceded your cell references with a $. This makes the references absolute rather relative. Dragging a formula with reference to A$1 will lock the row reference to row 1. You can do this for any combination of rows and columns. (A$1 or $A1 or $A$1). Then drag the formula to the desired distance (using the "bead" in the lower right of cell) or double click the bead to automatically drag to the last row. Then you can copy and paste values.

– primohacker
Jan 22 at 18:16

I'm trying to copy a formula, look for the next empty cell within same column and paste it in buckets of 10 rows, then copy & paste it as value, after that, look for the next empty cell within the column and repeat the procedure...

– user988433
Jan 22 at 18:31

You may have some typos in the question because the row numbers in the description don't match this, making the question confusing.

– fixer1234
Jan 23 at 0:13

add a comment |

0

active

oldest

votes

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1397072%2fcopy-and-paste-a-formula-using-a-loop%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

0

active

oldest

votes

0

active

oldest

votes

draft saved

draft discarded

Thanks for contributing an answer to Super User!

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');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Jtdylktuy