0

I have a Excel VBA code that looks through a folder and copies data to the current workbook. Where would I place a code line for move to next available blank row when the code is run again?

I assume I would use the following line of code but it doesn't seem to work in the place where I have it located in the "Select Values from Cells" section:

    twb.Worksheets("Log").Range("A65536").End(xlUp).Offset(1, 0).Select

My full code is:

    Sub RenameExcelInDir()

    Dim MyPath As String
    Dim MyFile As String
    Dim MyExt As String
    Dim MyNewName As String
    Dim MyVendor As String
    Dim MyFAC As String
    Dim MyFabric As String
    Dim MyFiberC As String
    Dim MyKnit As String
    Dim MyWoven As String
    Dim MyDesc As String
    Dim wb As Workbook
    Dim twb As Workbook
    Dim wks As Worksheet
    Dim r As Range
    Dim getDate As String

    'Opens File Dialog Window to choose dir to search in

With Application.FileDialog(msoFileDialogFolderPicker)
   .Show
   MyPath = .SelectedItems(1)
End With

getDate = Date

Set twb = ThisWorkbook

Set r = twb.Worksheets("Log").Range("A2")

MyFile = Dir(MyPath & "\*.*")

Do While Len(MyFile) > 0
    MyExt = Split(MyFile, ".")(UBound(Split(MyFile, ".")))

    Set wb = Workbooks.Open(MyPath & "\" & MyFile, UpdateLinks:=0)


    'Loops through the worksheet collection

For Each wks In wb.Worksheets

Select Case wks.Name

    Case "ISU Form"
    MyNewName = ValidFileName(FileName:=wb.Sheets("ISU FORM").Range("C23").Value & "." & MyExt)
    MyVendor = ValidFileName(FileName:=wb.Sheets("ISU FORM").Range("C21").Value)
    MyFAC = ValidFileName(FileName:=wb.Sheets("ISU FORM").Range("C25").Value)
    MyDesc = ValidFileName(FileName:=wb.Sheets("ISU FORM").Range("C14").Value)
    MyFabric = ValidFileName(FileName:=wb.Sheets("ISU FORM").Range("C15").Value)
    MyFiberC = ValidFileName(FileName:=wb.Sheets("ISU FORM").Range("C17").Value)

    Case "GLOBAL ISU"
    MyNewName = ValidFileName(FileName:=wb.Sheets("GLOBAL ISU").Range("M26").Value & "." & MyExt)
    MyVendor = ValidFileName(FileName:=wb.Sheets("GLOBAL ISU").Range("D18").Value)
    MyFAC = ValidFileName(FileName:=wb.Sheets("GLOBAL ISU").Range("D21").Value)
    MyDesc = ValidFileName(FileName:=wb.Sheets("GLOBAL ISU").Range("D26").Value)
    MyFabric = ValidFileName(FileName:=wb.Sheets("GLOBAL ISU").Range("D64").Value)
    MyFiberC = ValidFileName(FileName:=wb.Sheets("GLOBAL ISU").Range("D66").Value)
    MyKnit = ValidFileName(FileName:=wb.Sheets("GLOBAL ISU").Range("D68").Value)
    MyWoven = ValidFileName(FileName:=wb.Sheets("GLOBAL ISU").Range("F68").Value)

End Select

Next wks

wb.Close

      'Select Values from cells
twb.Worksheets("Log").Range("A65536").End(xlUp).Offset(1, 0).Select
r.Value = MyFile
r.Offset(, 1).Value = MyNewName
r.Offset(, 2).Value = getDate
r.Offset(, 3).Value = MyVendor
r.Offset(, 4).Value = MyFAC
r.Offset(, 5).Value = MyDesc
r.Offset(, 6).Value = MyFabric
r.Offset(, 7).Value = MyFiberC
r.Offset(, 8).Value = MyKnit
r.Offset(, 9).Value = MyWoven
r.Offset(, 10).Value = MyPath
Set r = r.Offset(1)
Name MyPath & "\" & MyFile As MyPath & "\" & MyNewName
MyFile = Dir


Loop

End Sub

and the ValidFileNameFunction is:

   Function ValidFileName(ByVal FileName As String) As String
Dim myarray() As Variant
Dim x
'check for illegal characters
myarray = Array("[", "]", "\\", "/", "*", "\", "?", "<>", "<", ">", ":", "|", "&")
For x = LBound(myarray) To UBound(myarray)
    FileName = Replace(FileName, myarray(x), "", 1)
Next x
ValidFileName = FileName
    End Function

I would like it to move to the next line in the worksheet "Log" when the code is ran again. My current testing results is just overwriting the first two lines in the test folder. Any help would be greatly appreciated. Thanks!

William76
  • 35
  • 5
  • Have you done any research? Row loops questions are extremely common here which means the amount of already written solutions is plentiful. You just need a variable row with a loop. `Range("A" & i)` .... `Next i`. Also, you should create a variable for your work sheets. It will go a long way with readability here. No need to keep restating `wb.Sheets("GLOBAL ISU")`. Just turn that into a variable (maybe *ISU*) and then you can just use `ISU.Range("A" & i)` – urdearboy Mar 27 '19 at 13:51
  • urdearboy, Yeah I did as much research on Mr. Excel and Stackoverflow and that is how I came up with the `twb.Worksheets("Log").Range("A65536").End(xlUp).Offset(1, 0).Select` code. So is this is not correct then. I also saw the `Dim i as Integer` method and didn't know if that was applicable. – William76 Mar 27 '19 at 13:57
  • You should declare your worksheets for shorten your code `Dim wsForm As Worksheet` and then `Set wsForm = wb.Sheets("ISU FORM")`. So you can use `wsForm` instead `wb.Sheets("ISU FORM")` – Damian Mar 27 '19 at 14:03
  • 1
    See [THIS](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba/11169920#11169920) on how to find the last row. Also avoid the use of `.Select`. You may want to see [THIS](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Siddharth Rout Mar 27 '19 at 14:03
  • Damian Thanks, I will update the declarations to your suggestions. Siddharth Rout, I will review the link you provided when I have a moment, thank you both for your help. – William76 Mar 27 '19 at 14:23

1 Answers1

0

Thank you all for your help. I was able to successfully able to accomplish this using the following bit of code: 

Dim nextrow As Long

nextrow = Cells(Rows.Count, "A").End(xlUp).row

I had to change a line or two in the existing code to get it to run, but it works! :) Thank you all for your links to articles that explained what I needed. Cheers!

William76
  • 35
  • 5