Hi Alllen

[Guest]

Hi Allen,

Thanks for your initiative and solution Really appreciate everyone's
support and contribution ot the forum/community.
I'm back again with the qruery on the same topic/challenge for me

This time..as you suggested I'm more descriptive...I'm describing my
problem/challenge below

1.
I have a sheet with 3 columns (MULTIPLE ROWS..Row # will change from project
to project so Row number is dynamic/generic)
2.
Col A has some value say..Months/Years (NOT restricted to 12 numbers).
3.
Col B, Col C have some data.
4.
User will be prompted to select a ROW from the row number specified on the
left hand side of the work sheet (Using-->Set Range =
Application.InputBox(prompt:="PLEASE CLICK ON THE ROW NUMBERS LISTED on THE
LEFT HAND SIDE TO SELECT A ROW", Type:=8)
5.
Suppose user selects Row# 5, then
the data in Row#2 of Column A (neglecting row#1 which is column title) which
is A2,
the data in Row#5 of Column A (neglecting row#1 which is column title) which
is A5, (ROW WHICH WAS SELECTED BY THE USER)
the data in last Row#n of Column A will be copied and pasted in the
respective cells under column D (that is D2, D5 and Dn)
6. Since User had selected Row#5 above..
the data in Row#2 of Column B till Row#5 of Col B will be selected/copied
(that is values in B2, B3, B4 and B5) and pasted in Row#2 though Row#5 of
Column E respectively.
7. Since User had selected Row#5 above..
the data in Row#5 of Column B till Row#n of Col B will be selected/copied
(that is values in B5, B6....Bn) and pasted in Row#5 though Row#n of Column F.
8. Since User had selected Row#5 above..
the data in Row#2 of Column C till Row#5 of Col C will be selected/copied
(that is values in C2, C3, C4 and C5) and pasted in Row#2 though Row#5 of
Column G respectively.
9. Since User had selected Row#5 above..
the data in Row#5 of Column C till Row#n of Col C will be selected/copied
(that is values in C5, C6....Cn) and pasted in Row#5 though Row#n of Column H.


What my code does not do
Step7, Sept9, and Step5 partly.

I have taken care of column titles in my code (but not have explained above)
they can be ignored..I'm OK with data copying and not with column titles..
*******************************
Sub Test()
'
'
' Let user select a row of values by clicking on the row number listed
on the work sheet

Dim Rng As Range
On Error Resume Next
Set Rng = Application.InputBox(prompt:="PLEASE CLICK ON THE ROW NUMBERS
LISTED on THE LEFT HAND SIDE TO SELECT A ROW", Type:=8)
If Rng Is Nothing Then
MsgBox "Operation Cancelled"
Else
Rng.Select
With Selection.Interior
.ColorIndex = 7
.Pattern = xlSolid
End With
''''''''''''''''''''''''''''
'Populating project date fields from column A

Dim kLastRow As Long
Dim k As Long

kLastRow = Cells(Rows.Count, "A").End(xlUp).Row
If Not Rng Is Nothing Then
Range("A2").Copy Range("E2")
Rng.Copy Cells(Rng.Row, "E")
'Cells(kLastRow, "A").Copy Cells(kLastRow, "E")
End If

''''''''''''''''''''''''''''''''''
'Populating column F and Col G

Dim jLastRow As Long
Dim j As Long

jLastRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("B1").Resize(Rng.Row).Copy Range("F1")
Rng.Offset(1, 0).Resize(iLastRow - Rng.Row).Copy Range("G2")

'Populating column H and Col I

Dim lLastRow As Long
Dim l As Long

lLastRow = Cells(Rows.Count, "C").End(xlUp).Row
Range("C1").Resize(Rng.Row).Copy Range("H1")
Rng.Offset(1, 0).Resize(iLastRow - Rng.Row).Copy Range("I2")

End If

End Sub
********************************************
I have a table (which is dynamic and will change from Project to Project
so
I can NOT HARD CODE THE MACRO)
Col A Col B Col C
Month Planned Actual Row# 1
Jan 50 48 Row# 2
Feb 55 54 Row# 3
Mar 58 60 Row# 4
Apr 60 62 Row# 5
May 65 65 Row# 6
Jun 68 65 Row# 7
Jul 75 70 Row# 8
Aug 85 84 Row# 9
Sep 100 95 Row# 10

2. User runs the macro and the required out put is

Col A Col D Col E Col F Col G

Jan 50 48
55 54
58 60
Apr 60 62 60 62
65 65
68 65
75 70
85 84
Sep 100 95
***********************************************

 

[Guest]

Try this:

Sub Test()
'
'
' Let user select a row of values by clicking on the row number listed
'on the work sheet

Dim Rng As Range
On Error Resume Next
Set Rng = Application.InputBox(prompt:="PLEASE CLICK ON THE ROW NUMBERS
LISTED on THE LEFT HAND SIDE TO SELECT A ROW", Type:=8)
If Rng Is Nothing Then
MsgBox "Operation Cancelled"
Else
Rng.Select
With Selection.Interior
.ColorIndex = 7
.Pattern = xlSolid
End With
''''''''''''''''''''''''''''
'Populating project date fields from column A

Dim ilastrow As Long

ilastrow = Cells(Rows.Count, "A").End(xlUp).Row
If Not Rng Is Nothing Then
Range("A2").Copy Range("E2")
Range("A" & Rng.Row).Copy Range("E" & Rng.Row)
Range("A" & ilastrow).Copy Range("E" & ilastrow) '
End If

''''''''''''''''''''''''''''''''''
'Populating column F and Col G

Range("b2").Resize(Rng.Row - 1, 2).Copy Range("F2")

'Populating column H and Col I

Range("B" & Rng.Row).Resize(ilastrow - Rng.Row + 1, 2).Copy Range("H" &
Rng.Row)


End If

End Sub