将数据粘贴到不同的Sheet excel vba

i got problem for paste data into different sheets. The program i execute keep on save the data in the same Sheet although i set it Sheets1 / Sheets2 . Please help,Thanks.

我遇到了将数据粘贴到不同工作表的问题。我执行的程序继续将数据保存在同一个工作表中,尽管我将其设置为Sheets1 / Sheets2。请帮忙,谢谢。

First Button:

NextRow = ThisWorkbook.Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
'Loop through each file in the folder
For Each objFile In objFolder.files
'List the name, size, and date/time of the current file
Cells(NextRow, 1).Value = objFile.Name
Cells(NextRow, 2).Value = objFile.Path
Cells(NextRow, 3).Value = Format(objFile.DateLastModified, "dd/mm/yyyy hh:mm:ss")
'Find the next row
NextRow = NextRow + 1
Next objFile

Second Button:

NextRow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row + 1
'Loop through each file in the folder
For Each objFile In objFolder.files
'List the name, size, and date/time of the current file
Cells(NextRow, 1).Value = objFile.Name
Cells(NextRow, 2).Value = objFile.Path
Cells(NextRow, 3).Value = Format(objFile.DateLastModified, "dd/mm/yyyy hh:mm:ss")
'Find the next row
NextRow = NextRow + 1
Next objFile

Regards, YY

2 个解决方案

#1

You should use fully qualified name for cells (specify sheet which cells belongs to). With statement should do the trick:

您应该为单元格使用完全限定名称(指定单元格所属的表格)。声明应该做的伎俩:

First button

With ThisWorkbook.Sheets("Sheet2")
    NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    'Loop through each file in the folder
    For Each objFile In objFolder.Files
        'List the name, size, and date/time of the current file
        .Cells(NextRow, 1).Value = objFile.Name
        .Cells(NextRow, 2).Value = objFile.Path
        .Cells(NextRow, 3).Value = Format(objFile.DateLastModified, "dd/mm/yyyy hh:mm:ss")
        'Find the next row
        NextRow = NextRow + 1
    Next objFile
End With

Second button

With ThisWorkbook.Sheets("Sheet1")
    NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    'Loop through each file in the folder
    For Each objFile In objFolder.Files
        'List the name, size, and date/time of the current file
        .Cells(NextRow, 1).Value = objFile.Name
        .Cells(NextRow, 2).Value = objFile.Path
        .Cells(NextRow, 3).Value = Format(objFile.DateLastModified, "dd/mm/yyyy hh:mm:ss")
        'Find the next row
        NextRow = NextRow + 1
    Next objFile
End with

BTW, as I see you have a lot of repeated code. I recommend you to use additional sub with sheet name as parametr:

顺便说一下,我看到你有很多重复的代码。我建议您使用带有工作表名称的附加子作为参数:

Sub test(sheetName As String)
    With ThisWorkbook.Sheets(sheetName)
        NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        'Loop through each file in the folder
        For Each objFile In objFolder.Files
            'List the name, size, and date/time of the current file
            .Cells(NextRow, 1).Value = objFile.Name
            .Cells(NextRow, 2).Value = objFile.Path
            .Cells(NextRow, 3).Value = Format(objFile.DateLastModified, "dd/mm/yyyy hh:mm:ss")
            'Find the next row
            NextRow = NextRow + 1
        Next objFile
    End With
End Sub

and call it for first button:

并将其命名为第一个按钮:

Call test("Sheet2")

and for second button:

并为第二个按钮:

Call test("Sheet1")

#2

You need to qualify Cells().........like:

你需要限定Cells().........喜欢:

Sheets("Sheet1").Cells(NextRow, 1).Value = objFile.Name

Sheets("Sheet2").Cells(NextRow, 1).Value = objFile.Name

Same for all such lines of code......................Otherwise you need a With

所有这些代码行都相同......................否则你需要一个With

#1