?
Solved

Excel Macro: Cut and Paste relative to starting position

Posted on 2011-04-20
26
Medium Priority
?
261 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:vacpartswarehouse
  • 14
  • 12
26 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35434982
What is the code that you are using?

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35435011
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
 

Author Comment

by:vacpartswarehouse
ID: 35435030
    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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:vacpartswarehouse
ID: 35435064
Any way to write it so that Offset knows to start at column B every time? Even if my selected cell varies.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35435101
vacpartswarehouse: You should avoid using .Select as much as possible.

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

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35435114
Show me the complete macro that you have. I'll modify that for you.

Sid
0
 

Author Comment

by:vacpartswarehouse
ID: 35435121
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
 

Author Comment

by:vacpartswarehouse
ID: 35435169
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
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 2000 total points
ID: 35435175
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
 

Author Comment

by:vacpartswarehouse
ID: 35435227
That looks like it would work. I tried this in place of my current macro but it doesn't do anything.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35435235
Can you upload your workbook so that I can test it?

Sid
0
 

Author Comment

by:vacpartswarehouse
ID: 35435321
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35435365
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
 

Author Comment

by:vacpartswarehouse
ID: 35435394
That is correct.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35435476
I just tried it and it works. Please see Screencast.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35435479
oops the screencast didn't load. 1 sec.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35435517
Here is the screencast.

Sid
SiddharthRout-446304.flv
0
 

Author Comment

by:vacpartswarehouse
ID: 35435563
That looks good!
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35435570
:-D

Do you mean the MACRO or the Screencast :)

Sid
0
 

Author Comment

by:vacpartswarehouse
ID: 35435629
Both! :)
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35435638
LOL :)

Sid
0
 

Author Comment

by:vacpartswarehouse
ID: 35435640
Is this the macro you sent me earlier?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35435642
Yes

Sid
0
 

Author Comment

by:vacpartswarehouse
ID: 35435664
You're right! It works! Thanks a ton!
0
 

Author Closing Comment

by:vacpartswarehouse
ID: 35435674
You're a GENIUS!
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35435688
Glad to be of help :)

Sid
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question