Hi excel users.
What I need is to copy values from a cell down a range.
I have some sample data below (end result). To explain, F3 (DV cell) will be copied and the value is posted in the cells below, up to the last cell on the left. The same is true for the rest.
With the VBA code – I know it is poorly written with all the selects but I think it helps illustrate what I need and I tried to make it dynamic to adapt to all of the ranges I need it for.
It works as is, but (other than needing it to be written better of course)
1) It pastes over the original top cell which I don’t want it to – I will have a heading there. So F3 will then become the cell that is copied and etc for the rest.
2) And can it be done without having to reproduce it x amount of times. Case statement maybe?
Thanks.
<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD></TD><TD>product 1</TD><TD></TD><TD></TD><TD>product 21</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">1</TD><TD>product 1</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>product 21</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">2</TD><TD>product 1</TD><TD></TD><TD style="TEXT-ALIGN: right">2</TD><TD>product 21</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">3</TD><TD>product 1</TD><TD></TD><TD style="TEXT-ALIGN: right">3</TD><TD>product 21</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">4</TD><TD>product 21</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD></TD><TD>product 23</TD><TD></TD><TD style="TEXT-ALIGN: right">5</TD><TD>product 21</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right">1</TD><TD>product 23</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right">2</TD><TD>product 23</TD><TD></TD><TD></TD><TD>product 3</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right">3</TD><TD>product 23</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>product 3</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: right">4</TD><TD>product 23</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>product 3</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: right">5</TD><TD>product 23</TD><TD></TD><TD style="TEXT-ALIGN: right">3</TD><TD>product 3</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">4</TD><TD>product 3</TD></TR></TBODY></TABLE>
Excel tables to the web >> Excel Jeanie HTML 4
What I need is to copy values from a cell down a range.
I have some sample data below (end result). To explain, F3 (DV cell) will be copied and the value is posted in the cells below, up to the last cell on the left. The same is true for the rest.
With the VBA code – I know it is poorly written with all the selects but I think it helps illustrate what I need and I tried to make it dynamic to adapt to all of the ranges I need it for.
It works as is, but (other than needing it to be written better of course)
1) It pastes over the original top cell which I don’t want it to – I will have a heading there. So F3 will then become the cell that is copied and etc for the rest.
2) And can it be done without having to reproduce it x amount of times. Case statement maybe?
Thanks.
<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD></TD><TD>product 1</TD><TD></TD><TD></TD><TD>product 21</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">1</TD><TD>product 1</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>product 21</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">2</TD><TD>product 1</TD><TD></TD><TD style="TEXT-ALIGN: right">2</TD><TD>product 21</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">3</TD><TD>product 1</TD><TD></TD><TD style="TEXT-ALIGN: right">3</TD><TD>product 21</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">4</TD><TD>product 21</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD></TD><TD>product 23</TD><TD></TD><TD style="TEXT-ALIGN: right">5</TD><TD>product 21</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right">1</TD><TD>product 23</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right">2</TD><TD>product 23</TD><TD></TD><TD></TD><TD>product 3</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right">3</TD><TD>product 23</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>product 3</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: right">4</TD><TD>product 23</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>product 3</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: right">5</TD><TD>product 23</TD><TD></TD><TD style="TEXT-ALIGN: right">3</TD><TD>product 3</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">4</TD><TD>product 3</TD></TR></TBODY></TABLE>
Excel tables to the web >> Excel Jeanie HTML 4
Code:
Sub copy1()
Range("F3").Select
Selection.Copy
ActiveCell.Offset(1, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
End Sub
Sub copy2()
Range("F8").Select
Selection.Copy
ActiveCell.Offset(1, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
End Sub
Sub copy3()
Range("I3").Select
Selection.Copy
ActiveCell.Offset(1, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
End Sub
Sub copy4()
Range("I10").Select
Selection.Copy
ActiveCell.Offset(1, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
End Sub