• 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 import data

EgyBlind

New Member
hello dears

i'm new member here, and this is first question for me, and i trust you could help me.

i want macro code let me copy data from excel sheets to master sheet, the senario as follow

i recieve everyday about 10 sheets each one contains customers' data as follow
a2 date
a3 time
a4 name
a5 category
...
a8:b8 order
a9:b9 follow up
etc ...

i want to save dayly these data to master sheet which contains all customers for all Q.

master sheet heading as follow
b1 date c1 time, d1 category customer name, e1 order, f1 follow up, g1 fileName
then move to another work book and coppy it ..

i tried this code but it not work at all even failed to browse for foldar

---- code

Sub ImportWorksheets()
'Process all Excel files in specified folder
Dim strFolder As String,
Dim sFile As String
Dim wsTarget As Worksheet
Dim wbSource As Workbook
Dim wsSource As Worksheet
Dim rowTarget As Long 'output row
rowTarget = 2
'Check the folder exists
strFolder = GetFolder
If Not FileFolderExists(FOLDER_PATH) Then
MsgBox "Specified folder does not exist, exiting!"
Exit Sub
End If

'Reset application settings in event of error
On Error GoTo errHandler
Application.ScreenUpdating = False

'Set up the target worksheet
Set wsTarget = Sheets("Sheet2")

'Loop through the Excel files in the folder
sFile = Dir(FOLDER_PATH & "*.xls*")
Do Until sFile = ""

'Open the source file and set the source worksheet - ASSUMED WORKSHEET(1)
Set wbSource = Workbooks.Open(FOLDER_PATH & sFile)
Set wsSource = wbSource.Worksheets(1) 'EDIT IF NECESSARY

'Import the data
With wsTarget
.Range("B" & rowTarget).Value = wsSource.Range("B6").Value
.Range("C" & rowTarget).Value = wsSource.Range("b8").Value
.Range("D" & rowTarget).Value = wsSource.Range("b9").Value
.Range("E" & rowTarget).Value = wsSource.Range("b10").Value
.Range("F" & rowTarget).Value = wsSource.Range("b11").Value
.Range("G" & rowTarget).Value = wsSource.Range("E11").Value
.Range("H" & rowTarget).Value = wsSource.Range("b12").Value
.Range("I" & rowTarget).Value = wsSource.Range("b13:c13").Value
.Range("J" & rowTarget).Value = wsSource.Range("b15").Value
.Range("K" & rowTarget).Value = wsSource.Range("b16:c16").Value

'Optional source filename in the last column
.Range("M" & rowTarget).Value = sFile
End With

'close the source workbook, increment the output row and get the next file
wbSource.Close SaveChanges:=False
rowTarget = rowTarget + 1
sFile = Dir()
Loop

errHandler:
On Error Resume Next
Application.ScreenUpdating = True

'Tidy up
Set wsSource = Nothing
Set wbSource = Nothing
Set wsTarget = Nothing
End Sub
Private Function FileFolderExists(strPath As String) As Boolean
If Not Dir(strPath, vbDirectory) = vbNullString Then FileFolderExists = True
End Function

--- code end

please, if possible need this macro work in all office versions from 2003 cause for different reasons sometimes use 2003
thanks very much
 
hello dears

here i attached files for what i want to do. again thanks for your support
 

Attachments

  • form1.xls
    22.5 KB · Views: 5
  • master.xls
    13.5 KB · Views: 3
pls. still waiting your help, actually add-on not work fine with me its not so accessible for me with my screen reader for this need macro code to easy run it, second it create new sheet everytime, while i need to coppy data from a folder contains everyday about 50 form to the same mastersheet, by the end of month this masterSheet contains more than 1500 row each row represent a form workbook coppied.
pls. hope you help me in this macro.
thanks
 
Back
Top