CODEWITHSUNDEEP
vbaexcel
Sean Kelly
Sean Kelly

Reputation: 183

VBA For Loop is Outputting to Wrong Sheet (Simple)

When executed, this code creates a new workbook then saves it into my directory. Then, I need it to fill column 'A' of the new workbook with the timestamp, however my loop is filling column 'A' of the original workbook. A MsgBox confirms the Activesheet is indeed pointing to the new Workbook. What am I missing?

'CREATING THE SHEET AND SAVING IT

Dim wb As Workbook
Set wb = Workbooks.Add(xlWBATWorksheet)
ActiveSheet.Name = "MySheet"
ActiveWorkbook.SaveAs "MyDirectory\MyWorkbook" & Space(1) & datetime

'FILLING COLUMN "A" WITH THE CURRENT DATE

With Workbooks("MyWorkbook" & Space(1) & datetime).Worksheets("MySheet")
    For i = 2 To Cells(Rows.Count, 2).End(xlUp).Row
        Cells(i, 1).Value = Format(CStr(Now), "mm_dd_yyyy")
    Next i
End With

Upvotes: 0

Views: 78

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

When using With you must indicate the link with . in front of each object that should follow that parent:

Dim wb As Workbook
Set wb = Workbooks.Add(xlWBATWorksheet)
ActiveSheet.Name = "MySheet"
ActiveWorkbook.SaveAs "MyDirectory\MyWorkbook" & Space(1) & datetime

'FILLING COLUMN "A" WITH THE CURRENT DATE

With Workbooks("MyWorkbook" & Space(1) & datetime).Worksheets("MySheet")
    For i = 2 To .Cells(Rows.Count, 2).End(xlUp).Row
        .Cells(i, 1).Value = Format(CStr(Now), "mm_dd_yyyy")
    Next i
End With

Upvotes: 2

Related Questions