vba find last row and copy all data above in column A

Question

I have been going round in circles with this as I have searched and there is a lot similar to what I want to do but nothing exact. My data exists in column A, I need to be able to find the last row in Column A, copy all of the data above (including the last line) and paste to a new sheet in a new workbook. Any help would be hugely appreciated.

 Selects appropriate worksheet - Non-MyPayFINAL
  Sheets("Non-MyPay FINAL").Select


  'Selects all data in column A and copies to clipboard
  Range("A1", Range("A1").End(xlDown)).Select
  Selection.Copy

 'Add a new workbook
  Workbooks.Add
 'Paste selected values from previous sheet
  Selection.PasteSpecial Paste:=xlPasteValues

 'Build SaveAs file name (for CSV file)
  MySaveFile = Format(Now(), "DDMMYYYY") & "NonMyPayFINAL" & ".CSV"
  'Save template file as...(for CSV file)
  ActiveWorkbook.SaveAs ("S:\MERIT OUTPUTS FOLDER\MSI Recruitment 
  Limited\" & MySaveFile), FileFormat:=xlCSV

  'Build SaveAs file name (for Txt file)
  MySaveFile = Format(Now(), "DDMMYYYY") & "NonMyPayFINAL" & ".Txt"
  'Save template file as...(for Txt file)
   ActiveWorkbook.SaveAs ("S:\MERIT OUTPUTS FOLDER\MSI Recruitment 
   Limited\" & MySaveFile), FileFormat:=xlTextWindows

Why the last row? Isn't just copying the whole column sufficient? What is the destination to paste? A blank workbook? Then getting the last row isn't needed at all. — Rik Sportel
– Rik Sportel, Commented Jul 11, 2017 at 10:45
The issue I have had up until now is that if I just select Column A, copy and paste into my new workbook and save then everything is fine. The worksheet this gets copied into is then saved as both a CSV and txt file. Now say the first set of data I load in has 100 rows (its all in one column) then everything is fine but if the next set has only 50 rows, when I save it as a text file, thjere are 50 rows of blank lines left at the end which cause an issue. Does that make sense? I have updated the above to include the code. — Dyhouse
– Dyhouse, Commented Jul 11, 2017 at 10:55
Blank rows - This is possible if there's some mark-up or whatever setup. See answer - It'll take the last populated value with end(xlDown) and it'll ignore markup. — Rik Sportel
– Rik Sportel, Commented Jul 11, 2017 at 11:16
I now get an error on "With" line saying Runtime error 9 Subscript out of range.... I worry that I haven't declared/inserted this correctly.... — Dyhouse
– Dyhouse, Commented Jul 11, 2017 at 11:37
The With statement just refers to the Object you're calling the methods / properties of. In my answer below, you'll need a Worksheet with the name "CopyFromThisOne". If you have "Sheet 1", change it to "Sheet 1" or as above to Worksheets("Non-MyPay FINAL") or whatever one you need. — Rik Sportel
– Rik Sportel, Commented Jul 11, 2017 at 13:16

Rik Sportel · Accepted Answer · 2017-07-11 11:10:49Z

1

To copy the whole column A from the sheet "CopyFromThisOne" to a new, empty workbook:

Sub CopyWholeColumnA()
Dim wb as Workbook
Set wb = Workbooks.Add

With ThisWorkbook.Worksheets("CopyFromThisOne")
    .Range("A:A").Copy wb.Worksheets(1).Range("A1")
End with
End Sub

To copy row 1 to the last occupied row in a continuous range into a new workbook:

Sub CopyColumnAOccupied()
Dim lastrow as integer
Dim wb as workbook
Set wb = Workbooks.add

With ThisWorkbook.Worksheets("CopyFromThisOne")
    lastrow = .Range("A1").End(xlDown).Row
    .Range("A1:A" & lastrow).Copy wb.Worksheets(1).Range("A1")
End With
End Sub

Although, based on your question (new worksheet in new workbook) I'd say you don't need to find any row and you can just copy the whole column.

edited Jul 11, 2017 at 11:10

answered Jul 11, 2017 at 10:53

Rik Sportel

2,6781 gold badge17 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mrig Over a year ago

I guess you missed Copy Range line in your second code.

Dyhouse Over a year ago

Your answer is great, did you see my previous comment which hopefully provided a touch more context as to why a straight forward copy and paste doesn't really work too well.

Rik Sportel Over a year ago

Yeah I saw - line dropped off half way, fixed it.

Collectives™ on Stack Overflow

vba find last row and copy all data above in column A

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related