Run-time error '1004' when copying from wksht A to B and have to move to correct tab on B.

[dmorr]

Great title. Concise but long.

I have a VB script to move data from a particular tab on workbook A to the same named tab on workbook B. The macro button is on A's tab being copied.
The problem is that the default tab of workbook B is not where the pasting is going. The users need the workbooks to always be on another tab when they open it up.
I can make this macro work if I previously save B on the tab I need the data to go. If I save B on the user's default tab and get out and then run the macro in A I get "Run-time error '1004' PasteSpecial method of Range class failed". The macro stops working while on the target tab of Workbook B in cell AD4.

Also, I determine where the paste is going based on a formula in Workbook A's macro button tab. That works because I'm getting to the right Workbook to paste.

What am I missing in my VB so that it won't conk out when it tries to go to the correct tab to paste in Workbook B?

I would appreciate anyone's kind attention to this problem. I am very much a rookie at this but it would make a lot of users happy if this could work.

Best regards,

David


My code is as follows:

Sub Copy_Employees_To_Following_Month()
'
' Button13_Click Macro
' Macro recorded 07/21/2011 by davem
'
Range("AD4:AG172").Select
Selection.Copy
Workbooks.Open Filename:=Cells(25, "J")
Sheets("Transfer").Select
Sheets("Transfer").Unprotect Password:="secret"
Range("AD4").Select
Selection.PasteSpecial
Application.CutCopyMode = False
Sheets("Transfer").Protect Password:="secret"
Sheets("1").Select
ActiveWorkbook.Save
ActiveWindow.Close
ActiveWindow.LargeScroll ToRight:=-2
ActiveWindow.LargeScroll Down:=-6
Range("J14").Select
End Sub

 

[dmorr]

Sorry, meant to say in the title "...from wkbk..." not "...from wksht...".

 

[tweedle]

I think it's essentially getting confused on where to paste, being a workbook external to the macro.

Specifically qualifying every item the action is to be performed upon will help Excel know what to do where.

Here's my version of your code:

Sub Copy_Employees_To_Following_Month()
'
' Button13_Click Macro
' Macro recorded 07/21/2011 by davem
'
    Dim wbTarget, wbSource As Workbook
    Dim wsTarget, wsSource As Worksheet
    Set wbSource = ThisWorkbook
    Set wsSource = wbSource.ActiveSheet
    Set wbTarget = Workbooks.Open(Filename:=Cells(25, "J"))
    Set wsTarget = wbTarget.Sheets("Transfer")
 
 
    wsTarget.Select
    wsTarget.Unprotect Password:="secret"
 
    wsSource.Range("AD4:AG172").Copy
 
    wsTarget.Range("AD4").PasteSpecial Paste:=xlPasteValues
    wsTarget.Protect Password:="secret"
    wbTarget.Sheets(1).Select
    wbTarget.Close True
 
    Application.CutCopyMode = False
 
    ActiveWindow.LargeScroll ToRight:=-2
    ActiveWindow.LargeScroll Down:=-6
    Range("J14").Select
End Sub

 

[dmorr]

Tweedle, that's it! I am so thankful for your quick and accurate response. There are about 40 users who every month for the last seven years have had to retype the employee list from one month's "temporary excel timesheet solution" to the next month. It had risen to the top of my bucket list but VB is not my forte. I'd be buying coffee for you right now if we were on the same side of the continent. Good luck with Hurricane Irene.

Best regards,

David

 

[dmorr]

I should have tested around before getting too excited. I put the corrected code Tweedle kindly provided to a Module. The workbook has one tab for each day in the month (31 in total) and then a Transfer tab to send it to our ERP product.
When I click on one of the date tabs I get "Run-time error '424' Object Required. The debugger points to this User Form's VB below that is colored in green.
The activity in question worked prior to changing to the better script from Tweedle. It's supposed to draw employee names from the 'Transfer' tab into a date tab for use in creating time entry when that date tab is clicked.

I'm lined up to take a course on VB for Excel in November(the soonest). I hope this problem is simple because I don't want to take people's time unnecessarily. I have spent some hours on it but if there is a moment to provide direction I would appreciate it.

Could it be that now with the code that identifies Source and Target workbooks from the VB earlier in the thread that Excel gets lost as to where the 'Transfer' info should go even though I've clicked on the date tab I want?

Best regards to all,

David

Private Sub TbDay_Change()
Dim wSht As Worksheet
Dim shtname As String
Dim found As Boolean
found = False
shtname = TbDay.Value
x = 4
items = TbDescript.ListCount - 1
If items > 0 Then
For I = 0 To items
TbDescript.RemoveItem (0)
Next I
End If
For Each wSht In Worksheets
If wSht.Name = shtname Then
Calendar1.Visible = False
ActiveWorkbook.Sheets(TbDay.Value).Activate
Do
For I = 0 To TbDescript.ListCount - 1
If TbDescript.List(I) = Range(shtname & "!I" & x) Then
found = True
Exit For
End If
Next I
If found = False Then
TbDescript.AddItem (Range(shtname & "!I" & x))
End If
x = x + 1
found = False
Loop While (Range(shtname & "!I" & x)) <> ""
If Range("BB5") = "" Then
I = 5
Do
Range(shtname & "!BB" & I) = Range("transfer!AE" & I - 1)
Range(shtname & "!BA" & I) = Range("transfer!AD" & I - 1)
I = I + 1
Loop While Range("transfer!AE" & I - 1) <> ""
End If
Exit Sub
End If
Next wSht
MsgBox "Sheet does not exist...Add another sheet and try again."
Calendar1.Visible = False
End Sub

 

[tweedle]

Where is Calendar1?

if on a sheet, what happens when we flip these two lines?
ActiveWorkbook.Sheets(TbDay.Value).Activate
Calendar1.Visible = False

 

[dmorr]

Calendar1 is in Forms. I switched the lines around as requested in the form and still had the same error with the yellow highlighting on Calendar1.Visible = False again.

The improved code for copy/pasting is in a module if that makes a difference. I will look at it with fresher eyes in the morning.

 

[tweedle]

FWIW, I don't think the two subs are related.
To proof that, you could back out my code and see if the userform code flies.

If I have this right, you have a userform with a celendar on it.
The user clicks the calendar.
That updates a textbox with the numeric day they selected.
that triggers this TbDay_Change routine which loops through the sheets looking for something to copy.

If that's the case, Calendar1.visible = false can be in the Calendar1_Click

Private Sub Calendar1_Click()
Calendar1.Visible = False
Me.Repaint
TbDay = Calendar1.Day
End Sub

[You seem to be hiding it whether an appropriate sheet is found or not.]

 

[dmorr]

Your code is working now. I went back to a backed up earlier version and added your code to the same module that I had my code in. Made all the difference.
The calendar error was happening without even going into it. The form contained the calendar but the bug was happening even just by going to the tab. Bottom line: I need to take a course.

Thanks for your time and help!

David