GPSPOW
asked on
Opening second Excel workbook and copying contents of worksheet back to first workbook
I always forget how to do this.
I have a project workbook, "wb1.xlsm" and 3 source workbooks, s1.xls, s2.xls and s3.xls.
In wb1.xlm there are 3 destination worksheets names "d1", "d2", & "d3". In workbooks s1, s2 and s3 there is only one sheet in each.
I want to open each of the source workbooks in order.
Read the contents of cell "G1" and determine based on the value in the cell if the whole sheet needs to be pasted back in "wb1.xlsm" to sheet "d1", "d2" or "d3"
Can someone help me with this?
Thanks
Glen
I have a project workbook, "wb1.xlsm" and 3 source workbooks, s1.xls, s2.xls and s3.xls.
In wb1.xlm there are 3 destination worksheets names "d1", "d2", & "d3". In workbooks s1, s2 and s3 there is only one sheet in each.
I want to open each of the source workbooks in order.
Read the contents of cell "G1" and determine based on the value in the cell if the whole sheet needs to be pasted back in "wb1.xlsm" to sheet "d1", "d2" or "d3"
Can someone help me with this?
Thanks
Glen
ASKER
The path will always be "C:\Path\"
Cell "G1" has the subject of the data. If the value = "67-67104 PMC Nonexempt" then the data is pasted to "sheet1".
If the value ="67-67104 PMC OvtHrs" then the data is pasted to "sheet2".
If the value = '67-67104 PMC ExHrs" then the data is pasted to "sheet3"
How do we tell it paste to the right place?
Thanks
Glen
Cell "G1" has the subject of the data. If the value = "67-67104 PMC Nonexempt" then the data is pasted to "sheet1".
If the value ="67-67104 PMC OvtHrs" then the data is pasted to "sheet2".
If the value = '67-67104 PMC ExHrs" then the data is pasted to "sheet3"
How do we tell it paste to the right place?
Thanks
Glen
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am getting a runtime error 91 : Object Variable or With Block Variable not set for the following:
Sub Workbook_Open()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsDst As Worksheet
Dim arrWBs As Variant
Dim I As Long
arrWBs = Array("Kronos_1.xls", "Kronos_2.xls", "Kronos_3.xls")
For I = LBound(arrWBs) To UBound(arrWBs)
Set wbSrc = Workbooks.Open("C:\Path\" & arrWBs(I))
Select Case wbSrc.Sheets(1).Range("G1" ).Value
Case "67-67104 PMC Nonexempt"
Set wsDst = wbDst.Sheets("NonExHrs")
Case "67-67104 PMC StL PMC"
Set wsDst = wbDst.Sheets("OvtHrs")
Case "67-67104 PMC Exempt"
Set wsDst = wbDst.Sheets("SalHrs")
End Select
If Not wbSrc Is Nothing Then
wbSrc.Sheets(1).UsedRange. Copy wsDst.Range("A1")
End If
wbSrc.Close SaveChanges:=False
Next I
End Sub
The debug highlights :
If Not wbSrc Is Nothing Then
wbSrc.Sheets(1).UsedRange. Copy wsDst.Range("A1")
End If
Any suggestions?
thanks
Glen
Sub Workbook_Open()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsDst As Worksheet
Dim arrWBs As Variant
Dim I As Long
arrWBs = Array("Kronos_1.xls", "Kronos_2.xls", "Kronos_3.xls")
For I = LBound(arrWBs) To UBound(arrWBs)
Set wbSrc = Workbooks.Open("C:\Path\" & arrWBs(I))
Select Case wbSrc.Sheets(1).Range("G1"
Case "67-67104 PMC Nonexempt"
Set wsDst = wbDst.Sheets("NonExHrs")
Case "67-67104 PMC StL PMC"
Set wsDst = wbDst.Sheets("OvtHrs")
Case "67-67104 PMC Exempt"
Set wsDst = wbDst.Sheets("SalHrs")
End Select
If Not wbSrc Is Nothing Then
wbSrc.Sheets(1).UsedRange.
End If
wbSrc.Close SaveChanges:=False
Next I
End Sub
The debug highlights :
If Not wbSrc Is Nothing Then
wbSrc.Sheets(1).UsedRange.
End If
Any suggestions?
thanks
Glen
Oops, checked the wrong thing for 'Nothing'.
It should be this.
The error you were getting was being caused by there not being a match In the Select Case statement.
Are you sure '67-67104 PMC Nonexempt' etc are the exact values to look for in G1?
No leading/trailing spaces/spelling mistakes?
It should be this.
If Not wsDst Is Nothing Then
wbSrc.Sheets(1).UsedRange.Copy wsDst.Range("A1")
End If
Mind you that does bring up another possible problem.The error you were getting was being caused by there not being a match In the Select Case statement.
Are you sure '67-67104 PMC Nonexempt' etc are the exact values to look for in G1?
No leading/trailing spaces/spelling mistakes?
ASKER
Now it is not recognizing the wbDst.sheets reference.
Set wsDst = wbDst.Sheets("OvtHrs")
Where do you set the value for wbDst? This is the workbook that has the VBA code in it.
thanks
Glen
Set wsDst = wbDst.Sheets("OvtHrs")
Where do you set the value for wbDst? This is the workbook that has the VBA code in it.
thanks
Glen
ASKER
I added Set wbDst = ActiveWorkbook.
It works perfectly.
Thank you
Glen
It works perfectly.
Thank you
Glen
Oops, another basic mistake.
I'm posting from my tablet so I'll blame it on that.:)
I'm posting from my tablet so I'll blame it on that.:)
Should be quite straightforward.
One bit missing though, how does the value in G1 determine which sheet the data should goto?
In the meantime here's some code to open the workbooks in question.
Open in new window