Add todays date to a dynamic range of cells using two variables for the range

[Mattyastill]

Hi,

I'm currently trying to write a macro which collates data each day into a masterdata file. I have managed to get the initial copy and paste from one file to the other.

What im currently struggling with is adding todays date in a different column so i know from which date the data was added to the master file.

The StartRange and EndRange essentially give me the start and end point of a specific days data. What im trying to do is select that range and insert todays data in Column AG of that row?

Appretiate any help on this.

Sub Collate_Data()

Dim LastRowBaseFile, LastRowTargetFile, EndRow As Long
Dim BaseFile, TargetFile As Workbook
Dim BaseSheet, TargetSheet As Worksheet
Dim StartRange, EndRange As Range

Set TargetFile = ThisWorkbook
Set BaseFile = Workbooks.Open(Filename:="x")
Set TargetSheet = TargetFile.Sheets("MasterData")
Set BaseSheet = BaseFile.Sheets("Sheet1")


    LastRowBaseFile = BaseSheet.Range("A" & Rows.Count).End(xlUp).Row
    LastRowTargetFile = TargetSheet.Range("A" & Rows.Count).End(xlUp).Row + 1
    
    EndRow = LastRowTargetFile + LastRowBaseFile - 1
    
    
    Set StartRange = BaseSheet.Range("AG" & LastRowTargetFile)
    Set EndRange = BaseSheet.Range("AG" & EndRow)
    
    BaseSheet.Range("A2:AF" & LastRowBaseFile).SpecialCells(xlCellTypeVisible).Copy
    TargetSheet.Range("A" & LastRowTargetFile).PasteSpecial xlPasteAll, xlPasteSpecialOperationNone, False, False  
    
    TargetSheet.Activate
    
    TargetSheet.Range(StartRange, EndRange).Select
    ActiveCell.Value = Date



End Sub

 

[Joe4]

I think it would be very beneficial for us to see an example of your data and your expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.