• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Short cut to avoid multiple copy and Paste in Excel sheet

Dear Team,

Help me out with the below query :

Attached is the sheet containing the method in which "N: no of times the header has to repeat as per the count in the below header.

For reference i have colored the column and the row. Kindly suggest me on how to complete the column without manual copy and paste.


Thanks in advance

Regards
Basavaraj K H
 

Attachments

  • Copy_1.xlsx
    9.2 KB · Views: 0
Here you go..

Code:
 =REPT(CONCATENATE(A$1&CHAR(10)),A2)

You can use REPT function and char(10) generates a new line character. The cells needs to have Wrap Text On for the cell where we are writing this formula so that we can see the text coming in multiple lines

Hope this is helpful...
 
Thank You for your response,
but it I want the Header "Apple iPhone 5S 16 GB" 4 times not in cell,
I have to paste in 4 row's,
same like column B & C respectively.
 
Here's a macro that would do what you want.
Code:
Sub CopyCells()
Dim rngNumbers As Range
Dim c As Range
Dim recCount As Long
Dim numCopy As Long
'Where does output go?
Const colPaste As String = "I"
'Where to start output?
recCount = 2
'Where is the range of numbers for input??
Set rngNumbers = Range("A2:C24")

Application.ScreenUpdating = False

For Each c In rngNumbers
    numCopy = c.Value
    If numCopy > 0 Then
        Cells(recCount, colPaste).Resize(numCopy).Value = Cells(1, c.Column)
        recCount = recCount + numCopy
    End If
Next c
Application.ScreenUpdating = True
End Sub
 
Back
Top