Select Specific Row in Active Column

[pjhtechnology]

Using Excel 2007 - this needs to also work in Excel 2003

I need to create a Macro for the following

Copy Row 1 thru 4 of the column for the Active.Cell to Row 1 thru 4 of the column to the right of the Active.Cell (the active cell will be wherever the user left the cursor in the column used)

If I can get through selecting the Rows I think I can figure out the rest

So far I found the code below which works to select Column A for the Active.Cell

Range("A" & ActiveCell.Row).Select
​

I have not be able to successfully modify this code to select Row 1 instead - Here's what I've tried

Range("1" & ActiveCell.Column).Select
Range(1 & ActiveCell.Column).Select
​

Obviously Selecting only Row 1 doesn't complete what I need - if you can help with the whole thing terrific - if you can help with any part of it also terrific

Thanks

 

[wolverineb]

This isn't the most elegant, but it's basic and should at least make sense for you:

Sub CopyTop()
    Dim lFromCol As Long, _
        lToCol As Long, _
        wksSheet As Worksheet, _
        lRowCounter As Long
        
    Set wksSheet = ActiveSheet
    lFromCol = ActiveCell.Column
    lToCol = lFromCol + 1
    
    For lRowCounter = 1 To 4
        wksSheet.Cells(lRowCounter, lToCol).Value = wksSheet.Cells(lRowCounter, lFromCol).Value
    Next lRowCounter
    
End Sub

 

[hiker95]

pjhtechnology,

If I understand you correctly.

In the below screenshot, cell B8 is the active cell before the macro:

Excel Workbook
	A	B	C
1	A1	B1
2	A2	B2
3	A3	B3
4	A4	B4
5	A5	B5
6	A6	B6
7	A7	B7
8	A8	B8
9	A9	B9
10	A10	B10
Sheet1

After the macro:

Excel Workbook
	A	B	C
1	A1	B1	B1
2	A2	B2	B2
3	A3	B3	B3
4	A4	B4	B4
5	A5	B5
6	A6	B6
7	A7	B7
8	A8	B8
9	A9	B9
10	A10	B10
Sheet1

The active cell is now A1, and, after the macro:

Excel Workbook
	A	B	C
1	A1	A1	B1
2	A2	A2	B2
3	A3	A3	B3
4	A4	A4	B4
5	A5	B5
6	A6	B6
7	A7	B7
8	A8	B8
9	A9	B9
10	A10	B10
Sheet1

If I select row 6, before the macro, there is not way to select an individual cell. The default active cell will always be in column A:

Excel Workbook
	A	B	C
1	A1	B1
2	A2	B2
3	A3	B3
4	A4	B4
5	A5	B5
6	A6	B6
7	A7	B7
8	A8	B8
9	A9	B9
10	A10	B10
Sheet1

After the macro:

Excel Workbook
	A	B	C
1	A1	A1
2	A2	A2
3	A3	A3
4	A4	A4
5	A5	B5
6	A6	B6
7	A7	B7
8	A8	B8
9	A9	B9
10	A10	B10
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub Test()
' hiker95, 03/19/2010
Dim ActCol As Long
ActCol = ActiveCell.Column
Range(Cells(1, ActCol), Cells(4, ActCol)).Copy Cells(1, ActCol + 1)
End Sub

Select a cell, and then run macro "Test".

 

[pjhtechnology]

Excellent - Both of these solutions Work for me - thanks for the quick replies