More of the same types of procedural questions around not using "Select" or "Activate"

iQuikDraw

New Member
Joined
Jan 20, 2021
Messages
12
Office Version
  1. 2010
Platform
  1. Windows
So for these examples below...You can see I was trying to clean these up and get rid of a bunch of "Select" and "Paste" type code. In the 3 cases below, I still have select in them to jump to the sheet, despite that the "With" statements have the sheets by named reference. I am finding that when I'm testing the script, it keeps failing these if I don't "Manually" jump to the sheet in question before I start an instruction, with statement notwithstanding...also, for the one with the "PasteSpecial" line in it, I tried to make that ".Range("Blah:Blah"&Blah).Copy Destination:=PasteSpecial(Blah blah)" but it keeps throwing errors, so I don't know enough to put that one together in less then all the lines I'm using there, not to mention the same issue where if I don't have focus on the sheet I'm working on, all of these instructions fail. As soon as I take the comment mark off of the "..... Select" line, everything works again, except it looks sloppy jumping around worksheets. I lack the knowledge to get around these or write them more efficient, despite trying many ways that all failed so far.


'Copy Annual Chart Data from Weekly tab to the master tables on the SUMMARY DATA tab.

Worksheets("SUMMARY DATA").Select

With wsDst2
wsDst2.Range("F" & RwCnt2 + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With

'Go back to the weekly Tab to delete the Annual columns after the data is moved to the SUMMARY DATA sheet

Worksheets(Worksheets.Count).Select

With wsDst1
.Range("I:X").Delete
End With

'Return to the Imported master data table and clear all contents to prepare for the next update

Worksheets("TG Shipped Lines").Select

With wsCpy
.Range("A:AD").Delete
'.Range("B:B").ClearContents
End With
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,066
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
What worksheet is wsDst1?
What worksheet is wsDst2?
What worksheet is wsCpy?

You are not defining any of the sheets in the code that you have posted. Please post your full code in code tags.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,676
Office Version
  1. 365
Platform
  1. Windows
I tried to make that ".Range("Blah:Blah"&Blah).Copy Destination:=PasteSpecial(Blah blah)" but it keeps throwing errors
You can't do copy and paste special in a single line, only normal paste (although the single line version is more like copy to rather than copy and paste as it bypasses the clipboard).
What worksheet is wsDst1?
What worksheet is wsDst2?
What worksheet is wsCpy?
The way that I read the post, it appears that only one of them is problematic so I would assume that they are worksheet codenames rather than variables.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,066
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
so I would assume that they are worksheet codenames rather than variables.
I am afraid that I can't make the assumption that they are codenames going by the way the rest of the code is written (in fact I will be a bit surprised if they are).
 

iQuikDraw

New Member
Joined
Jan 20, 2021
Messages
12
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

I understand, my bad.

The full sheet names in each of the ".Select" lines refers to the variables I used to name each of the sheets, not code names, for each respective With statement. I had written the With statements with the ".Select" lines commented out. When they kept failing, I took the comment marks off of those lines to manually jump to the sheet in question in each of these cases.

The variable names are really, wsCopy (TG Shipped Lines), wsDst1 (Worksheets(Worksheets.Count) & wsDst2 (Summary Data) just shortened...copy and destinations...I'll be embarrassed placing my full "Beginner" code here maybe...I'll see if I can do a summarized version with the declarations in it.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,066
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
As @jasonb75 has stated, from a With statement point of view only the first one has any issues, what issues are you getting with the other two (obviously I am assuming that RwCnt2 is a row number) ?

VBA Code:
    Dim wsDst2 As Worksheet, wsDst1 As Worksheet, wsCpy As Worksheet, RwCnt2  as Long
   
    Set wsDst2 = Worksheets("SUMMARY DATA")

    With wsDst2
        .Range("F" & RwCnt2 + 1).PasteSpecial Paste:=xlPasteValues
    End With

    'Go back to the weekly Tab to delete the Annual columns after the data is moved to the SUMMARY DATA sheet

    Set wsDst1 = Worksheets(Worksheets.Count)

    With wsDst1
        .Range("I:X").Delete
    End With

    'Return to the Imported master data table and clear all contents to prepare for the next update

    Set wsCpy = Worksheets("TG Shipped Lines")

    With wsCpy
        .Range("A:AD").Delete
        '.Range("B:B").ClearContents
    End With
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,676
Office Version
  1. 365
Platform
  1. Windows
Adding a point to @MARK858's suggestion above, you are not copying, only pasting. If the clipboard is empty then that will cause an error.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,171
Messages
5,640,579
Members
417,151
Latest member
ChickenTenderer

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
Top