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

jsnyder797

New Member
Joined
Jun 1, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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.

^_^
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
 
Upvote 0
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?
 
Upvote 0
In that case if you copy (or cut) from say A35 why can't you just paste into H35 or Z35 etc?
1654133417758.png


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
 
Upvote 0
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.

VBA Code:
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
CDEF
1AmountParkingMaterialsGas
2918.82
3244.92
4667.38x
5113.41
6856.07x
7336.20x
8348.23
9503.18x
10470.29
11613.10
12423.69x
13391.78x
14572.70
15574.65
16172.04
17705.81
18708.90
19602.62
Allocate to column


After I double-click cell C1:

22 06 02.xlsm
CDEF
1AmountParkingMaterialsGas
2918.82
3244.92
4667.38
5113.41
6856.07
7336.20
8348.23
9503.18
10470.29
11613.10
12423.69
13391.78
14572.70
15574.65
16172.04
17705.81
18708.90
19602.62
Allocate to column
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top