Call a macro from a button in sheet 1 and write some data in sheet 2

4288 views vba

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
End Sub

What is possibly going wrong here?

answered question

2 Answers


Add a period . in front of Cells(.Rows.Count, "B")... and Cells(.Rows.Count, "A")....

As is, ActiveSheet is implied - you're not actually using the With...End With block.

posted this

I think you just need to do add a .Cells with the "With" statement:

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

posted this

Have an answer?


Please login first before posting an answer.