VBA dynamic paste after values

[Closed]
Report
-
 jocke -
Hello,

Iam trying to do more or less a copy paste Macro in excel. The date comes out in the following format:

Entity-----Account1----Account2
A----Value-----Value
B----Value-----Value
C----Value-----Value

What I want it to look like finished is

Entity-----Account----Value
A----Account1-----Value
B----Account1-----Value
C----Account1-----Value
A----Account1-----Value
B----Account1-----Value
C----Account1-----Value

What I am struggeling with is to make excel pick up the end of my table since the no of rows may change I need a dynamic table to extract the data I use:
Range(Selection, Selection.End(xlToRight)).Select
Which gives me the dynamic I need. However as soon as I try to get the data after the other it chooses a value to paste to as of example:

Range("D2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Range("C2").Select
Selection.End(xlDown).Select
Range("C34").Select
ActiveSheet.Paste

The C34 bit needs to be dynamic. Does anyone have a clever way of solving this?

Many thanks!

Joakim

1 reply

The end result should obviously be:

Entity-----Account----Value
A----Account1-----Value
B----Account1-----Value
C----Account1-----Value
A----Account2-----Value
B----Account2-----Value
C----Account2-----Value