• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro to Pull Data From Multiple Files

Jess709

New Member
This is my first time posting on this forum. I'm in need assistance with building a Macro. I only have the very basic knowledge of macros, so the complex macro that I need to formulate has been very challenging to even attempt.

I am attempting to automate a process which is currently manually entered into Excel. We receive daily balance sheets from our 21 stores and I need to have certain figures pulled from the balance sheets into two other Excel files. I've included an attachment of the sample files. Please assume that I will house all the files in the same folder, to make things simpler.

What I need to accomplish is:
Pull the contents of cell C3 from the "Daily Balance" file and enter it into cell B4 in the "sales Sheet" file. I also need the contents of C3 from the "Daily Balance" file entered into B17 in the "Long Form April" file.

This would be an example of 1 day's balance sheet. I would need to duplicate the macro to complete this same task for the other days of the month. This would mean that C3 on the "Daily Balance" spreadsheet would remain constant, but the cells in the "Sales Sheet" and "long form April" would change based on the day of the month in which the daily balance sheet is for.

Please let me know if any additional information is needed to provide assistance.

Thank you in advance!!
 

Attachments

  • Daily Balance 4.15.15.xlsm
    12.9 KB · Views: 0
  • Long Form APRIL.xlsm
    12.9 KB · Views: 0
  • SALES SHEET.xlsm
    64.5 KB · Views: 0
Hi,

Welcome onboard!!

Check this..

Code:
Sub update()
Dim myPath As String, daily_balance As String, strDate As String, mydate As Date
Dim myday As Integer
Dim myrow As Integer, myfind As Range, daily_balance_dir As String
Dim C3 As String

Application.ScreenUpdating = False

myPath = ThisWorkbook.Path & "\"
daily_balance_dir = "Daily Balance*.xlsm" 'Daily Balance 4.15.15.xlsm
daily_balance = Dir(myPath & daily_balance_dir)

strDate = Trim(Mid(daily_balance, InStrRev(daily_balance, " "), InStr(daily_balance, ".xlsm") - InStrRev(daily_balance, " ")))

mydate = DateSerial(Right(strDate, 2), Replace(Left(strDate, 2), ".", ""), Mid(strDate, InStr(strDate, ".") + 1, 2))
myday = Day(mydate)

'get C3 Value............................
Workbooks.Open (myPath & daily_balance)
    C3 = ActiveSheet.[C3]
ActiveSheet.Parent.Close False
'........................................

If myday < 16 Then
    myrow = myday + 2
Else
    myrow = myday + 4
End If

Cells(myrow, 2).Value = C3

Workbooks.Open (myPath & "SALES SHEET.xlsm")

Set myfind = ActiveSheet.Cells.Find(mydate)
myfind.Offset(1).Value = C3
ActiveSheet.Parent.Close True

ThisWorkbook.Save

Application.ScreenUpdating = True
End Sub
 
Back
Top