Copy and pasting data to the next row in VBA Macro

[tanyaleblanc]

In VBA I'm trying to copy just the 21 in "Cost Center: 21 - Northern NS" and just the 30, etc, in "Cost Center: 30" as you see below.

when I do this in a macro it copies the 21 all the way to the bottom and ignores the 30 and 40, how can I fix this? I tried to tag this, but I received an error stating it was too long.

[TABLE="width: 401"]
<tbody>[TR]
[TD]Cost Center: 21 - Northern NS[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD]Cost Center: 30 -[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Cost Center: 40 -[/TD]
[/TR]
[TR]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]40[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Can you please supply the macro.

 

[tanyaleblanc]

Sorry, I'm putting in actual cell numbers, eg A3, but these will change month over month.

Windows("Finance Billing.xls").Activate
lastrow = Cells(Rows.Count, "E").End(xlUp).Row
Range("A3").Select
Selection.AutoFill Destination:=Range("A3:A" & lastrow)
Range("A3:A" & lastrow).Select
Range("A78").Select
Selection.AutoFill Destination:=Range("A78:A" & lastrow)
Range("A78:A" & lastrow).Select
Range("A153").Select
Selection.AutoFill Destination:=Range("A153:A" & lastrow)

 

[Fluff]

That code is just copying the entire cell contents, not just part of it.
Is that what you want?

 

[tanyaleblanc]

no, in this scenario, I just want to copy each number in the cost center row down to the next one. I also have the same scenario where I want to copy the entire cell down to the next one, for example, I have NB, NS, NL, I want to copy NB down to the last cell before NS starts and then copy NS down to the last cell before NL starts. If you can provide both that would be great.

 

[Fluff]

Do you mean

Excel 2013/2016
	A
3	Cost Center: 21 - Northern NS
4	21
5	21
6	21
7	Cost Center: 30 -
Summary

or

Excel 2013/2016
	A
3	Cost Center: 21 - Northern NS
4	Cost Center: 21 - Northern NS
5	Cost Center: 21 - Northern NS
6	Cost Center: 21 - Northern NS
7	Cost Center: 30 -
Summary

If the former will the number always be two digits?

 

[tanyaleblanc]

I mean the first example, and no the number will not always be two digits, I could have 1 as a cost center, but 01 would work too I think.

 

[Fluff]

How about

Sub tanyaleblanc()
   Dim Rng As Range
   For Each Rng In Range("A:A").SpecialCells(xlBlanks).Areas
      Rng.Value = Trim(Split(Split(Rng.Offset(-1).Resize(1), ":")(1), "-")(0))
   Next Rng
End Sub

 

[tanyaleblanc]

Beautiful, works perfect, how can I use this code to do the same thing for the below, so that NB will copy down until the last row and then NS will copy down to the last row and so on and so forth.

[TABLE="width: 304"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]short_name[/TD]
[TD]full_account[/TD]
[/TR]
[TR]
[TD]NB[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1428384 [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2374106 [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2433415 [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3097195 [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3344827 [/TD]
[/TR]
[TR]
[TD]NS[/TD]
[TD]3344827 [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3344827 [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3547075 [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]931693 [/TD]
[/TR]
[TR]
[TD]NF[/TD]
[TD]1046459 [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1057646 [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1063990 [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1100726 [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1109231 [/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Like

Sub tanya()
   With Range("A2", Range("B" & Rows.Count).End(xlUp).Offset(, -1))
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
      .Value = .Value
   End With
End Sub