CODEWITHSUNDEEP
vbaexcel
anky
anky

Reputation: 75120

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

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?

Upvotes: 0

Views: 41

Answers (2)

BigBen
BigBen

Reputation: 50143

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.

See Using With Statements for more detail.

Upvotes: 2

Ryan R
Ryan R

Reputation: 31

I think you just need to do add a "." in front of Cells in 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

Upvotes: 1

Related Questions