I am trying to call the below code by using a button from sheet
Homepage and expecting it to write data in sheet
Logs . However when I do that, the data is getting written in the same sheet where the macro is called from.
Below is my code:
Sub MyRenamePDF() Dim MyFolder As String Dim MyFile As String Dim i As Long Dim MyOldFile As String Dim MyNewFile As String Dim dt As String Dim FSO As Object Dim rng As Range Dim input_file As String Dim output_file As String dt = Format(Now(), "YYYY_MM_DD_HH_MM") Set FSO = CreateObject("Scripting.Filesystemobject") MyFolder = "D:\test\" TargetFolder = "D:\output\" MyFile = Dir(MyFolder & "\*.pdf") Do While MyFile <> "" MyOldFile = MyFolder & "\" & MyFile MyNewFile = MyFolder & "\" & "0001" & "_" & dt & "_" & MyFile Name MyOldFile As MyNewFile output_file = FSO.GetFileName(MyNewFile) With ThisWorkbook.Worksheets("Logs") Cells(.Rows.Count, "B").End(xlUp).Offset(1).Value = output_file Cells(.Rows.Count, "A").End(xlUp).Offset(1).Value = MyFile End With FSO.MoveFile MyNewFile, TargetFolder MyFile = Dir Loop End Sub
What is possibly going wrong here?