Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy certain data in a excel to another excel
im making an application automating the reporting process
usually i do it manually by copy-paste the information to one another i have very little experience in vb and none in macro here is what the application should do 1.open a new xls file 2.open new worksheet in the new xls file and rename it 3.open a few data xls files 4.copy certain data in the xls files and arrange in accordingly in the worksheet step 2-4 is looping depending on how much worksheet or data needed in one workshet 5.save the new xls file you guys have any idea? please help atleast give me some links or tips on where to learn macro or vb fast thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy certain data in a excel to another excel
here is some code that may help
Sub TimeCard() Const MasterPath = "c:\temp\time cards" Dim DateColumnArray(16, 2) 'Application.ScreenUpdating = False Set fso = CreateObject _ ("Scripting.FileSystemObject") Set folder = _ fso.GetFolder(MasterPath) SelectEmployeeForm.SelectEmployeeBox.Clear If folder.subfolders.Count 0 Then For Each sf In folder.subfolders EmployeeDir = sf 'strip off path from filename Do While InStr(EmployeeDir, "\") 0 EmployeeDir = Mid(EmployeeDir, InStr(EmployeeDir, "\") + 1) Loop SelectEmployeeForm.SelectEmployeeBox.AddItem EmployeeDir Next sf End If 'Custom userform, not standard VBA SelectEmployeeForm.Show Employee = SelectEmployeeForm.SelectEmployeeBox.Value TimeSheetWB = Employee + " Time Sheet.xls" TimeCardWB = Employee + " Daily Timecard.xls" Workbooks.Open Filename:="c:\temp\time cards" + "\" + Employee + _ "\" + TimeSheetWB 'wait one second for last listbox to clear 'need this so the listbox will be visable Application.Wait (Now + TimeValue("0:00:1")) SelectWorksheetForm.SelectWorksheetBox.Clear For Each ws In Worksheets SelectWorksheetForm.SelectWorksheetBox.AddItem ws.Name Next ws SelectWorksheetForm.Show StringPayPeriod = SelectWorksheetForm.SelectWorksheetBox.Value Sheets(StringPayPeriod).Activate Set DateRange = Range("E7:T7") Count = 1 'wait one second for last listbox to clear 'need this so the listbox will be visable Application.Wait (Now + TimeValue("0:00:1")) SelectDateForm.SelectDateBox.Clear For Each cell In DateRange MyString = cell + " - " + _ CStr(Cells(cell.Row + 2, cell.Column)) SelectDateForm.SelectDateBox.AddItem MyString DateColumnArray(Count, 1) = MyString DateColumnArray(Count, 2) = cell.Column Count = Count + 1 Next cell 'custom userform, not standard excel SelectDateForm.Show SelDate = SelectDateForm.SelectDateBox.Value For Count = 1 To 16 If StrComp(SelDate, DateColumnArray(Count, 1)) = 0 Then Exit For End If Next Count CopyColumnNum = DateColumnArray(Count, 2) Application.ScreenUpdating = False '&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& &&&& 'OPEN DAILY TIMECARD Workbooks.Open Filename:="c:\temp\time cards" + "\" + Employee + _ "\" + TimeCardWB '&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& &&&& Windows(TimeSheetWB).Activate Sheets(StringPayPeriod).Activate Application.WindowState = xlMaximized 'Application.ScreenUpdating = False '&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& &&&& 'LOAD DATA FROM TIMESHEET INTO DAILY TIMECARD Windows(TimeCardWB).Activate Range("G7").Select ActiveCell.FormulaR1C1 = "='[" + TimeSheetWB + "]" + StringPayPeriod + "'!R9C" + CStr(CopyColumnNum) Application.ScreenUpdating = False OffsetCount = 0 "^^" wrote: im making an application automating the reporting process usually i do it manually by copy-paste the information to one another i have very little experience in vb and none in macro here is what the application should do 1.open a new xls file 2.open new worksheet in the new xls file and rename it 3.open a few data xls files 4.copy certain data in the xls files and arrange in accordingly in the worksheet step 2-4 is looping depending on how much worksheet or data needed in one workshet 5.save the new xls file you guys have any idea? please help atleast give me some links or tips on where to learn macro or vb fast thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How copy none excel data & paste in 2007 without overwriting data | Excel Discussion (Misc queries) | |||
Copy excel data (inc. textbox data) into Word | Excel Discussion (Misc queries) | |||
copy data from excel 1 to excel 2 when different dates and columns | Excel Programming | |||
Excel -- Excel data copy truncated at 255 characters | Excel Worksheet Functions | |||
automatically copy selected data from one excel to other excel | Excel Programming |