get available row

[thysonj]

I am colating the data from almost 700 workbooks into one. I'd like to grab data in file A/worksheet 1 and copy it to file z/worksheet 1 and then loop to file b/worksheet 1 and copy it to file z/worksheet 1. I need a way of keeping track of which row is the next row to paste too. My data should work out like this...

file A:
A B C D
1 a d f
2 w e g
3 c v n h
4 f h
5 g

+
file B:
A B C D E F
1 w h t g
2 b n b
3 g m g
4 h m
5

=
file Z:
A B C D E
1 a d f
2 w e g
3 c v n h
4 f h
5 g
6 w h t g
7 b n b
8 g m g
9 h m

I can easily copy the data and paste it but i can't figure out how to keep track of the row. My first solution did not work since I was using a simple incrementer so if i copied 4 rows it would paste them into rows 1-4 but then on the next file it would paste whatever data it copied starting at row 2 therefore over writing three rows(2-3) of data from file A.

This seems like an easy one but I can't figure out how to "get" that next available row after I have pasted data.

 

[cLFlaVA]

If you're pasting one row directly after another, and there is no data beneath the row, you can use the UsedRange.Rows.Count property of the worksheet.

Dim sht as Worksheet
Set sht = Sheets("Sheet1")

Dim i as Integer
i = sht.UsedRange.Rows.Count

*cLFlaVA
----------------------------
Lois: "Peter, you're drunk!"
Peter: "I'm not drunk, I'm just exhausted from stayin' up all night drinking!