Problems Working With "With...End WIth"

A

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,564
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Please consider this code...

Rich (BB code): 
Sub laurier()
Dim wshdia As Worksheet
Dim wshfld As Worksheet
Dim wshcrt As Worksheet
Dim wshdist As Worksheet
Dim wsht As Worksheet
Dim lastrow1 As Integer
Dim lastrow2 As Integer
Dim lastrow3 As Integer
Dim wb As Workbook
Dim LR As Integer
Dim LR2 As Integer
Dim LR3 As Integer

Set wsht = Workbooks("Open1.xls").Worksheets("Title")

'Open reference file if not already open

With wsht.Range("D11")
    sfname = Format(.Value, "00000") & "(" & Format(.Value, "dd-mmmm-yy") & ").xls"
End With
On Error Resume Next
Set wb = Workbooks(sfname)
On Error GoTo 0
If wb Is Nothing Then Set wb = Workbooks.Open("E:\SportsOps 2009\Data\" & sfname)

Set wshdia = Workbooks(sfname).Worksheets("Dia_Temp")
Set wshfld = Workbooks(sfname).Worksheets("Flds_Temp")
Set wshcrt = Workbooks(sfname).Worksheets("Crts_Temp")
Set wshdist = Workbooks(sfname).Worksheets("Distribution")

' Delete pre-existing data from destination worksheet if it exists. Do not delete header rows (1:2)
wshdist.Activate
With wshdist
    .Unprotect
    If .FilterMode Then .ShowAllData
    lastrowx = Range("A65536").End(xlUp).Row
    ' MsgBox "Next Open Row : " & lastrow1 + 1
    If lastrowx > 2 Then
        .Rows("3" & ":" & lastrowx).Delete
    End If
    
' Copy cells for reference purposes from SportsOps_Data.xls to destination worksheet
    
    With Workbooks("SportsOps_Data.xls").Worksheets("Distribution")
        .Range("CH3:CN18").Copy Destination:=wshdist.Range("CH3")
    End With
        
End With
With wshdia
    With wshdist
        lastrow1 = Range("A65536").End(xlUp).Row
    End With
    LR = .Range("E" & Rows.Count).End(xlUp).Row
    If LR = 1 Then
       MsgBox "No DIAMOND rentals."
    Else
        .Range("E2:E" & LR).Copy 'contract #
        wshdist.Range("A" & lastrow1 + 1).PasteSpecial Paste:=xlPasteValues
        .Range("B2:B" & LR).Copy 'complex
        wshdist.Range("E" & lastrow1 + 1).PasteSpecial Paste:=xlPasteValues
        .Range("M2:M" & LR).Copy 'facility B
        wshdist.Range("F" & lastrow1 + 1).PasteSpecial Paste:=xlPasteValues
        .Range("F2:F" & LR).Copy 'program start
        wshdist.Range("I" & lastrow1 + 1).PasteSpecial Paste:=xlPasteValues
        .Range("G2:G" & LR).Copy 'program end
        wshdist.Range("J" & lastrow1 + 1).PasteSpecial Paste:=xlPasteValues
    End If
End With
With wshdist
    If .FilterMode Then .ShowAllData
    lastrow2 = Range("A65536").End(xlUp).Row
    ' MsgBox "Next Open Row : " & lastrow2 + 1
End With
With wshfld
    LR2 = .Range("E" & Rows.Count).End(xlUp).Row
    If LR2 = 1 Then
       MsgBox "No field rentals."
    Else
        .Range("E2:E" & LR2).Copy 'contract #
        wshdist.Range("A" & lastrow2 + 1).PasteSpecial Paste:=xlPasteValues
        .Range("B2:B" & LR2).Copy 'complex
        wshdist.Range("E" & lastrow2 + 1).PasteSpecial Paste:=xlPasteValues
        .Range("M2:M" & LR2).Copy 'facility B
        wshdist.Range("F" & lastrow2 + 1).PasteSpecial Paste:=xlPasteValues
        .Range("F2:F" & LR2).Copy 'program start
        wshdist.Range("I" & lastrow2 + 1).PasteSpecial Paste:=xlPasteValues
        .Range("G2:G" & LR2).Copy 'program end
        wshdist.Range("J" & lastrow2 + 1).PasteSpecial Paste:=xlPasteValues
    End If
End With
With wshdist
    If .FilterMode Then .ShowAllData
    lastrow3 = Range("A65536").End(xlUp).Row
    ' MsgBox "Next Open Row : " & lastrow3 + 1
End With
With wshcrt
    LR3 = .Range("E" & Rows.Count).End(xlUp).Row
    If LR3 = 1 Then
       MsgBox "No court rentals."
    Else
        .Range("E2:E" & LR3).Copy 'contract #
        wshdist.Range("A" & lastrow3 + 1).PasteSpecial Paste:=xlPasteValues
        .Range("B2:B" & LR3).Copy 'complex
        wshdist.Range("E" & lastrow3 + 1).PasteSpecial Paste:=xlPasteValues
        .Range("M2:M" & LR3).Copy 'facility B
        wshdist.Range("F" & lastrow3 + 1).PasteSpecial Paste:=xlPasteValues
        .Range("F2:F" & LR3).Copy 'program start
        wshdist.Range("I" & lastrow3 + 1).PasteSpecial Paste:=xlPasteValues
        .Range("G2:G" & LR3).Copy 'program end
        wshdist.Range("J" & lastrow3 + 1).PasteSpecial Paste:=xlPasteValues
    End If
End With
With wshdist
    LR4 = .Range("A" & Rows.Count).End(xlUp).Row
    If LR4 < 2 Then
        MsgBox "There are no records for this date."
        Exit Sub
    Else
        Dim LR5 As Integer
        LR5 = LR4 - 2
        MsgBox "There are " & LR5 & " records."
    End If
    .Range("B3:B" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=VLOOKUP($A3,'E:\SportsOps 2009\[Groups.xls]Group_Data'!$A:$D,4,FALSE)"
    .Range("C3:C" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=$E$1&TEXT(ROW()-2,""000"")"
    .Range("D3:D" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=VLOOKUP($A3,'E:\SportsOps 2009\[Groups.xls]Group_Data'!$A:$E,5,FALSE)"
    .Range("G3:G" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=VLOOKUP($F3,'E:\SportsOps 2009\[SportsOps_Data.xls]FACILITIES'!$A:$F,4,FALSE)"
    .Range("H3:H" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=VLOOKUP($F3,'E:\SportsOps 2009\[SportsOps_Data.xls]FACILITIES'!$A:$F,5,FALSE)"
    .Range("k3:k" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=VLOOKUP($F3,'E:\SportsOps 2009\[SportsOps_Data.xls]FACILITIES'!$A:$F,3,FALSE)"
    .Range("L3:L" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=IF(LEFT($B3,1)=""D"",$E$1,"""")"
        
    .Range("M3:M" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=IF(LEFT($B3,1)=""D"",""<"","""")"
    .Range("N3:N" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=IF(LEFT($B3,1)=""D"",$I3-TIME(1,30,0),"""")"
    .Range("O3:O" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=CONCATENATE($M3,TEXT($N3,""H:MM AM/PM""))"
    .Range("P3:P" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=IF(LEFT($B3,1)=""F"","""",IF(LEFT($B3,1)=""C"","""",IF($E3=""Hillside Park"",IF(WEEKDAY($E$1)=2,IF($I3>TIME(15,30,0),Staff_Temp!$F$6,Staff_Temp!$F$4),IF(WEEKDAY($E$1)=4,IF($I3>TIME(15,30,0),Staff_Temp!$F$6,Staff_Temp!$F$4),IF($I3>TIME(15,30,0),Staff_Temp!$F$15,Staff_Temp!$F$13))),IF($E3=""RIM Park Outdoor Facilities"",IF(WEEKDAY($E$1)=3,IF($I3>TIME(15,30,0),Staff_Temp!$F$6,Staff_Temp!$F$4),IF(WEEKDAY($E$1)=5,IF($I3>TIME(15,30,0),Staff_Temp!$F$6,Staff_Temp!$F$4),Staff_Temp!$F$16)),Staff_Temp!$F$4))))"
    .Range("Q3:Q" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=IF($P3=""NA"","""",VLOOKUP($P3,Staff_Temp!$B$22:$C$37,2,FALSE))"
    
    .Range("R3:R" & .Range("A" & Rows.Count).End(xlUp).Row).Value = .Range("$E$1")
    .Range("S3:S" & .Range("A" & Rows.Count).End(xlUp).Row).Value = "<"
    .Range("T3:T" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=$I3-TIME(0,30,0)"
    .Range("U3:U" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=CONCATENATE($S3,TEXT($T3,""H:MM AM/PM""))"
    .Range("V3:V" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=IF(LEFT($B3,1)=""F"",""NA"",IF(LEFT($B3,1)=""C"",""NA"",IF($K3=""HP"",IF($I3>TIME(13,30,0),Staff_Temp!$F$15,Staff_Temp!$F$13),IF($K3=""RP"",IF($I3>TIME(13,30,0),Staff_Temp!$F$18,Staff_Temp!$F$16),IF($I3>TIME(13,30,0),Staff_Temp!$F$12,Staff_Temp!$F$10)))))"
    .Range("W3:W" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=IF($V3=""NA"","""",VLOOKUP($V3,Staff_Temp!$B$22:$C$37,2,FALSE))"
    
    .Range("X3:X" & .Range("A" & Rows.Count).End(xlUp).Row).Value = ">"
    .Range("Y3:Y" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=$I3"
    .Range("Z3:Z" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=CONCATENATE($S3,TEXT($Y3,""H:MM AM/PM""))"
    .Range("AA3:AA" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=IF($K3=""HP"",IF($I3>TIME(13,30,0),Staff_Temp!$F$15,Staff_Temp!$F$13),IF($K3=""RP"",IF($I3>TIME(13,30,0),Staff_Temp!$F$18,Staff_Temp!$F$16),IF($I3>TIME(13,30,0),Staff_Temp!$F$12,Staff_Temp!$F$10)))"

    .Range("AB3:AB" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=VLOOKUP($AA3,Staff_Temp!$B$22:$C$22,2,FALSE)"
    .Range("AC3:AC" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=IF(VLOOKUP($F3,[SportsOps_Data.xls]FACILITIES!$A:$F,6,FALSE)=""Y"",IF($J3>TIME(20,30,0),""7:45 PM"",""NR""),""NA"")"
    .Range("AD3:AD" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=IF($AC3=""NR"","""",IF($AC3=""NA"","""",$AA3))"
    .Range("AE3:AE" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=IF($AD3="""","""",VLOOKUP($AD3,Staff_Temp!$B$22:$C$22,2,FALSE))"
    
    .Range("AF3:AF" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=IF($AC3=""NA"",""NA"",IF($AC3=""NR"",""NR"",$J3))"
    .Range("AG3:AG" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=IF($AC3=""NR"","""",IF($AC3=""NA"","""",$AA3))"
    .Range("AH3:AH" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=IF(AG41="""","""",VLOOKUP($AD3,Staff_Temp!$B$22:$C$22,2,FALSE))"

    .Range("AI3:AI" & .Range("A" & Rows.Count).End(xlUp).Row).Value = " >= "
    .Range("AJ3:AJ" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=$J3"
    .Range("AK3:AK" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=CONCATENATE($M3,TEXT($J3,""H:MM AM/PM""))"
    .Range("AL3:AL" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=IF($K3=""HP"",IF($J3<TIME(13,30,0),Staff_Temp!$F$13,Staff_Temp!$F$15),IF($K3=""RP"",IF($J3<TIME(14,0,0),Staff_Temp!F16,Staff_Temp!$F$18),IF($K3=""SP"",IF($J3<TIME(13,30,0),Staff_Temp!F10,Staff_Temp!$F$12),Staff_Temp!F6)))"
    '**** REPAIR FOrmula To SELECT NAME OF LATE WORKING STAFF *
    .Range("AM3:AM" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=VLOOKUP($AA3,Staff_Temp!$B$22:$C$37,2,FALSE)"
    '
    .Range("AN3:AN" & .Range("A" & Rows.Count).End(xlUp).Row).Value = "<"
    .Range("AP3:AP" & .Range("A" & Rows.Count).End(xlUp).Row).Value = ""
    .Range("AQ3:AQ" & .Range("A" & Rows.Count).End(xlUp).Row).Value = ""
    .Range("AR3:AR" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=CONCATENATE($AN3,TEXT($AO3,""h:mm AM/PM""),$AP3,TEXT($AQ3,""h:mm AM/PM""))"
    .Range("AS3:AS" & .Range("A" & Rows.Count).End(xlUp).Row).Value = ""
    .Range("AT3:AT" & .Range("A" & Rows.Count).End(xlUp).Row).Value = ""
    .Range("AU3:AU" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=VLOOKUP($AT3,Staff_Temp!$B$22:$C$37,2,FALSE)"

    .Range("AV3:AV" & .Range("A" & Rows.Count).End(xlUp).Row).Value = "<"
    .Range("AW3:AW" & .Range("A" & Rows.Count).End(xlUp).Row).Value = ""
    .Range("AX3:AX" & .Range("A" & Rows.Count).End(xlUp).Row).Value = ""
    .Range("AY3:AY" & .Range("A" & Rows.Count).End(xlUp).Row).Value = ""
    .Range("AZ3:AZ" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=CONCATENATE($AN3,TEXT($AO3,""h:mm AM/PM""),$AP3,TEXT($AQ3,""h:mm AM/PM""))"
    .Range("BA3:BA" & .Range("A" & Rows.Count).End(xlUp).Row).Value = ""
    .Range("BB3:BB" & .Range("A" & Rows.Count).End(xlUp).Row).Value = ""
    .Range("BB3:BB" & .Range("A" & Rows.Count).End(xlUp).Row).Value = ""
    .Range("BC3:BC" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=VLOOKUP($BB3,Staff_Temp!$B$22:$C$37,2,FALSE)"

    .Range("BD3:BD" & .Range("A" & Rows.Count).End(xlUp).Row).Value = "<"
    .Range("BE3:BE" & .Range("A" & Rows.Count).End(xlUp).Row).Value = ""
    .Range("BF3:BF" & .Range("A" & Rows.Count).End(xlUp).Row).Value = ""
    .Range("BG3:BG" & .Range("A" & Rows.Count).End(xlUp).Row).Value = ""
    .Range("BH3:BH" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=CONCATENATE($AN3,TEXT($AO3,""h:mm AM/PM""),$AP3,TEXT($AQ3,""h:mm AM/PM""))"
    .Range("BI3:BI" & .Range("A" & Rows.Count).End(xlUp).Row).Value = ""
    .Range("BJ3:BJ" & .Range("A" & Rows.Count).End(xlUp).Row).Value = ""
    .Range("BK3:BK" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=VLOOKUP($BJ3,Staff_Temp!$B$22:$C$37,2,FALSE)"
      
    .Range("BL3:BL" & .Range("A" & Rows.Count).End(xlUp).Row).Value = "<"
    .Range("BM3:BM" & .Range("A" & Rows.Count).End(xlUp).Row).Value = ""
    .Range("BN3:BN" & .Range("A" & Rows.Count).End(xlUp).Row).Value = ""
    .Range("BO3:Bo" & .Range("A" & Rows.Count).End(xlUp).Row).Value = ""
    .Range("BP3:BP" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=CONCATENATE($AN3,TEXT($AO3,""h:mm AM/PM""),$AP3,TEXT($AQ3,""h:mm AM/PM""))"
    .Range("BQ3:BR" & .Range("A" & Rows.Count).End(xlUp).Row).Value = ""
    .Range("BR3:BS" & .Range("A" & Rows.Count).End(xlUp).Row).Value = ""
    .Range("BS3:BS" & .Range("A" & Rows.Count).End(xlUp).Row).Formula = "=VLOOKUP($BR3,Staff_Temp!$B$22:$C$37,2,FALSE)"

    .Rows("3" & ":" & LR4).Select
    Selection.Copy
    .Rows("3 ").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    .Range("I3:N" & LR4).Locked = False
    .Range("P3:P" & LR4).Locked = False
    .Range("R3:T" & LR4).Locked = False
    .Range("V3:V" & LR4).Locked = False
    .Range("X3:Y" & LR4).Locked = False
    .Range("AA3:AA" & LR4).Locked = False
    .Range("AC3:AD" & LR4).Locked = False
    .Range("AF3:AG" & LR4).Locked = False
    .Range("AI3:AJ" & LR4).Locked = False
    .Range("AL3:AL" & LR4).Locked = False
    .Range("AN3:AQ" & LR4).Locked = False
    .Range("AS3:AT" & LR4).Locked = False
    .Range("AV3:AY" & LR4).Locked = False
    .Range("BA3:BB" & LR4).Locked = False
    .Range("BD3:BG" & LR4).Locked = False
    .Range("BI3:BJ" & LR4).Locked = False
    .Range("BL3:BO" & LR4).Locked = False
    .Range("BQ3:BR" & LR4).Locked = False
    
     Dim ValidationRange As Range

    Set ValidationRange = .Range("A3", Range("A65536").End(xlUp))

    With Workbooks("SportsOps_Data.xls").Worksheets("Distribution")
        .Rows("3:3").Copy
    End With
        ValidationRange.PasteSpecial Paste:=6, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
     Application.CutCopyMode = False
        
    End With
ActiveSheet.Protect
Application.EnableEvents = True
wb.Save
Workbooks("SportsOps_Data.xls").Save
Workbooks("SportsOps_Data.xls").Close


End Sub

Problem 1:

This code with it's "With" and "End Withs" is causing me grief.
If I execute this code I get all kinds of crazy results.
If I step through this code and monitor the results on the worksheet, everything runs fine.

What I have observed is, if the worksheet is always active, the routine gets applied to some other worksheet. For instance, in my destination workbook (39945(12-May-09).xls for example), worksheet ("Distribution") is the target worksheet for the majority of code. There are formulas that rely on it exclusivly to determine row count etc.

If this worksheet is always in focus, the preferred results get all blurred. If I step through the code but focused on another worksheet (thinking that actions are beinging applied to the "Distribution" worksheet behind my back, I will get an error (see the red highlighted line in the code). If the worksheet is in focus, I no longer get the error.

So, how do I ensure that my actions are being applied to the correct worksheet. Do I alwyas have to be focused on the worksheet for things to work properly. I'm just as confused as this message probably is.

I was under the impression that whatever code fell between the With and End with lines would be executed to that sole worksheet regardless as to where the focus is. I have an activate command at the beginning of my code to make it the focus of attention, yet somewhere along the worksheet loses focus and we run into a "Select method of range class failed" error.

I hope someone is better able to deciphre my situation better than I am able to describe it. This continues to be a major roadblock, so any assistance would be greatly appreciated. I would be happy to provide further explanation if it makes the difference of resolving the issue or not.

Problem 2:
When I try to run this macro with a button push, none of the bulk commands are executed. All that happens is the saving of SportsOps_Data.xls. It does not open the workbook as needed in the beginning, nor does it process any of the source/destination manipulation. But, if I step through the code, everything will be fine as far as execution goes. However, if I'm on another worksheet (Not "distribution"), my counts are all off and data gets overwritten during the transfer process rather than being appended.)

Jenn
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
Check for stuff like this:

lastrowx = Range("A65536").End(xlUp).Row

for instance, range should have a . in front of it, or it'll find the last used row on the active sheet.
 
Upvote 0
Ahhhh ... cool.
I will look into that. I have already found at least one instance of that. I have overcome the problem by putting an activate command for the destination worksheet, but I bet I can eliminate that with your suggestion Weaver.

And as far as problem #2 goes. My bad. I had enableevents disabled.

Jenn
 
Upvote 0
Ark68 said:
Ahhhh ... cool.
I will look into that. I have already found at least one instance of that. I have overcome the problem by putting an activate command for the destination worksheet, but I bet I can eliminate that with your suggestion Weaver.

And as far as problem #2 goes. My bad. I had enableevents disabled.

Jenn
Click to expand...

That's okay, I hadn't read that far!
 
Upvote 0
But ....

AFter having found the numerous bad range statements, I'm running into an error ("Select method of range class failed." with:

Rich (BB code): 
With wshdist
    LR4 = .Range("A" & Rows.Count).End(xlUp).Row
    If LR4 < 2 Then
        MsgBox "There are no records for this date."
        Exit Sub
    Else
        Dim LR5 As Integer
        LR5 = LR4 - 2
        MsgBox "There are " & LR5 & " records."
    End If
  

... BLAH BLAH BLAH .....


    .Rows("3" & ":" & LR4).Select
    Selection.Copy
    .Rows("3").Select
    Selection.PasteSpecial Paste:=xlPasteValues

It works when the "Distribution" worksheet is in focus, but as soon as another sheet is activated, it blasts out an error.


Jenn
 
Upvote 0
Apart from the fact I'd never use .select (see below)

Code: 
.Rows("3" & ":" & LR4).copy
.Rows("3").PasteSpecial Paste:=xlPasteValues

might go better as

Code: 
.Rows("3" & ":" & LR4).value = .Rows("3" & ":" & LR4).value
 
Upvote 0
Don't select ranges to work with them...

Code: 
]with .Rows("3" & ":" & LR4)
    .Formula = .Value
End With

Or

Code: 
]with .Rows("3" & ":" & LR4)
    .Copy
    .PasteSpecial xlValues
End With




Ark68 said:
But ....

AFter having found the numerous bad range statements, I'm running into an error ("Select method of range class failed." with:

Rich (BB code): 
With wshdist
    LR4 = .Range("A" & Rows.Count).End(xlUp).Row
    If LR4 < 2 Then
        MsgBox "There are no records for this date."
        Exit Sub
    Else
        Dim LR5 As Integer
        LR5 = LR4 - 2
        MsgBox "There are " & LR5 & " records."
    End If
  

... BLAH BLAH BLAH .....


    .Rows("3" & ":" & LR4).Select
    Selection.Copy
    .Rows("3").Select
    Selection.PasteSpecial Paste:=xlPasteValues

It works when the "Distribution" worksheet is in focus, but as soon as another sheet is activated, it blasts out an error.


Jenn
Click to expand...
 
Upvote 0
Awesome stuff. Thank you both!
All that verbage I spouted off for something as simple as a .

I ended up using njimack's suggestion as I was getting an "Application-defined or object-defined error." with Weaver's.

And yes, I try to avoid using select in my code, but it was all I could work with a the time. I have learned something new now. Cut/Copy and paste still seems like one of my bigger challenges.

Jenn
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
Add a column w/ formula to copy / paste macro
Replies
5
Views
135
wknight7
W
A
  • Solved
copy paste from one worksheet to other but in different columns
2
Replies
18
Views
856
Joe4
Joe4
O
  • Question
Autofilter no return
Replies
7
Views
202
OX_2005
O
K
  • Question
How to shorten vba code without any problems
Replies
1
Views
75
kamranyd
K
L
  • Solved
VBA Error
2 3
Replies
29
Views
670
Joe4
Joe4

Forum statistics

Threads
1,216,746
Messages
6,132,476
Members
449,729
Latest member
davelevnt

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top