Cut + Paste selected data in column A while keeping row spacing

[jsnyder797]

I'm doing my quarterly BAS statement and I'm moving all business expenses from one column into their designated columns with their correct labels so I have a total of each column at the bottom of the table.
Anyhow, I'm attempting to cut or even copy and paste this numerical data from "column A" to another column and all I can achieve is a list of the copied data one row after another instead of staying in the source row.

^_^

 

[jsnyder797]

I'm doing my quarterly BAS statement and I'm moving all business expenses from one column into their designated columns with their correct labels so I have a total of each column at the bottom of the table.
Anyhow, I'm attempting to cut or even copy and paste this numerical data from "column A" to another column and all I can achieve is a list of the copied data one row after another instead of staying in the source row.

^_^

So what i hope to do is search through the transactions by name and select the values and paste them in the correct column in their specific rows

 

[Peter_SSs]

Welcome to the MrExcel board!

I'm attempting to cut or even copy and paste this numerical data from "column A" to another column and all I can achieve is a list of the copied data one row after another instead of staying in the source row.

Are you doing this ..
- manually?
- with vba code?
- something else?

 

[jsnyder797]

Welcome to the MrExcel board!


Are you doing this ..
- manually?
- with vba code?
- something else?

manually, copy paste

 

[Peter_SSs]

manually, copy paste

In that case if you copy (or cut) from say A35 why can't you just paste into H35 or Z35 etc?

 

[jsnyder797]

In that case if you copy (or cut) from say A35 why can't you just paste into H35 or Z35 etc?

for example i want to select all the costs for materials and place it in the column on the right labeled materials so i can get a total of each expense

 

[jsnyder797]

View attachment 66119

for example i want to select all the costs for materials and place it in the column on the right labeled materials so i can get a total of each expense

while keeping them in their correct date row

 

[Peter_SSs]

Thanks for the clarification.

For the future it is helpful if you can give sample data in a form that we can copy for testing so I suggest that you investigate the following.
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.

I don't thank that you can readily manually do what you are trying to do. Here is a suggested alternative though.

1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).
5. Back in the worksheet, instead of selecting the cells that you want moved, mark with an "x" (though it could actually be any text) the target cells where you want the values to go. That could be in a single column or multiple columns as shown in the mini-sheet below the vba code.
6. When you are ready to move the numbers in those rows, double-click the 'Amount' heading (C1 in my mini-sheet)
7. Steps 5 and 6 can be repeated if there are more amounts to allocate/move.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim c As Range, rngAllocation As Range
  Dim AmtRws As Long, AllocationCols As Long
  
  If Target.Value = "Amount" Then
    Cancel = True
    AmtRws = Range("A" & Rows.Count).End(xlUp).Row - Target.Row
    AllocationCols = Cells(1, Columns.Count).End(xlToLeft).Column - Target.Column
    On Error Resume Next
    Set rngAllocation = Target.Offset(1, 1).Resize(AmtRws, AllocationCols).SpecialCells(xlConstants, xlTextValues)
    On Error GoTo 0
    If Not rngAllocation Is Nothing Then
      For Each c In rngAllocation
        c.Value = Intersect(c.EntireRow, Target.EntireColumn).Value
        Intersect(c.EntireRow, Target.EntireColumn).ClearContents
      Next c
    End If
  End If
End Sub

22 06 02.xlsm
	C	D	E	F
1	Amount	Parking	Materials	Gas
2	918.82
3	244.92
4	667.38		x
5	113.41
6	856.07		x
7	336.20		x
8	348.23
9	503.18	x
10	470.29
11	613.10
12	423.69			x
13	391.78			x
14	572.70
15	574.65
16	172.04
17	705.81
18	708.90
19	602.62
Allocate to column

After I double-click cell C1:

22 06 02.xlsm
	C	D	E	F
1	Amount	Parking	Materials	Gas
2	918.82
3	244.92
4			667.38
5	113.41
6			856.07
7			336.20
8	348.23
9		503.18
10	470.29
11	613.10
12				423.69
13				391.78
14	572.70
15	574.65
16	172.04
17	705.81
18	708.90
19	602.62
Allocate to column

 

[jsnyder797]

that makes sense, ill give it a try