How to extract first 50 rows

[chavanalini]

Hi,

I want to copy and paste first 50 rows from sheet1 to sheet2

Please help is there any formula to do this

Thank you

[alansidman]

Sub first50()
    Dim w1 As Worksheet
    Dim w2 As Worksheet
    Set w1 = Sheets("Sheet1")
    Set w2 = Sheets("Sheet2")
    Dim r As Range
    Dim lc As Long
    lc = w1.Cells(1, Columns.Count).End(xlToLeft).Column
    Set r = w1.Range(Cells(1, 1), Cells(50, lc))
        r.Copy w2.Range("A1")
        Application.CutCopyMode = False
End Sub

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

[chavanalini]

Thank you for your response

but i dont want to use VBA code please tell me is there any formula to do this exercise, sorry for taxing your time.

Thank you

[alansidman]

VBA or a manual copy and paste. The only other solution is to put formulas in sheet2

in A1 type =Sheet1!A1 and then manually copy down fifty rows. I don't see that this is any easier than a manual copy and paste.

For my own curiosity, why don't you want to use VBA?

[chavanalini]

Thanks for your reply

I dont have any problem to use VBA, but I have to give this excel file to client every time they have to press Alt-F8, I dont want to give extra work to them thats why I am asking about the formula.

If there is no formula is there any VBA code to generate automatically when I open the excel file instead of pressing Alt - F8

Thank you



Thanks