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

Active Member
Joined
Apr 1, 2012
Messages
349
Office Version
  1. 365
  2. 2016
  3. 2007
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

kshitij_dch

Active Member
Joined
Apr 1, 2012
Messages
349
Office Version
  1. 365
  2. 2016
  3. 2007
Platform
  1. Windows
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,128,099
Messages
5,628,678
Members
416,332
Latest member
blkbeltmjk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top