find value in D8 of each workbook in a folder and replace last used cell in column A of that workbook with value in A8

[kshitij_dch]

Hello All,

I am working on copying last used row from different workbooks and pasting it into master.xlsm however i am stuck how i will differentiate copied data because Last used Row has totals of each column and A column has names (total)

i have Location Name in Each Workbook in a folder In D8 , i was wondering if i could replace Last used Cell of Column A that is always be Total with Value in D8 so that every time i copy the last used row it will give me data location wise

Code i am using to copy last used row from all workbooks in a folder to master.xlsm is

Sub LoopThroughFolder()

Dim MyFile As String, Str As String, MyDir As String, Wb As Workbook
Dim Rws As Long, Rng As Range
Set Wb = ThisWorkbook

MyDir = "C:\Users\jhjhjh\Desktop\New folder\"
MyFile = Dir(MyDir & "*.xls")
ChDir MyDir
Application.ScreenUpdating = 0
Application.DisplayAlerts = 0
On Error Resume Next
Do While MyFile <> ""
Workbooks.Open (MyFile)
With Worksheets("Sheet1")
Rws = .Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range(.Cells(Rws, 1), .Cells(Rws, 9))
Wb.Worksheets("Sheet1").Range("A2").EntireRow.Insert
Rng.Copy Wb.Worksheets("Sheet1").Range("A2").EntireRow
ActiveWorkbook.Close True
End With
Application.DisplayAlerts = 1
MyFile = Dir()
Loop

End Sub

 

[kshitij_dch]

Option Explicit

Sub test()


    Dim MyPath          As String
    Dim MyFile          As String
    Dim Wkb             As Workbook
    Dim Cnt             As Long
    
    Application.ScreenUpdating = False
    
    MyPath = "C:\Users\1256380\Desktop\My folder\" 'change the path accordingly
    
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
    
    MyFile = Dir(MyPath & "*.xls")
    
    Cnt = 0
    Do While Len(MyFile) > 0
        Cnt = Cnt + 1
        Set Wkb = Workbooks.Open(MyPath & MyFile)
        [COLOR=rgb(226, 80, 65)]Range("A1").End(xlDown).Select
        Wkb.Worksheets("Sheet1").Range("B7").Value = "MyNewValue" 'change the new value accordingly[/COLOR]
        Wkb.Close savechanges:=True
        MyFile = Dir
    Loop
    
    If Cnt > 0 Then
        MsgBox "Completed...", vbExclamation
    Else
        MsgBox "No files were found!", vbExclamation
    End If
    
    Application.ScreenUpdating = True
    
End Sub

above code line highlighted in red need to be changed , it replaces the B7 Value of each workbook in a foilder with MyNew Value however , i need macro to forst search last used cell in column A and replace it with Value in A8