Conditional cut paste

[ashani]

hi
I wonder if someone could pls guide me - is it possible either via VBA/formula that wherever the "Date Range" mentioned in the column C13 onwards than cut the data from there to all the way until the next "Date Range" mentioned in the column and paste it in the available column in row 2.

I have attached the screenshot :

Thank you

 

[Joe4]

I am afraid that screen shot isn't that helpful, as we have no context, as we have no idea what range that is in, or where your other data resides.
Can you post some other screen prints that include:
- Column and row headers so we can see where exactly everything resides
- Your other data on this sheet
- An image of what your data looks like initially
- An image of your desired result

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[ashani]

Hi
I’ve a restriction in my pc and can’t use xl2bb. Here's the screenshot so basically I want the system to look into Column A and where it says date range XYZ from A10 than move the data underneath to column B onwards in transpose way.

 

[Automatrix]

Can you show us an example of what the result should look like?

 

[ashani]

Hi @Automatrix

The result should be :

Column A10 to A21 will move to Column B3 onwards and Column A22:A28 will move to Column C3 onwards and so on.

thank you

 

[Automatrix]

Hi @Automatrix

The result should be :

Column A10 to A21 will move to Column B3 onwards and Column A22:A28 will move to Column C3 onwards and so on.

thank you

Like this? Is there supposed to be data in A10:A21?

Book5
	A	B	C	D
1
2
3	Date Range XYZ	Date Range XYZ	Date Range XYZ	Date Range XYZ
4
5
6
7
8
9
10	Date Range XYZ
11
12
13
14
15
16
17
18
19
20
21
22	Date Range XYZ
23
24
25
26
27
28
Sheet3

 

[ashani]

Yes that’s correct there is data in those cells

 

[ashani]

I'm using this formula but it does it only in the range, however I would like in the whole database, where it says "Data Range XYZ" in Column C than VBA to move all the data from there until the next Cell mentioned "Data Range XYZ" to next available column in row 2.

    Range("C21").Select
    ActiveCell.FormulaR1C1 = "Data Range XYZ"
    Range("C21:S39").Select
    Selection.Cut
    Selection.End(xlToRight).Select
    Selection.End(xlToRight).Select
    Selection.End(xlToRight).Select
    Selection.End(xlToRight).Select
    Selection.End(xlToRight).Select
    Selection.End(xlToRight).Select
    Selection.End(xlToRight).Select
    Selection.End(xlToRight).Select
    Selection.End(xlToLeft).Select
    Range("T21").Select
    Selection.End(xlUp).Select
    Range("T2").Select
    ActiveSheet.Paste
    Range("T26").Select
    Selection.End(xlToLeft).Select
    Range("C26").Select
    Selection.End(xlDown).Select
    ActiveCell.FormulaR1C1 = "Data Range XYZ"
    Range("C40:C58").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range("C40:S58").Select
    Selection.Cut
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Range("AK2").Select
    ActiveSheet.Paste
    Range("AI29").Select
    Selection.End(xlToLeft).Select
    ActiveWindow.LargeScroll Down:=1
    Range("A60").Select
    ActiveWindow.LargeScroll Down:=1
    Range("A91").Select
    ActiveWindow.LargeScroll Down:=-1
    Range("C59").Select
    ActiveCell.FormulaR1C1 = "Data Range XYZ"
    Range("C59:S77").Select
    Selection.Cut
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Range("BB2").Select
    ActiveSheet.Paste
    Range("BB3").Select
    Selection.End(xlToLeft).Select
    Range("BA25").Select
    Selection.End(xlToLeft).Select
    Range("A26").Select
    ActiveWindow.LargeScroll Down:=1
    Range("C57").Select
    ActiveWindow.LargeScroll Down:=1
    Range("C78").Select
    ActiveCell.FormulaR1C1 = "Data Range XYZ"
    Range("C78:J96").Select
    Selection.Cut
    Range("BS2").Select
    ActiveSheet.Paste
End Sub

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D
1
2
3	Date Range XYZ	Date Range XYZ	Date Range XYZ	Date Range XYZ
4
5
6
7
8
9
10	Date Range XYZ
11
12
13
14
15
16
17
18
19
20
21
22	Date Range XYZ
23
Details

Cell Formulas
Range	Formula
B3:D3	B3	=TOROW(A3:A10000,1)
Dynamic array formulas.