VBA Help: Select Range and Paste in another Column

mvlinlb

New Member
Joined
Sep 30, 2015
Messages
5
Hi All:

In the below example, I am trying to look for populated values in Column A (range is A2:A5). If there are, (i.e. this would apply to A2 and A5) then copy/paste the corresponding row value from Col B into Col C. Initially, I tried to use =IF(A2>0, B2, ""). This worked, however I have I have another macro linked to Col C such that when I hit the "enter" key something else happens. Not sure how its possible to both copy active cells from B to C but also to simulate an "enter" on those selected cells that were populated in Col C?

Example Before:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Date[/TD]
[TD]Variable A[/TD]
[TD]Variable B[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]10/27/15[/TD]
[TD]65[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD]65[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]10/26/15[/TD]
[TD]67[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD]60[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Example After (new values are reflected in C2 and C5. Enter Key is pressed on those individual cells):

A
B
C
1
DateVariable AVariable B
2
10/27/156565
3
65
4
10/26/1567
5
6060

<tbody>
</tbody>




Thank you for your time and assistance.

Mark
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Re: VBA Help: Select Rancge and Paste in another Column

If you have an event code macro tied to column C and you install another macro which changes values in column C, it could could corrupt your data in addition to causing a perpetual loop in the event code. You need to post the macro that applies to that sheet for evaluation before adding additional code.
 
Upvote 0
Re: VBA Help: Select Rancge and Paste in another Column

Mark,

Pretty much what JLGWhiz said, attempt to fix that event macro first. Event macros are more trouble than they are worth most of the time.

But, to solve your issue with a Macro,

Code:
Sub RangePaste()

Dim ws      As Worksheet 'Current Worksheet
Dim LastRow As Long      'Used to find the last row. (Important, place before code needed)
Dim iRow    As Long


    Set ws = Sheets("Sheet1") 'Current Worksheet working in
    LastRow = ws.Range("B1").CurrentRegion.Rows.Count 'Finds the last row in the sheet.
   
    For iRow = 2 To LastRow Step 1
        If Cells(iRow, "A") <> "" Then 'If cells in column A are not blank
            Cells(iRow, "B").Copy Destination:=Cells(iRow, "C") ' This is for one Cell to one Cell.
'If you want to use a Range of cells instead, use this code instead of the one above.
'                        sh1.Range("A" & iRow, "B" & iRow).Copy Destination:=sh1.Range("C" & iRow, "D" & iRow)
        End If
    Next


End Sub
 
Upvote 0
Re: VBA Help: Select Rancge and Paste in another Column

Mark,

Pretty much what JLGWhiz said, attempt to fix that event macro first. Event macros are more trouble than they are worth most of the time.

But, to solve your issue with a Macro,

Code:
Sub RangePaste()

Dim ws      As Worksheet 'Current Worksheet
Dim LastRow As Long      'Used to find the last row. (Important, place before code needed)
Dim iRow    As Long


    Set ws = Sheets("Sheet1") 'Current Worksheet working in
    LastRow = ws.Range("B1").CurrentRegion.Rows.Count 'Finds the last row in the sheet.
   
    For iRow = 2 To LastRow Step 1
        If Cells(iRow, "A") <> "" Then 'If cells in column A are not blank
            Cells(iRow, "B").Copy Destination:=Cells(iRow, "C") ' This is for one Cell to one Cell.
'If you want to use a Range of cells instead, use this code instead of the one above.
'                        sh1.Range("A" & iRow, "B" & iRow).Copy Destination:=sh1.Range("C" & iRow, "D" & iRow)
        End If
    Next


End Sub
@Lres81715, If there is a blank row in the used range this
Code:
LastRow = ws.Range("B1").CurrentRegion.Rows.Count 'Finds the last row in the sheet.
Will give an incorrect last row with data. The CurrentRegion only includes contigent cells, so it would also give a false value if used to get the last column with data. The ones that usually gets the job done is
Code:
LastRow = ws.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
lastCol = ws.Cells.Find("*", , xlFormulas, xlPart, xlByColumns, xlPrevious).column
These allow VBA to go to the extreme right or bottom of a sheet and work back toward the top left of the sheet to find the first data of any kind, visible or not, to get the last row or last column.
 
Upvote 0
Re: VBA Help: Select Rancge and Paste in another Column

JLGWhiz,

Yes correct,

I work on reports where the only rows I'm concerned with have completed filled columns. The ones that aren't are incomplete and are to be ignored until the account manager is finished submitting the order. But for clarification,
Code:
[COLOR=#333333]LastRow = ws.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row [/COLOR]
L[COLOR=#333333]astCol = ws.Cells.Find("*", , xlFormulas, xlPart, xlByColumns, xlPrevious).column[/COLOR]
are better at that. Thanks for the correction
 
Upvote 0
Re: VBA Help: Select Rancge and Paste in another Column

JLGWhiz and Lres81715: Thank you both for your help. There was an issue (as you said) in putting it all together and being corrupted. I was able to separate and Lres81715, the code you provided worked like a charm so thank you.

Have a great weekend.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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