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

 

[Guest]

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