I have a macro in excel that copies data from 5 tabs into one tab that is the master data file, and then into another where it hides some columns. Each time the macro is being ran, it deletes all the data from the two destination tabs. Everything works fine, but I would like to have an option to manually enter a comment in the destination tab and have it copied to the source tab. Is it even possible without altering the macro dramatically? The columns that are being hidden might be changed in the future and I would like to avoid having to change the macro every time they are.

This is the macro that I currently run. It works fine. All of the tables are in the same format, the data itself starts in the 3rd row and the comments that I would like to have would be in column “AQ”

Sub ConsolidateData()
Dim ws As Worksheet
Dim destSheet As Worksheet
Dim destSheetStakeholders As Worksheet
Dim lastRow As Long
Dim destRow As Long
Dim rng As Range
Dim sourceSheets As Variant
Dim sheetName As Variant
Dim columnsToHide As Variant

' Define source sheets
sourceSheets = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5")

' Define destination sheets
Set destSheet = ThisWorkbook.Sheets("Projects consolidated")
Set destSheetStakeholders = ThisWorkbook.Sheets("Destination tab 2")

' Clear existing data from destination sheets (starting from row 3)
destSheet.Rows("3:" & destSheet.Rows.Count).ClearContents
destSheetStakeholders.Rows("3:" & destSheetStakeholders.Rows.Count).ClearContents

' Initialize destination rows
destRow = 3

' Define columns to hide in Destination tab 2
columnsToHide = Array("O", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP") ' Adjust as needed

For Each sheetName In sourceSheets
    Set ws = ThisWorkbook.Sheets(sheetName)
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    If lastRow > 2 Then
        ' Define the range to copy (all columns, from row 3 to last row)
        Set rng = ws.Range("A3:AP" & lastRow)
        
        ' Copy data to consolidated and stakeholders view
        destSheet.Cells(destRow, 1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
        destSheetStakeholders.Cells(destRow, 1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
        
        ' Hide specified columns in Destination tab 2
        For Each col In columnsToHide
            destSheetStakeholders.Columns(col).Hidden = True
        Next col
        
        ' Update destination rows
        destRow = destRow + rng.Rows.Count
    End If
Next sheetName

End Sub