Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Runtime Error ' 9'

Status
Not open for further replies.
jrobin5881

jrobin5881

Technical User
Mar 10, 2004
228
US
I'm getting the above error with the statement that say's subscript out of range. I don't understand why this is occuring due to me recording the macro and copying and pasting into my code. Listed below is the code and the last line is where the code is bombing out and giving me the error message. I know the file name is spelled incorrectly but that is the way the file is named. Any way to fix this?
'
Workbooks.OpenText Filename:="T:\OPS\OPSDEL\DELIVERY\CSDRS\excutive.xls", _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), _
Array(22, 1)), TrailingMinusNumbers:=True
Columns("B:B").Select
ActiveWindow.SmallScroll ToRight:=2
Columns("B:M").Select
Selection.Delete shift:=xlToLeft
Columns("F:F").Select
Selection.Insert shift:=xlToRight
Columns("K:K").Select
Selection.Insert shift:=xlToRight
ActiveWindow.SmallScroll ToRight:=6
Columns("N:N").Select
Selection.Insert shift:=xlToRight
ActiveWindow.SmallScroll ToRight:=3
Columns("Q:Q").Select
Selection.Insert shift:=xlToRight
Columns("R:S").Select
Selection.Delete shift:=xlToLeft
ActiveWindow.SmallScroll ToRight:=-9
Range("F2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F9"), Type:=xlFillDefault
Range("F2:F9").Select
Range("F1").Select
ActiveCell.FormulaR1C1 = "Pref"
Columns("B:E").Select
Selection.EntireColumn.Hidden = True
Range("K1").Select
ActiveCell.FormulaR1C1 = "Std"
Range("K2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
Range("K2").Select
Selection.AutoFill Destination:=Range("K2:K9"), Type:=xlFillDefault
Range("K2:K9").Select
Columns("G:J").Select
Selection.EntireColumn.Hidden = True
Range("N1").Select
ActiveCell.FormulaR1C1 = "Pkg"
Range("N2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
Range("N2").Select
Selection.AutoFill Destination:=Range("N2:N9"), Type:=xlFillDefault
Range("N2:N9").Select
Columns("L:M").Select
Selection.EntireColumn.Hidden = True
Range("Q1").Select
ActiveCell.FormulaR1C1 = "Pri"
Range("Q2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
Range("Q2").Select
Selection.AutoFill Destination:=Range("Q2:Q9"), Type:=xlFillDefault
Range("Q2:Q9").Select
Columns("O:p").Select
Selection.EntireColumn.Hidden = True
Columns("A:A").EntireColumn.AutoFit
'Range("A1").Select
Range("F2:F8").Select
'Range("F2").Activate
Selection.Copy
Windows("template2.xls").Activate
Range("B6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("excutive.xls").Activate
 
Sort by date Sort by votes




Hi,

This usually happen when an object reference does not exist.

Avoid the use of SELECT and ACTIVATE.
Code: 
    Dim wbEX As Workbook
    Set wbEX = Workbooks.OpenText(Filename:="T:\OPS\OPSDEL\DELIVERY\CSDRS\excutive.xls", _
        Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
        Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
        , 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), _
        Array(22, 1)), TrailingMinusNumbers:=True)
    With wbEX.Sheets("WHATEVER_SHEET_NAME")
        .Columns("B:M").Delete shift:=xlToLeft
        .Columns("F:F").Insert shift:=xlToRight
        .Columns("K:K").Insert shift:=xlToRight
        .Columns("N:N").Insert shift:=xlToRight
        .Columns("Q:Q").Insert shift:=xlToRight
        .Columns("R:S").Delete shift:=xlToLeft
        .Range("F2").FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
        .Range("F2").AutoFill Destination:=Range("F2:F9"), Type:=xlFillDefault
        .Range("F1").FormulaR1C1 = "Pref"
        .Columns("B:E").EntireColumn.Hidden = True
        .Range("K1").FormulaR1C1 = "Std"
        .Range("K2").FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
        .Range("K2").AutoFill Destination:=Range("K2:K9"), Type:=xlFillDefault
        .Columns("G:J").EntireColumn.Hidden = True
        .Range("N1").FormulaR1C1 = "Pkg"
        .Range("N2").FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
        .Range("N2").AutoFill Destination:=Range("N2:N9"), Type:=xlFillDefault
        .Columns("L:M").EntireColumn.Hidden = True
        .Range("Q1").FormulaR1C1 = "Pri"
        .Range("Q2").FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
        .Range("Q2").AutoFill Destination:=Range("Q2:Q9"), Type:=xlFillDefault
        .Columns("O:P").EntireColumn.Hidden = True
        .Columns("A:A").EntireColumn.AutoFit
        'Range("A1").Select
        .Range("F2:F8").Copy
        ThisWorkbook.Sheets("WHATEVER_SHEET_NAME").Range("B6").PasteSpecial _
            Paste:=xlPasteValues, _
            Operation:=xlNone, _
            SkipBlanks:=False, _
            Transpose:=False
    End With

Skip,

[glasses] When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![tongue]
 
Upvote 0 Downvote
Skip,

In testing the code I'm getting a new error message when I step through it using F8 I get "Compile Error: Expected Function or Variable" right after the second line of code. .OpenText is the line that is highlighted by VBA
 
Upvote 0 Downvote



You have to be CERTAIN that this OpenText code is all included in ONE STATEMENT...
Code: 
    Set wbEX = Workbooks.OpenText(Filename:="T:\OPS\OPSDEL\DELIVERY\CSDRS\excutive.xls", _
        Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
        Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
        , 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), _
        Array(22, 1)), TrailingMinusNumbers:=True)

lines wrap in this posting window and the UNDERSCORE character is a continuation symbol.

Pay attention to the VB Editor indications.

Skip,

[glasses] When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![tongue]
 
Upvote 0 Downvote
Skip,

It's all together yet I"m still getting the same message. I even took all of the underscore space's and line wrapping out and made it one long line and it still didn't work.
 
Upvote 0 Downvote


Strange. I seem to vaguely remember that the OpenText method cannot be set to a workbook object.

Try this...
Code: 
    Dim wbEX As Workbook
    Workbooks.OpenText _
        Filename:="T:\OPS\OPSDEL\DELIVERY\CSDRS\excutive.xls", _
        Origin:=437, _
        StartRow:=1, _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=True, _
        Semicolon:=False, Comma:=False, _
        Space:=False, _
        Other:=False, _
        FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1)), _
        TrailingMinusNumbers:=True
                
    Set wbEX = ActiveWorkbook

Skip,

[glasses] When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![tongue]
 
Upvote 0 Downvote
Thank goodness for memories! It works fine now! I appreciate it.
 
Upvote 0 Downvote
Skip,

Now we have a new issue: I'm getting subscript out of range again after I copy range F2:F8 and try and flip back to the book to paste it in. I tried using your method of With etc. and could'nt get that to work? Essentially I need to be able to move back and forth between a worksheet and a template and copy and paste data.

Columns("O:p").EntireColumn.Hidden = True
.Columns("A:A").EntireColumn.AutoFit
'Range("A1").Select
.Range("F2:F8").Copy
Windows("template2.xls").Activate
'ThisWorkbook.Sheets("template2").Range("B6").PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Range("B6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
Windows("excutive.xls").Activate
Range("A2:A8,Q2:Q8").Copy
'Range("Q2").Activate
'Application.CutCopyMode = False
'Selection.Copy
Windows("template.xls").Activate
Range("F7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("excutive.xls").Activate






 
Upvote 0 Downvote



Please. You have dot references to a WITH statement and have not included the referenced code.

Please post ALL your code!

Skip,

[glasses] When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![tongue]
 
Upvote 0 Downvote



It may be something like this...
Code: 
    With wbWEX.Sheets("[b]YOUR SHEET NAME HERE[/b]")
        .Range("F2:F8").Copy
        ThisWorkbook.Sheets("template2").Range("B6").PasteSpecial _
            Paste:=xlPasteValues, _
            Operation:=xlNone, _
            SkipBlanks:=False, _
            Transpose:=False
         
        .Range("A2:A8,Q2:Q8").Copy
        ThisWorkbook.Sheets("template2").Range("F7").PasteSpecial _
            Paste:=xlPasteValues, _
            Operation:=xlNone, _
            SkipBlanks:=False, _
            Transpose:=False
    End With

Skip,

[glasses] When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![tongue]
 
Upvote 0 Downvote
jrobin5581, could you also use the code window tags, when posting code? If you do not know how to do this, check out the Process TGML link at the bottom of a response window.

Thanks.

faq219-2884

Gerry
My paintings and sculpture
 
Upvote 0 Downvote
Fumei,
Thanks for the tip.

Skip- I posted all of my code below and now I'm getting 'Run time error 424' at the highlighted line of code when run. I tried flip flopping the line".Range("F2:F8").Copy before the highlighted line but that didn't help either.

Code: 
Sub test()
 
 Dim wbEX As Workbook
    Workbooks.OpenText _
        Filename:="T:\OPS\OPSDEL\DELIVERY\CSDRS\excutive.xls", _
        Origin:=437, _
        StartRow:=1, _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=True, _
        Semicolon:=False, Comma:=False, _
        Space:=False, _
        Other:=False, _
        FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1)), _
        TrailingMinusNumbers:=True
                
    Set wbEX = ActiveWorkbook
    
    With wbEX.Sheets("excutive")
        .Columns("B:M").Delete shift:=xlToLeft
        .Columns("F:F").Insert shift:=xlToRight
        .Columns("K:K").Insert shift:=xlToRight
        .Columns("N:N").Insert shift:=xlToRight
        .Columns("Q:Q").Insert shift:=xlToRight
        .Columns("R:S").Delete shift:=xlToLeft
        .Range("F2").FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
        .Range("F2").AutoFill Destination:=Range("F2:F9"), Type:=xlFillDefault
        .Range("F1").FormulaR1C1 = "Pref"
        .Columns("B:E").EntireColumn.Hidden = True
        .Range("K1").FormulaR1C1 = "Std"
        .Range("K2").FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
        .Range("K2").AutoFill Destination:=Range("K2:K9"), Type:=xlFillDefault
        .Columns("G:J").EntireColumn.Hidden = True
        .Range("N1").FormulaR1C1 = "Pkg"
        .Range("N2").FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
        .Range("N2").AutoFill Destination:=Range("N2:N9"), Type:=xlFillDefault
        .Columns("L:M").EntireColumn.Hidden = True
        .Range("Q1").FormulaR1C1 = "Pri"
        .Range("Q2").FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
        .Range("Q2").AutoFill Destination:=Range("Q2:Q9"), Type:=xlFillDefault
        .Columns("O:P").EntireColumn.Hidden = True
        .Columns("A:A").EntireColumn.AutoFit
        'Range("A1").Select
[COLOR=red yellow]With wbWEX.Sheets("template2")[/color red yellow]

        .Range("F2:F8").Copy
        
        ThisWorkbook.Sheets("template2.xls").Range("B6").PasteSpecial _
            Paste:=xlPasteValues, _
            Operation:=xlNone, _
            SkipBlanks:=False, _
            Transpose:=False
         
        .Range("A2:A8,Q2:Q8").Copy
        ThisWorkbook.Sheets("template2").Range("F7").PasteSpecial _
            Paste:=xlPasteValues, _
            Operation:=xlNone, _
            SkipBlanks:=False, _
            Transpose:=False
    End With
     
    End With
    

End Sub
 
Upvote 0 Downvote



Do you have a sheet named template2 in excutive.xls?

I guess NOT!

Previously you used...
Code: 
With wbEX.Sheets("[b]excutive[/b]")

Skip,

[glasses] When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![tongue]
 
Upvote 0 Downvote
Skip - sorry for all the confusion

I have two workbooks

excutive has a worksheet "excutive" in it
template2 has a worksheet "CSDRS" in it.

The .Range("F2:F8").Copy (which is the line below the highlighted code in my last post)I want to paste into template2 Worksheet "CSDRS" then I want to return back to excutive workbook, "excutive" worksheet and copy some more info to paste back into workbook template2 Worksheet "CSDRS" and so on.
 
Upvote 0 Downvote


And, as I pointed out
Code: 
With wbWEX.Sheets("template2")
does not exist, does it?
Code: 
Sub test()
 
 Dim wbEX As Workbook
    Workbooks.OpenText _
        Filename:="T:\OPS\OPSDEL\DELIVERY\CSDRS\excutive.xls", _
        Origin:=437, _
        StartRow:=1, _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=True, _
        Semicolon:=False, Comma:=False, _
        Space:=False, _
        Other:=False, _
        FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1)), _
        TrailingMinusNumbers:=True
                
    Set wbEX = ActiveWorkbook
    
    With wbEX.Sheets("excutive")
        .Columns("B:M").Delete shift:=xlToLeft
        .Columns("F:F").Insert shift:=xlToRight
        .Columns("K:K").Insert shift:=xlToRight
        .Columns("N:N").Insert shift:=xlToRight
        .Columns("Q:Q").Insert shift:=xlToRight
        .Columns("R:S").Delete shift:=xlToLeft
        .Range("F2").FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
        .Range("F2").AutoFill Destination:=Range("F2:F9"), Type:=xlFillDefault
        .Range("F1").FormulaR1C1 = "Pref"
        .Columns("B:E").EntireColumn.Hidden = True
        .Range("K1").FormulaR1C1 = "Std"
        .Range("K2").FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
        .Range("K2").AutoFill Destination:=Range("K2:K9"), Type:=xlFillDefault
        .Columns("G:J").EntireColumn.Hidden = True
        .Range("N1").FormulaR1C1 = "Pkg"
        .Range("N2").FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
        .Range("N2").AutoFill Destination:=Range("N2:N9"), Type:=xlFillDefault
        .Columns("L:M").EntireColumn.Hidden = True
        .Range("Q1").FormulaR1C1 = "Pri"
        .Range("Q2").FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
        .Range("Q2").AutoFill Destination:=Range("Q2:Q9"), Type:=xlFillDefault
        .Columns("O:P").EntireColumn.Hidden = True
        .Columns("A:A").EntireColumn.AutoFit
        'Range("A1").Select[s]
'With wbWEX.Sheets("template2")[/s]

        .Range("F2:F8").Copy
        
        ThisWorkbook.Sheets("template2.xls").Range("B6").PasteSpecial _
            Paste:=xlPasteValues, _
            Operation:=xlNone, _
            SkipBlanks:=False, _
            Transpose:=False
         
        .Range("A2:A8,Q2:Q8").Copy
        ThisWorkbook.Sheets("template2").Range("F7").PasteSpecial _
            Paste:=xlPasteValues, _
            Operation:=xlNone, _
            SkipBlanks:=False, _
            Transpose:=False
   
     
    End With
    

End Sub


Skip,

[glasses] When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![tongue]
 
Upvote 0 Downvote
Skip - After reading back through and with all your help I finally got it. Thank you for hanging in there with me. I see how it goes now. The This.workbook is the workbook that has the macros and where I'm working from. It all came together. Here's my final code in case it can help someone else someday.

Code: 
Dim wbEX As Workbook
    Workbooks.OpenText _
        Filename:="T:\OPS\OPSDEL\DELIVERY\CSDRS\excutive.xls", _
        Origin:=437, _
        StartRow:=1, _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=True, _
        Semicolon:=False, Comma:=False, _
        Space:=False, _
        Other:=False, _
        FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1)), _
        TrailingMinusNumbers:=True
                
    Set wbEX = ActiveWorkbook
    
    With wbEX.Sheets("excutive")
        .Columns("B:M").Delete shift:=xlToLeft
        .Columns("F:F").Insert shift:=xlToRight
        .Columns("K:K").Insert shift:=xlToRight
        .Columns("N:N").Insert shift:=xlToRight
        .Columns("Q:Q").Insert shift:=xlToRight
        .Columns("R:S").Delete shift:=xlToLeft
        .Range("F2").FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
        .Range("F2").AutoFill Destination:=Range("F2:F9"), Type:=xlFillDefault
        .Range("F1").FormulaR1C1 = "Pref"
        .Columns("B:E").EntireColumn.Hidden = True
        .Range("K1").FormulaR1C1 = "Std"
        .Range("K2").FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
        .Range("K2").AutoFill Destination:=Range("K2:K9"), Type:=xlFillDefault
        .Columns("G:J").EntireColumn.Hidden = True
        .Range("N1").FormulaR1C1 = "Pkg"
        .Range("N2").FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
        .Range("N2").AutoFill Destination:=Range("N2:N9"), Type:=xlFillDefault
        .Columns("L:M").EntireColumn.Hidden = True
        .Range("Q1").FormulaR1C1 = "Pri"
        .Range("Q2").FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
        .Range("Q2").AutoFill Destination:=Range("Q2:Q9"), Type:=xlFillDefault
        .Columns("O:P").EntireColumn.Hidden = True
        .Columns("A:A").EntireColumn.AutoFit
        'Range("A1").Select
        .Range("F2:F8").Copy
          
        ThisWorkbook.Sheets("CSDRS").Range("B6").PasteSpecial _
            Paste:=xlPasteValues, _
            Operation:=xlNone, _
            SkipBlanks:=False, _
            Transpose:=False
         'With wbWEX.Sheets("excutive")
        .Range("Q2:Q8").Copy
        
        ThisWorkbook.Sheets("CSDRS").Range("F7").PasteSpecial _
            Paste:=xlPasteValues, _
            Operation:=xlNone, _
            SkipBlanks:=False, _
            Transpose:=False
            
          
    End With
     
End Sub
 
Upvote 0 Downvote
Bottom line: learn the diff between ThisWorkbook and ActiveWorkbook.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Helpful tip
Microsoft Office 365 Word Macro Causing a Project is unviewable error
Replies
1
Views
1K
dhookom
dhookom
remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
1K
remeng
remeng
morechocolate
  • Locked
  • Question
run-tune error 3704 operation is not allowed when the object is codes: I don't think so.
Replies
7
Views
1K
morechocolate
morechocolate
sxschech
  • Locked
  • Question
For Each Loop works once then gives error 91 2
Replies
19
Views
860
sxschech
sxschech
huiettcm
  • Locked
  • Question
Catching the Mouse Click Error in Modal User Form
Replies
1
Views
421
strongm
strongm

Part and Inventory Search

Sponsor

Back
Top