Copy the data of workbook

[Guest]

I record a macro code to copy the data from the workbook to another workbook
( av1.xls, av2.xls .. etc.. to attendance.xls) but the problem is I have to
open first all the excel workbook before i can run the macro... anyone can
make this code simple and can auto open all the excel files and after the
copying complete it will close and just the attendance.xls will remain open.

Thanks a lot...


Sub Copy ()
' Branch1
Windows("av1.xls").Activate
Sheets("Working Time").Select
Range("A2302").Select
Selection.Copy
Windows("attendance.xls").Activate
Range("A2302").Select
ActiveSheet.Paste
'Branch2
Windows("av2.xls").Activate
Sheets("Working Time").Select
Range("A2302").Select
Application.CutCopyMode = False
Selection.Copy
Windows("attendance.xls").Activate
ActiveWindow.SmallScroll Down:=280
Range("A303603").Select
ActiveSheet.Paste
'Branch3
Windows("av3.xls").Activate
Sheets("Working Time").Select
Range("A2302").Select
Application.CutCopyMode = False
Selection.Copy
Windows("attendance.xls").Activate
Range("A604904").Select
ActiveSheet.Paste
'Branch4
Windows("av4.xls").Activate
Sheets("Working Time").Select
Range("A2302").Select
Application.CutCopyMode = False
Selection.Copy
Windows("attendance.xls").Activate
Range("A9051204").Select
ActiveSheet.Paste
'Branch6
Windows("av6a.xls").Activate
Sheets("Working Time").Select
Range("A2302").Select
Application.CutCopyMode = False
Selection.Copy
Windows("attendance.xls").Activate
Range("A12051502").Select
ActiveSheet.Paste
End Sub

 

[Dave Peterson]

Can you pick out a column in the Working Time worksheets that always have data
in them?

I picked column A. Then I can use that column to determine where to paste the
next worksheet's data.

If that's possible:

Option Explicit
Sub Copy()

Dim myFileNames As Variant
Dim iCtr As Long
Dim AttWks As Worksheet
Dim wks As Worksheet
Dim myPath As String
Dim testStr As String
Dim DestCell As Range

myFileNames = Array("av1", "av2", "av3", "av4", "av6a")

myPath = "C:\my documents\excel\test"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

'attendance.xls should be open
'and change the name of the worksheet
Set AttWks = Workbooks("attendance.xls").Worksheets("sheet1")

'empty out existing data???
With AttWks
.Range("a2", .Cells(.Cells.Count)).ClearContents
Set DestCell = .Range("a2")
End With

For iCtr = LBound(myFileNames) To UBound(myFileNames)
testStr = ""
On Error Resume Next
testStr = Dir(myPath & myFileNames(iCtr) & ".xls")
On Error GoTo 0

If testStr = "" Then
MsgBox myFileNames(iCtr) & " wasn't found!"
Exit Sub
End If
Next iCtr

For iCtr = LBound(myFileNames) To UBound(myFileNames)

Set wks = Nothing
On Error Resume Next
Set wks = Workbooks.Open _
(Filename:=myPath & myFileNames(iCtr) & ".xls") _
.Worksheets("working time")
On Error GoTo 0

If wks Is Nothing Then
MsgBox """Working Time"" was not found in: " & myFileNames(iCtr)
Exit Sub
End If

With wks
.Range("a2", .Cells.SpecialCells(xlCellTypeLastCell)).Copy _
Destination:=DestCell
.Parent.Close savechanges:=False
End With

With AttWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

Next iCtr

Application.CutCopyMode = False
End Sub

 

[Guest]

HI Dave,

Thanks for the code.... but what about if i have three worksheets in one
workbook ( ex. workbook av1 then inside have three sheets namely Working
Time, Lucnch Time and Break Time ) how can i transfer that using your
codes....

Thanks once again....

jeff

 

[Dave Peterson]

If the names of the worksheets don't vary between books and they all come back
to the same sheet:

Option Explicit


Sub Copy()

Dim myFileNames As Variant
Dim myWksNames As Variant
Dim wCtr As Long
Dim iCtr As Long
Dim AttWks As Worksheet
Dim wkbk As Workbook
Dim wks As Worksheet
Dim myPath As String
Dim testStr As String
Dim DestCell As Range

myFileNames = Array("av1", "av2", "av3", "av4", "av6a")
myWksNames = Array("working time", "Lunch time", "Break time")

myPath = "C:\my documents\excel\test"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

'attendance.xls should be open
'and change the name of the worksheet
Set AttWks = Workbooks("attendance.xls").Worksheets("sheet1")

'empty out existing data???
With AttWks
.Range("a2", .Cells(.Cells.Count)).ClearContents
Set DestCell = .Range("a2")
End With

For iCtr = LBound(myFileNames) To UBound(myFileNames)
testStr = ""
On Error Resume Next
testStr = Dir(myPath & myFileNames(iCtr) & ".xls")
On Error GoTo 0

If testStr = "" Then
MsgBox myFileNames(iCtr) & " wasn't found!"
Exit Sub
End If
Next iCtr

For iCtr = LBound(myFileNames) To UBound(myFileNames)

Set wkbk = Workbooks.Open _
(Filename:=myPath & myFileNames(iCtr) & ".xls")

For wCtr = LBound(myWksNames) To UBound(myWksNames)
Set wks = Nothing
On Error Resume Next
Set wks = wkbk.Worksheets(myWksNames(wCtr))
On Error GoTo 0

If wks Is Nothing Then
MsgBox myWksNames(wCtr) & " was not found in: " _
& myFileNames(iCtr)
'but keep going
Else
With wks
.Range("a2", _
.Cells.SpecialCells(xlCellTypeLastCell)).Copy _
Destination:=DestCell
End With
With AttWks
Set DestCell _
= .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

End If
Next wCtr

wkbk.Close savechanges:=False
Next iCtr

Application.CutCopyMode = False
End Sub

 

[Guest]

Hi Dave,

I have try ur second code already and all the data of sheets working time,
lunch time and break time are all transfered to sheet1 column A of attendance
i am trying to put the Working time data to Collumn A, Lunch Time to Collumn
H and Break Time to Collumn G ,.. in sheet1 of attendance workbook but i
cannot do I always have the error... so can you help me again how to do
that....

Thanks once again,,,

jeff

 

[Dave Peterson]

Before I try again, how many columns are in the "working time" worksheets?
A:G or just A?

Same with the other two worksheets.

In fact, is this just a copy|paste at the next available cell in columns A, H, G
or do you have to match up by names??

If you have to match up by names, I think I'd take a different approach.

Put all the data in one worksheet, but put the name on each row, put the
category (working/lunch/break) on each row, put the time(s) on each row, and put
the times/dates on each row.

Then use some sort of pivottable to summarize this data.