Problem with xldown using relative reference

I

iujfcra

New Member
Joined
Feb 20, 2018
Messages
4
Can anyone help me with this code? The range is only selecting 2 cells down from the active cell each time, instead of to the last cell with data.

Code: 
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1", Range("A1").End(xlDown)).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(-1, 1).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1", Range("A1").End(xlDown)).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(-2, 1).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1", Range("A1").End(xlDown)).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
Hi & welcome to the board
What does your data look like?
 
Last edited:
Upvote 0
Fluff said:
Hi & welcome to the board
What does your data look like?
Click to expand...

Hi. My data is in a rectangle and I am trying to copy down from a selection and then move up and to the right (forming a a step) and perform copy paste into a range below the step cell. I am new to VBA and have not been able to get this to work correctly.


0.00%0.00%0.00%0.00%0.00%5.00%6.00%
0.00%0.00%0.00%0.00%4.00%5.00%6.00%
0.00%0.00%0.00%3.00%4.00%5.00%6.00%
0.00%0.00%2.00%3.00%4.00%5.00%6.00%
0.00%1.00%2.00%3.00%4.00%5.00%6.00%
0.00%1.00%2.00%3.00%4.00%5.00%6.00%
0.00%1.00%0.00%3.00%4.00%5.00%0.00%
1.00%0.00%0.00%3.00%4.00%0.00%0.00%

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>
 
Upvote 0
Is that how your data looks before you run the macro? After you run the macro? Or what you want it to look like?
Also which cell is the ActiveCell when you run the macro?
 
Upvote 0
Fluff said:
Is that how your data looks before you run the macro? After you run the macro? Or what you want it to look like?
Also which cell is the ActiveCell when you run the macro?
Click to expand...

B15 is the ActiveCell which is copied to the range B16:B18. Then it will move to C14 and copy that percentage to C15:C18. I am using relative reference since the starting point will change each month and the macro will be used for different data sets.

BEFORE
ABCDEFG
110.00%0.00%0.00%0.00%0.00%5.00%6.00%
120.00%0.00%0.00%0.00%4.00%5.00%6.00%
130.00%0.00%0.00%3.00%4.00%5.00%6.00%
140.00%0.00%2.00%3.00%4.00%5.00%6.00%
150.00%1.00%2.00%3.00%4.00%5.00%6.00%
160.00%1.00%2.00%3.00%4.00%5.00%6.00%
170.00%1.00%0.00%3.00%4.00%5.00%0.00%
181.00%0.00%0.00%3.00%4.00%0.00%0.00%
AFTER
ABCDEFG
110.00%0.00%0.00%0.00%0.00%5.00%6.00%
120.00%0.00%0.00%0.00%4.00%5.00%6.00%
130.00%0.00%0.00%3.00%4.00%5.00%6.00%
140.00%0.00%2.00%3.00%4.00%5.00%6.00%
150.00%1.00%2.00%3.00%4.00%5.00%6.00%
160.00%1.00%2.00%3.00%4.00%5.00%6.00%
170.00%1.00%2.00%3.00%4.00%5.00%0.00%
181.00%1.00%2.00%3.00%4.00%5.00%0.00%

<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>
 
Upvote 0
Ok, try this
Code: 
   Range(activecell.Offset(1, 0), activecell.End(xlDown)).Value = activecell.Value
   activecell.Offset(-1, 1).Select
   Range(activecell.Offset(1, 0), activecell.End(xlDown)).Value = activecell.Value
   activecell.Offset(-1, 1).Select
   Range(activecell.Offset(1, 0), activecell.End(xlDown)).Value = activecell.Value
 
Upvote 0
Fluff said:
Ok, try this
Code: 
   Range(activecell.Offset(1, 0), activecell.End(xlDown)).Value = activecell.Value
   activecell.Offset(-1, 1).Select
   Range(activecell.Offset(1, 0), activecell.End(xlDown)).Value = activecell.Value
   activecell.Offset(-1, 1).Select
   Range(activecell.Offset(1, 0), activecell.End(xlDown)).Value = activecell.Value
Click to expand...


Thanks. That works!
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
VBA Questions - Copying dynamic data from one tab to another and deleting filtered rows
Replies
1
Views
153
DanteAmor
DanteAmor
J
  • Question
VBA to select partial filename and go back and forth between two files to copy paste
Replies
1
Views
156
shknbk2
S
P
  • Solved
Counter in For Each loop
Replies
2
Views
171
Possum17
P
A
  • Question
Macro to update list to last row on another sheet copy source from multiple sheets
Replies
0
Views
296
Azamamir
A
P
  • Solved
Need help with macro Dynamic Range??
Replies
6
Views
156
pennbowl
P

Forum statistics

Threads
1,214,413
Messages
6,119,372
Members
448,888
Latest member
Arle8907

Share this page

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