copy paste data based on previous column

[HKB]

Basically I have an excel spreadsheet which can be updated daily depending on the workflow.


I have Create a Simple macro which is copy data in column ("E2:E42") as based on Column ("D2:D42").
but many time data in column "D" more than or less than 42 ,but my macro always copy up to "E42",
every time I have to manually enter or delete data.
What I need that is a macro that can automatic adjust range which have data in column "D"

Thanks in advance

HKB

 

[vletm]

Instead of 42, You could use like ...
"D2:D" & Activesheet.Cells(Rows.Count, 4).End(xlUp).Row

 

[HKB]

Hi vletm
please guide me how? here is my code

Sub Macro1()
'
' Macro1 Macro
'

'
    Dim vFile As Variant
   
   
    'Showing Excel Open Dialog Form
    vFile = Application.GetOpenFilename("Excel Files (*.xlsx)," & _
    "*.xl*", 1, "Select Excel File", "Open", False)

    'If Cancel then exit
    If TypeName(vFile) = "Boolean" Then
        Exit Sub
    End If

    'Open selected file
    Workbooks.Open vFile
    Range("A1:M1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Nifty50Bhav.Activate
    Sheets("Bhav Copy").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("N1").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("A1").Select
    Sheets("ind_nifty50list").Select
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "='Bhav Copy'!R[1]C[6]"
    Range("E3").Select
    Selection.FormulaArray = _
        "=INDEX('Bhav Copy'!R1C6:R3000C6,MATCH(RC[-2]&RC[-1],'Bhav Copy'!R1C2:R3000C2&'Bhav Copy'!R1C13:R3000C13,0))"
    Range("E3").Select
    Selection.Copy
    Range("E4:E42").Select
    ActiveSheet.Paste
    Range("F2").Select
    Application.CutCopyMode = False
    Range("A1").Select
    Cells.Select
    Selection.NumberFormat = "0.00"
    Rows("2:2").Select
    Selection.NumberFormat = "[$-409]dd-mmm-yy;@"
    Cells.EntireColumn.AutoFit
    lastcol = Cells(2, Columns.Count).End(xlToLeft).Column + 1
    Range("E2:E100").Copy
    Cells(2, lastcol).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
    Range("E2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("A1").Select
   
End Sub

 

[vletm]

HKB ... hmm?
I have Create a Simple macro
which is copy data in column ("E2:E42") as based on Column ("D2:D42").
and 'Your code' has rows:
Range("E3").Select
Selection.Copy
Range("E4:E42").Select
ActiveSheet.Paste
What would be connection of those?You copy "E3" and
after that You paste it to "E4:E42" ...
Please try to think one more time
what do You really want to do and
after that You could
Record that to Macro.

 

[HKB]

HKB ... hmm?
I have Create a Simple macro
which is copy data in column ("E2:E42") as based on Column ("D2:D42").
and 'Your code' has rows:
Range("E3").Select
Selection.Copy
Range("E4:E42").Select
ActiveSheet.Paste
What would be connection of those?You copy "E3" and
after that You paste it to "E4:E42" ...
Please try to think one more time
what do You really want to do and
after that You could
Record that to Macro.

yes,
i want copy "E3" and paste it to "E4:E42" may be less or more depend on Column "D"

 

[vletm]

hmm...
Instead of

Range("E3").Select
Selection.Copy
Range("E4:E42").Select
ActiveSheet.Paste

try next

Range("E3").copy destination:= range("E4:E" & Cells(Rows.Count, 4).End(xlUp).Row)

Couldn't test at all... no sample file!

 

[HKB]

hmm...
Instead of

Range("E3").Select
Selection.Copy
Range("E4:E42").Select
ActiveSheet.Paste

try next

Range("E3").copy destination:= range("E4:E" & Cells(Rows.Count, 4).End(xlUp).Row)

Couldn't test at all... no sample file!

YES, IT'IS....Thanks vletm...It's Work..Perfect..