Insert last day of previous month, copy and paste to next empty row

[maratonomak1]

Hi,

I have a code to copy and paste to next empty row from workbook X to Y column B(+3). I'm trying to insert the last day of previous month format (MMDDYY) but in column A . So if the next empty row pasted in cell B20 to insert the last day of previous month only in A20. My code so far.

Sub Copy()

ThisWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone
Application.CalculateFullRebuild
Application.CalculateUntilAsyncQueriesDone

Dim SourceBk As Workbook, DestBk As Workbook
Dim Sh As Worksheet
Dim pasterow As Long
Dim lo As Excel.ListObject

Set SourceBk = ThisWorkbook 'X"
Set DestBk = Workbooks("Y.xlsm")

Application.ScreenUpdating = False

For Each Sh In ThisWorkbook.Worksheets
pasterow = DestBk.Sheets(Sh.Name).Cells(Rows.Count, "B").End(xlUp).Row + 3
With SourceBk.Sheets(Sh.Name)
If .Range("A2") <> "" Then
Set lo = .ListObjects(1)
With lo
.Range.Copy
DestBk.Sheets(Sh.Name).Range("B" & pasterow).PasteSpecial xlPasteFormats
DestBk.Sheets(Sh.Name).Range("B" & pasterow).PasteSpecial xlPasteValuesAndNumberFormats
End With
Else
DestBk.Sheets(Sh.Name).Range("B" & pasterow).Value = "N/A"
End If
End With
Next Sh

Application.CutCopyMode = False

Set SourceBk = Nothing
Set DestBk = Nothing

Application.ScreenUpdating = True

End Sub

Any help will be greatly appreciated.

Thanks!

 

[maratonomak1]

If all you want to do is add the date then modify your Else statement:

       Else
           DestBk.Sheets(Sh.Name).Range("B" & pasterow).Value = "N/A"
           [COLOR="#0000CD"]DestBk.Sheets(Sh.Name).Range("A" & pasterow) = Format(Date - Day(Date), "mmddyy")  [/COLOR]
       End If

And delete the If...End If Statement for the UsedRange.

That worked perfectly !!!

Thank you so much

 

[JLGWhiz]

That worked perfectly !!!

Thank you so much

You're welcome,
regards, JLG