Excel Macro: Cut and Paste relative to starting position

I have tried using a macro with relative references to do this, but the macro uses ActiveCell.Offset(1, -1) to specify where it gets its information from. I need it to go to the same column every time even if my starting point varies.
MacroHelp.jpg
vacpartswarehouseAsked:
Who is Participating?
 
SiddharthRoutCommented:
Yes, I did.

Is this what you are trying?

Sub Sample()
    Dim r As Long, NextR As Long
    Dim ws As Worksheet
    
    Set ws = ActiveSheet
    
    r = ActiveCell.Row
    c = ActiveCell.Column
    NextR = r + 1
    
    ws.Range("B" & NextR & ":N" & NextR).Copy ws.Cells(r, c)
    ws.Range("B" & NextR & ":N" & NextR).Clear
    
End Sub

Open in new window


Sid
0
 
SiddharthRoutCommented:
What is the code that you are using?

Sid
0
 
SiddharthRoutCommented:
Here is the logic.

    ActiveCell.Offset(1).Select '<~~ Move one cell down
    ActiveCell.Offset(0, 1).Select '<~~ Move one to right
    ActiveCell.Offset(0, 1).Select '<~~ Move one to right from current cell
    ActiveCell.Offset(0, 1).Select '<~~ Move one to right from current cell and so on

Sid
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
vacpartswarehouseAuthor Commented:
    ActiveCell.Offset(1, -1).Range("A1").Select
    ActiveWindow.SmallScroll ToRight:=7
    ActiveCell.Range("A1:N1").Select
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Selection.Cut
    ActiveCell.Offset(1, -1).Range("A1").Select
    Selection.Insert Shift:=xlToRight
    ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
    Selection.Delete Shift:=xlUp

Open in new window

0
 
vacpartswarehouseAuthor Commented:
Any way to write it so that Offset knows to start at column B every time? Even if my selected cell varies.
0
 
SiddharthRoutCommented:
vacpartswarehouse: You should avoid using .Select as much as possible.

Let me understand what exactly are you trying to do :)

Sid
0
 
SiddharthRoutCommented:
Show me the complete macro that you have. I'll modify that for you.

Sid
0
 
vacpartswarehouseAuthor Commented:
I generated this macro using the record feature in excel. I also enabled Use Relative References while recording. Did you view the image I uploaded?
0
 
vacpartswarehouseAuthor Commented:
Sub LineUp()
'
' LineUp Macro
' 
'
' Keyboard Shortcut: Ctrl+Shift+U
'
    ActiveCell.Offset(1, -1).Range("A1").Select
    ActiveWindow.SmallScroll ToRight:=7
    ActiveCell.Range("A1:N1").Select
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Selection.Cut
    ActiveCell.Offset(1, -1).Range("A1").Select
    Selection.Insert Shift:=xlToRight
    ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
    Selection.Delete Shift:=xlUp
End Sub

Open in new window

0
 
vacpartswarehouseAuthor Commented:
That looks like it would work. I tried this in place of my current macro but it doesn't do anything.
0
 
SiddharthRoutCommented:
Can you upload your workbook so that I can test it?

Sid
0
 
vacpartswarehouseAuthor Commented:
This file contains the same data as in the image above. Unfortunately I cannot upload the original file due to size and sensitive information.
MacroTesting.xlsx
0
 
SiddharthRoutCommented:
This would do :)

Now from you first post, If I select say cell D2 then B3:N3 should be copied to D2? Right?

Sid
0
 
vacpartswarehouseAuthor Commented:
That is correct.
0
 
SiddharthRoutCommented:
I just tried it and it works. Please see Screencast.

Sid
0
 
SiddharthRoutCommented:
oops the screencast didn't load. 1 sec.

Sid
0
 
SiddharthRoutCommented:
Here is the screencast.

Sid
SiddharthRout-446304.flv
0
 
vacpartswarehouseAuthor Commented:
That looks good!
0
 
SiddharthRoutCommented:
:-D

Do you mean the MACRO or the Screencast :)

Sid
0
 
vacpartswarehouseAuthor Commented:
Both! :)
0
 
SiddharthRoutCommented:
LOL :)

Sid
0
 
vacpartswarehouseAuthor Commented:
Is this the macro you sent me earlier?
0
 
SiddharthRoutCommented:
Yes

Sid
0
 
vacpartswarehouseAuthor Commented:
You're right! It works! Thanks a ton!
0
 
vacpartswarehouseAuthor Commented:
You're a GENIUS!
0
 
SiddharthRoutCommented:
Glad to be of help :)

Sid
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.