Macro to copy paste single cell at the time from the list

Henceman

New Member
Joined
Oct 9, 2017
Messages
42
I have following simple code, which takes values from A column and pastes them to B2, one by one, and runs some code on each of these values, which is not relevant to this question.


VBA Code:
Range("A13").Copy Range("B2")
Range("A14").Copy Range("B2")
Range("A15").Copy Range("B2")
Range("A16").Copy Range("B2")
Range("A17").Copy Range("B2")
...

Problem is, that the amount of values I have in A column is not fixed, there might be 5, might be 15 or more. I cannot tell macro to simply copy and paste values from 13-to 17.

How can I set this range to be dynamic, so if there are only two values, macro stops after these two?
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

ChrisFoster

Board Regular
Joined
Jun 21, 2019
Messages
127
Office Version
  1. 365
Platform
  1. Windows
Try;

VBA Code:
    With ActiveSheet
    .Range("A1" & .Cells(Rows.Count, "A").End(xlUp).Row).Copy Range("B2")
    End With
 

Henceman

New Member
Joined
Oct 9, 2017
Messages
42
Thank you for feedback, but can you elaborate what your code does?
Right now I think it will quickly copy paste values from A column to B2, But I need to run my own code after every copy-paste action, so I dont know where will my code fit in.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub Henceman()
   Dim Cl As Range
   
   For Each Cl In Range("A13", Range("A" & Rows.Count).End(xlUp))
      Cl.Copy Range("B2")
      'your code here
   Next Cl
End Sub
 

Henceman

New Member
Joined
Oct 9, 2017
Messages
42

ADVERTISEMENT

Seems to run indefinitely, I included two sample rows of my code to be more clear.

VBA Code:
    Dim Cl As Range
   
   For Each Cl In Range("A13", Range("A" & Rows.Count).End(xlUp))
      Cl.Copy Range("B2")
    Call <mycommandresfresh>
    Call <mycommandsubmit>
    
   Next Cl

1. Should take value from A13 and paste to B2
2. Run my code.
2. Should take value from A14 and paste (overwrite previous value) to B2
4. Run my code
5. Repeat until there are no more consecutive values in A column (it should not scan until the end of the worksheet that maybe there is value on A45900 cell!)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. Windows
It will run until the last used cell in col A. If that is not what you want, then what do you want?
 

Henceman

New Member
Joined
Oct 9, 2017
Messages
42

ADVERTISEMENT

As I said, it needs to check sequential all squares like A13-A17 and do the mentioned copy-paste action. If there is no value in A17 for example, then last cell the operation will be done is on A16, so only 4 cells. If there is no value on A17, but there is value on A18, then it should still abort the check for remaining squares.

This marcro seemed to go through every cell there is on A column, which was not desired. To say bluntly, I expect the amount of values to be between 2 and 30, no more or less.
 

ChrisFoster

Board Regular
Joined
Jun 21, 2019
Messages
127
Office Version
  1. 365
Platform
  1. Windows
Fluff's code, specifically the below part selects the range to be from A13 down to the last row that has data in column A. So when it gets to a blank cell it will stop - that's what you want it to do, isn't it?

VBA Code:
For Each Cl In Range("A13", Range("A" & Rows.Count).End(xlUp))
 

Henceman

New Member
Joined
Oct 9, 2017
Messages
42
No, looks like it does not stop, i cleared the entire column down from my values and yes, then it indeed completed in the expected amount of time.
But I have hundreds of cells in A column filled with data, which is not the subject of this macro and while I do have 30 empty cells separating the data, the macro still skips the empty space and starts working on the data it should not. I am not sure I can make it any more clearer.


Cell for pasting the data (B2)
A13 data (data macro should use)
A14 data (data macro should use)
A15 data (data macro should use)
data not relevant to macro and should not be copied and pasted
data not relevant to macro and should not be copied and pasted
...

 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. Windows
How will the macro know which cells to work on & which cells to ignore?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,216
Messages
5,594,885
Members
413,947
Latest member
gizmolucy

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
Top