Select and copy Rows

[mdorey]

hello all,

How can i select an unknown range of rows from the second until the second before last and then copy them?

Thanks

 

[footoo]

Rows("2:" & Range([A1], ActiveSheet.UsedRange).Rows.Count - 1).Copy

 

[mdorey]

Rows("2:" & Range([A1], ActiveSheet.UsedRange).Rows.Count - 1).Copy

Hi thanks for the reply.

It really select the second rows but it does not select the 2 before the last used

 

[mdorey]

Hi thanks for the reply.

It really select the second rows but it does not select the 2 before the last used

Got it it works now :P hahaha thanks

 

[mdorey]

Rows("2:" & Range([A1], ActiveSheet.UsedRange).Rows.Count - 1).Copy

I want to put that code in workbook1, workbook1 will open several workbooks (six) with vba.

How can i adapt that code to select the range from the other workbooks and past on workbook1.sheet(named with the name of the files open)?

Here is a sample of the code

Sub OpenData()


Dim myValue As Variant
myValue = InputBox("File name?")


    ChDir "L:\..."
    Workbooks.OpenText Filename:= _
        "L:\...\PRO " & myValue & ".txt" _
        , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True

 

[footoo]

Assuming column A on each sheet in Workbook1 can be used to find the next available blank row :

ActiveSheet.Rows("2:" & Range([A1], ActiveSheet.UsedRange).Rows.Count - 1).Copy _
Workbooks("Workbook1").Sheets(myValue).Cells(Rows.Count, "A").End(xlUp)(2).EntireRow