Search code examples
excelvbafor-loopcopy-paste

Copy and pasting a looped result down a column in another sheet

This is my current code and I want to change the last line.

Dim Answer As VbMsgBoxResult
Answer = MsgBox("Did you refresh the model?", vbYesNo + vbQuestion + vbDefaultButton2)
If Answer = vbYes Then

Sheets("SITE Model").Range("C8").Value = 0
Sheets("SITE Model").Range("C6").Value = "DB"

For i = Sheets("NETWORK Model").Range("C8") To Sheets("NETWORK Model").Range("C9")
Sheets("SITE Model").Select
Range("C8").Select
ActiveCell.FormulaR1C1 = i
Sheets("NETWORK Model").Select
Range("D37:EF41").Select
Selection.Copy
Range("D27").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


Sheets("NETWORK Model").Range("C62").CopySheets("SITE Model").Range ("K2")

How do I change the range (k2) down a row for the next loop?

Thank you!

Solution

  • You need to make your reference to cell K2 dynamic so that it changes with each iteration of the loop.

    Include a new variable, then use that for the row number. Remember to make sure to +1 before the end of the loop so when it skips back to the top for the next iteration, the variable has increased by one, thereby moving the row number down.

    Example:

    Dim lRowNum As Long

  'Insert this line before the start of your loop.
  lRowNum = 2

  ' Replace your last line with these two lines.
  Sheets("NETWORK Model").Range("C62").CopySheets("SITE Model").Range ("K" & lRowNum)
  lRowNum = lRowNum + 1

    General points:

    Rather than use .select and .copy there are more efficient ways to reference the range you need and transfer/copy it to the desired location. I also highly recommend that you always qualify a .range statement with the prefix of worksheet. This will ensure your code will always execute on the correct worksheet and correct area of cells. For example, instead of -

    Sheets("NETWORK Model").Select
Range("D37:EF41").Select
Selection.Copy
Range("D27").Select
Selection.PasteSpecial...

    You can write this as follows in just one line;

    Sheets("NETWORK Model").Range("D27:EF31").Value = Sheets("NETWORK Model").Range("D37:EF41").Value

    (I may have misunderstood exactly where you want to copy the NETWORK Model range to, but this should give you an idea of how you can construct the statement to achieve the same task in a more efficient manner. Of course, you may prefer to use .select .copy and .pastespecial so up to you!)