VBA: Code to find first empty cell in column A

[Puppies72]

Hi all,

I'm struggling my way through a macro with help from you all and am bringing in data from several sheets into a single sheet - what I need to do is to copy certain ranges (which I know how to select and do now) but then I need to paste it beneath the last active cell in column A which can vary.

So for instance if the first part of my macro copies in 450 entries into column A and the next part selects 100 entries it needs to paste those starting from A451 (the next empty cell).

The number of entries copied in will vary every time which is why I need a flexible way to do this.

As always help gratefuly appreciated!

 

[wut]

Worksheets("Sheet1").Range("A1").End(xlDown).Row + 1

Literally copied and pasted from a google search. Does what you want?

 

[Puppies72]

wow thanks for the quick reply - I've had mixed results with some of the googled stuff I've found - will try this in the morning!

 

[Excelworld]

I'm having the same issue as Puppies72 - the user 'wut' has been banned I'm guess for posting silly replies....

any help on this would be great

 

[njimack]

there are numerous ways of determining the last row or first blank row...

Range("A" & Rows.Count).End(xlup).Offset(1).Row
Cells(Rows.Count,1).End(xlup).Offset(1).Row
UsedRange.Rows.Count

 

[Excelworld]

My issue is a little more complex - the users all have a form on one worksheet within a workbook to fill in and send to me. I have a tracker that tracks information like name, ref number etc so I need the macro to copy that information from the the forms and paste it to a new row on the tracker. so it needs to check the next empty row and enter the informatin from the the form in the relivant cells - name, ref number, date etc

 

[njimack]

My issue is a little more complex - the users all have a form on one worksheet within a workbook to fill in and send to me. I have a tracker that tracks information like name, ref number etc so I need the macro to copy that information from the the forms and paste it to a new row on the tracker. so it needs to check the next empty row and enter the informatin from the the form in the relivant cells - name, ref number, date etc

that doesn't sound so complex...

Dim LastRow as Long
LastRow = Sheets("Sheet1").Cells(Rows.Count,1).End(xlup).Offset(1).Row

Sheets("Sheet2").Range("A1").Copy Sheets("Sheet1").Cells(LastRow,1)

 

[Excelworld]

lol ok in my head it sounded more complex - I bow down to your superior coding skills

thanks a lot for the help - I'll give this a whirl now

 

[Excelworld]

that doesn't sound so complex...

Dim LastRow as Long
LastRow = Sheets("Sheet1").Cells(Rows.Count,1).End(xlup).Offset(1).Row

Sheets("Sheet2").Range("A1").Copy Sheets("Sheet1").Cells(LastRow,1)

I might be out of my depth here as I still can't work out a few things

The tracker I'm using is stored on my desktop (for ease) and it's call AndyTrackerQ3.xls. Now the individual files being sent to me via email will have a different persons names on each form so tomsmith.xls terryjones.xls Neilgoodwin.xls might be all in one email - the idea being that I open a form check it click a button and it copies the info from cells AC74, AD74, AE74, AF74, AG74, AH74, AI74 into my tracker. The first available row that these will copy into would be starting C3, D4, E4, F4 , G4, H4, J4 and the next form I open would have to copy in the line below so C4, D5, E5, F5 etc or essentially just the next available row<o></o>

Hope what I'm trying to do/say makes sense!

 

[njimack]

If you're looking for a full solution, you'll need to provide a lot more detail:
- which file (tracker or emailed file) will contain the macro?
- will both files be open when the macro is run?
- what are the relevant sheet names?
- is there a field in the tracker that lists the persons name? If so, which column?
- in your example, you said the data will be copied to C3, D4, E4 etc. Why is column C a different row to the other columns?