VBA Help: Select Range and Paste in another Column

[mvlinlb]

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:

	A	B	C
1	Date	Variable A	Variable B
2	10/27/15	65
3		65
4	10/26/15	67
5		60

<tbody>
</tbody>

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

	A	B	C
1	Date	Variable A	Variable B
2	10/27/15	65	65
3		65
4	10/26/15	67
5		60	60

<tbody>
</tbody>

Thank you for your time and assistance.

Mark

 

[JLGWhiz]

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.

 

[Lres81715]

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,

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

 

[JLGWhiz]

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,

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

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

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.

 

[Lres81715]

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,

[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

 

[mvlinlb]

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.