Although the earlier answer has its merits:
1) I would not use COUNTA, because if there are empty cells in the row or column, the cells at the bottom or on the right will be ignored.
2) I would never rely on the user who selects the correct sheet to use before running the macro; especially with so many sheets.
My reaction to the question is that you installed Macro Record, roamed your book, and then stopped recording. You pick one thing, then another. You are viewing the sheets. For me, most of the statements are not clumsy, they are meaningless.
Below is the answer to your question about finding the last row of column A, but this is more of a tutorial on finding range sizes, getting data from a range, and then placing it elsewhere. This seems like most of what you are trying to do with a minimal understanding of VBA. I apologize if this criticism is unfair, but this is the impression that gives me your question.
Sub Test() Dim RowS01Max As Integer Dim Sheet1Data() As Variant ' With Sheets("Sheet1") allows you to access data within worksheet Sheet1 ' without selecting it. ' Range("A1:C11") refers to a range within the active sheet ' .Range("A1:C11") refers to a range within the sheet identified in the ' With statement. ' ^ Note the dot With Sheets("Sheet1") ' Rows.Count is the number of rows for the version of Excel you are using. ' .Cells(Rows.Count, "A") address the bottom row of column A of worksheet ' Sheet1. ' .Cells(Rows.Count, 1) refer to column A by number. ' End(xlUp) is the VBA equivalent of Ctrl+Up. ' If you positioned the cursor at the bottom of column A and pressed ' Ctrl+Up, the cursor would jump to the last row in column A with a value. ' The following statement gets that row number without actually moving ' the cursor. RowS01Max = .Cells(Rows.Count, "A").End(xlUp) ' The following statement loads the contents of range A1:C11 of ' Sheets("Sheet1") into array Sheet1Data. Sheet1Data = .Range("A1:C11").Value ' This is the same statement but the range is specified in a different way. ' .Cells(Row,Column) identifies a single cell within the sheet specified in ' the With statement. .Cells(1,1) identifies row 1, column 1 which is A1. '. Cells(11, "C") identifies row 11, column C which is C11. Sheet1Data = .Range(.Cells(1, 1), .Cells(11, "C")).Value ' This statement uses RowS01Max to specify the last row Sheet1Data = .Range(.Cells(1, 1), .Cells(RowS01Max, 1)).Value ' In all three examples above, the contents of the specified range will ' be loaded to array Sheet1Data. Whichever range you pick, Sheet1Data ' will always be a two dimensional array with the first dimension being ' the row and the second dimension being the column. ' In the first two examples Sheet1Data(5,3) contains the contents ' of cell C5. In the third example, I have only loaded column A but the ' array will still has two dimensions but the only permitted value for the ' second dimension is 1. ' The following statement writes the contents of Sheet1Data to column "E" .Range(.Cells(1, 5), .Cells(RowS01Max, 5)).Value = Sheet1Data End With With Sheets("Sheet2") ' The following statement writes the contents of Sheet1Data to column "E" ' of worksheet Sheet2. .Range(.Cells(1, 5), .Cells(RowS01Max, 5)).Value = Sheet1Data End With End Sub
Do not despair! Most of us started with a macroscanner and still use it to find syntax for an unfamiliar team. Browse other questions. Some ask about exotic functionality, but many of them relate to data movement, by an experienced programmer, in simple ways. Set up several books with a polling problem. Copy and paste the solution into the module. Go through F8 (see Debugger), switch between Excel and the editor, see what happens with the worksheet, and move the cursor over the variable to see its current value. Spend half a day. You will be amazed at how quickly this starts to make sense. Good luck and good programming.