0
Sheets("SITE Model").Select
    Range("C8").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("C9").Select
    Sheets("NETWORK Model").Select
    Range("D37").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("D27").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

I want to specify x and y by a cell reference C8 and C9 respectively. Any help would be appreciated!

Updated photo attached to make the accumulating more clear. The first section is the input from "SITE Model". The second section is accumulating pasted values. The the third section totals 1 and 2 and then pastes the new answer into section 2 as values.

Jack
  • 13
  • 3
  • Sorry, to be clear it is the "1" that is to change from 1 to 2 to 3 etc to a max number as defined by y (C9) – Jack Jun 09 '20 at 11:57
  • What do you want to accomplish? No need to select anything... Selecting, Activating only consumes Excel resources and do not bring any benefit. If you want to express the two ranges/cells according to their row and column you can use `Cells(8, 3)` for C8 and `Cells(9, 3)` for C9. This representation means row 8 and column 3 (C). Such a way of defining will return the cells of the active pages... – FaneDuru Jun 09 '20 at 12:02
  • Sorry, my explanation wasn't overly clear! The 1 value is pasted into a model in worksheet "SITE Model" which fetches data from a database. The relevant data is then available on sheet "NETWORK Model". The copying function is pasting values such that when the model runs from 1-1000 it creates an accumulating total. I just need to run various sections of the database (e.g 100-150 which is set by x and y) to see the accumulated totals. Does that help a little? – Jack Jun 09 '20 at 12:24
  • If before your explanation I was rather confused, now I am more confused... Is it somewhere in your explanation a reference to invoked x and y you were asking about? Where in your code or in your 'explanation' is something (concret) mentioned about the way of accumulating? Can you edit your post and show us the sheet like it is and then how do you need/wont it (or another sheet) to look? With relevant data and explanations to understand something about the process you need to apply... – FaneDuru Jun 09 '20 at 12:30
  • Read this https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba A screenshot might help as your question is rather confusing as it stands. Where do x and y fit into your posted code? – SJR Jun 09 '20 at 12:46
  • Sure: (1) I have a model which spits out lines of revenue, this is in sheet "SITE Model". The revenue changes based on the numbers on a single cell input (which would be 1-1000). It does this because dependent on that number it changes variables in the model which have been plucked from a database. (2) Specific revenue lines from this model appear in the sheet "NETWORK Model" (it does this though a simple cell referring in the "SITE Model" sheet.(3) Once I have these values in the "NETWORK Model" through the simple referencing. – Jack Jun 09 '20 at 12:57
  • Sorry stuck on word count: I then create an accumulating total by adding the previous runs values with the new ones. Then pasting these new values as the 'previous' values so that it creates an accumulating total. Added a screenshot (sanitised for NDA reasons) but hope it helps. – Jack Jun 09 '20 at 12:59
  • This is still rather hard to follow. We don't need the detail of your system. Strip it down to the basics in terms of an Excel request, and perhaps add expected output to your picture. You might like to read [mcve]. – SJR Jun 09 '20 at 14:26

0 Answers0