This answer is an attempt to get your started.
If you search the internet for "Excel VBA Tutorial" you will get many hits. Try a few because they are all different and pick the one you like best. Work through that tutorial to get a general feel for Excel. I do not believe you will be successful finding bits of relevant code without that general feel.
Do not try to describe your entire problem because I doubt anyone will respond. Instead try to break your problem down into little steps and seek help with those steps.
For example, you will need to determine the number of rows in the post-meeting report so you can access that data. You then want to add that data to the bottom of the previous list. In both cases you need to determine the last used row in a worksheet. "Excel VBA: How to find last row of worksheet?" is a simple question and you will be able to find multiple answers. I give my response to that question below.
I assume the post-meeting report and the list you are creating are in different workbooks. Your macro could be in the same workbook as the list or it could be in a different workbook. Macros can access their own workbooks, any other workbook that happens to be open or they can open as many other workbooks as required. Again "Excel VBA: How do I work with several workbooks?" should result in plenty of hits.
I have not tried either of my questions. I find "Excel VBA:" helps but you may require several attempts before you find the just the right question to get the answer you seek. But if your question is small and precise you should always be able to find an answer.
Let's return to the first question. An irritating feature of Excel VBA is that they are almost always several ways of achieving a similar effect. Create a new workbook, create a module and copy the code below to it. Run the macro FindFinal().
This macro demonstrates several methods of finding the last row and column. Every method has its problems and I have tried to show how how each method can fail. There is a lot of worksheet access within this macro which I believe will repay study. It should help you decide which method is appropriate for each of your requirements.
Option Explicit
Sub FindFinal()
Dim Col As Long
Dim Rng As Range
Dim Row As Long
' Try the various techniques on an empty worksheet
Debug.Print "***** Empty worksheet"
Debug.Print ""
With Worksheets("Sheet1")
.Cells.EntireRow.Delete
Set Rng = .UsedRange
If Rng Is Nothing Then
Debug.Print "Used range is Nothing"
Else
Debug.Print "Top row of used range is: " & Rng.Row
Debug.Print "Left column row of used range is: " & Rng.Column
Debug.Print "Number of rows in used range is: " & Rng.Rows.Count
Debug.Print "Number of columns in used range is: " & Rng.Columns.Count
Debug.Print "!!! Notice that the worksheet is empty but the user range is not."
End If
Debug.Print ""
Set Rng = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByRows, xlPrevious)
If Rng Is Nothing Then
Debug.Print "According to Find the worksheet is empty"
Else
Debug.Print "According to Find the last row containing a value is: " & Rng.Row
End If
Debug.Print ""
Set Rng = .Cells.SpecialCells(xlCellTypeLastCell)
If Rng Is Nothing Then
Debug.Print "According to SpecialCells the worksheet is empty"
Else
Debug.Print "According to SpecialCells the last row is: " & Rng.Row
Debug.Print "According to SpecialCells the last column is: " & Rng.Column
End If
Debug.Print ""
Row = .Cells(1, 1).End(xlDown).Row
Debug.Print "Down from A1 goes to: A" & Row
Row = .Cells(Rows.Count, 1).End(xlUp).Row
Debug.Print "up from A" & Rows.Count & " goes to: A" & Row
Col = .Cells(1, 1).End(xlToRight).Column
Debug.Print "Right from A1 goes to: " & ColNumToCode(Col) & "1"
Col = .Cells(1, Columns.Count).End(xlToLeft).Column
Debug.Print "Left from " & Columns.Count & _
"1 goes to: " & ColNumToCode(Col) & "1"
' Add some values and formatting to worksheet
.Range("A1").Value = "A1"
.Range("A2").Value = "A2"
For Row = 5 To 7
.Cells(Row, "A").Value = "A" & Row
Next
For Row = 12 To 15
.Cells(Row, 1).Value = "A" & Row
Next
.Range("B1").Value = "B1"
.Range("C2").Value = "C2"
.Range("B16").Value = "B6"
.Range("C17").Value = "C17"
.Columns("F").ColumnWidth = 5
.Cells(18, 4).Interior.Color = RGB(128, 128, 255)
.Rows(19).RowHeight = 5
Debug.Print ""
Debug.Print "***** Non-empty worksheet"
Debug.Print ""
Set Rng = .UsedRange
If Rng Is Nothing Then
Debug.Print "Used range is Nothing"
Else
Debug.Print "Top row of used range is: " & Rng.Row
Debug.Print "Left column row of used range is: " & Rng.Column
Debug.Print "Number of rows in used range is: " & Rng.Rows.Count
Debug.Print "Number of columns in used range is: " & Rng.Columns.Count
Debug.Print "!!! Notice that row 19 which is empty but has had its height changed is ""used""."
Debug.Print "!!! Notice that column 5 which is empty but has had its width changed is not ""used""."
Debug.Print "!!! Notice that column 4 which is empty but contains a coloured cell is ""used""."
End If
Debug.Print ""
Set Rng = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByRows, xlPrevious)
If Rng Is Nothing Then
Debug.Print "According to Find the worksheet is empty"
Else
Debug.Print "According to Find the last row containing a formula is: " & Rng.Row
End If
' *** Note: search by columns not search by rows ***
Set Rng = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByColumns, xlPrevious)
If Rng Is Nothing Then
Debug.Print "According to Find the worksheet is empty"
Else
Debug.Print "According to Find the last column containing a formula is: " & Rng.Column
End If
' *** Note: Find returns a single cell and the nature of the search
' affects what it find. Compare SpecialCells below.
Debug.Print ""
Set Rng = .Cells.SpecialCells(xlCellTypeLastCell)
If Rng Is Nothing Then
Debug.Print "According to SpecialCells the worksheet is empty"
Else
Debug.Print "According to SpecialCells the last row is: " & Rng.Row
Debug.Print "According to SpecialCells the last column is: " & Rng.Column
End If
Debug.Print ""
Row = 1
Do While True
Debug.Print "Down from A" & Row & " goes to: ";
Row = .Cells(Row, 1).End(xlDown).Row
Debug.Print "A" & Row
If Row = Rows.Count Then Exit Do
Loop
End With
With Worksheets("Sheet2")
.Cells.EntireRow.Delete
.Range("B2").Value = "B2"
.Range("C3").Value = "C3"
.Range("B7").Value = "B7"
.Range("B7:B8").Merge
.Range("F3").Value = "F3"
.Range("F3:G3").Merge
Debug.Print ""
Debug.Print "***** Try with merged cells"
Set Rng = .UsedRange
If Rng Is Nothing Then
Debug.Print "Used range is Nothing"
Else
Debug.Print "Used range is: " & Replace(Rng.Address, "$", "")
End If
Debug.Print ""
Set Rng = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByRows, xlPrevious)
If Rng Is Nothing Then
Debug.Print "According to Find the worksheet is empty"
Else
Debug.Print "According to Find the last cell by row is: " & Replace(Rng.Address, "$", "")
End If
Set Rng = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByColumns, xlPrevious)
If Rng Is Nothing Then
Debug.Print "According to Find the worksheet is empty"
Else
Debug.Print "According to Find the last cell by column is: " & Replace(Rng.Address, "$", "")
End If
Debug.Print "!!! Notice that Find can ""see"" B7 but not F3."
Debug.Print ""
Set Rng = .Cells.SpecialCells(xlCellTypeLastCell)
If Rng Is Nothing Then
Debug.Print "According to SpecialCells the worksheet is empty"
Else
Debug.Print "According to SpecialCells the last row is: " & Rng.Row
Debug.Print "According to SpecialCells the last column is: " & Rng.Column
End If
End With
End Sub
Function ColNumToCode(ByVal ColNum As Long) As String
Dim Code As String
Dim PartNum As Long
' Last updated 3 Feb 12. Adapted to handle three character codes.
If ColNum = 0 Then
ColNumToCode = "0"
Else
Code = ""
Do While ColNum > 0
PartNum = (ColNum - 1) Mod 26
Code = Chr(65 + PartNum) & Code
ColNum = (ColNum - PartNum - 1) \ 26
Loop
End If
End Function
In the code above, I access worksheet cells directly with statements such as .Range("B2").Value = "B2". This can be slow particularly when you are moving data from one worksheet to another. An alternative approach is to use arrays.
Dim Rng As Range
Dim ShtValues as Variant
With Worksheets("Xxxx")
Set Rng = .Range(.Cells(Row1, Col1), .Cells(Row2, Col2))
End With
ShtValues = Rng.Value
A Variant is a variable that can hold anything including an array. ShtValues = Rng.Value converts ShtValues to a two-dimensional array hold all the values within Rng. Processing values within an array is much faster that accessing them in the worksheet.
.Range(.Cells(Row1, Col1), .Cells(Row2, Col2)) is perhaps the easiest way of creating a range specifying the worksheet area with Cells(Row1, Col1) as the top left cell and Cells(Row2, Col2) as the bottom right.
If I understand correctly, you want to move data from the post-meeting report to the list but the sequence of columns in the report and list are not the same. This suggests you need to move the data as columns. Using .Range(.Cells(Row1, Col1), .Cells(Row2, Col2)) and with Col1 = Col2, you can define a range that is a column.
Rng1.Copy Destination := Cell2
The above statement will copy the contents of Rng1 to the range starting at Cell2. A statement like this for each column of data in the report may be the easiest way of copying the data.
I hope the above gives you a start.
