Copy to named range

[Aladdin]

Why is this so hard? All I want to do is copy a range to another named range. But excel makes me activate the sheet, then select the range otherwise I get an error.

'Step 1: open text file

'Copy Range
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

'Go back to original worksheet
sOpenedWksht = Worksheets.Parent.Name 'Set Name to opened workbook
Windows(sCurrentWksht).Activate

'Select Range, only this way works.
Sheets("Run58 -1%").Activate
[rng58].Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Why doesn't this work?
[rng58].Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

 

[Aladdin]

Does anyone know the answer?

Excel 2003

 

[Norie]

There are actually a few things that could go wrong in that code.

Where do you get the error?

You haven't posted the code you are using to open the text file but this might work.

Dim rngDst As Range
Dim rngSrc As Range
 
    Set rngSrc = Range("A2").CurrentRegion

    Set rngDst = ThisWorkbook.Sheets("Run58 -1%").Range("rng58")
 
    rngSrc.Copy
 
    rngDst.PasteSpecial xlPasteValues

One reason it might not work is because there's not workbook or worksheet reference for the source range.

To add those we would need to know how and when the text file was opened.
the text file

 

[doofusboy]

Have you tried?

Windows(sCurrentWksht).Range("MG58").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

There shouldn't be any need to activate or select anything to complete the paste operation.

 

[Aladdin]

Have you tried?

Windows(sCurrentWksht).Range("MG58").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

There shouldn't be any need to activate or select anything to complete the paste operation.

How do I copy the range from the opened text file?

Here is the whole code

Sub MainGetNegativeRuns()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'1.) RUN58 -1%
Call GetData("file.txt")
'Select Range
Sheets("Run58 -1%").Activate
[rng58].Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'Open and close workbook
Windows(sOpenedWksht).Activate
ActiveWindow.Close
Windows(sCurrentWksht).Activate

'::LAST STEP
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

MsgBox "DONE RUNNING NEGATIVE RETURNS"
End Sub

Sub GetData(txtName As String)
Dim rName As Range
Dim sLocation As String
Dim sDirectory As String
sCurrentWksht = Worksheets.Parent.Name
sDirectory = "C:\output"

'OPEN FILE
ChDir sDirectory
Workbooks.OpenText Filename:= _
txtName _
, 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), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array( _
28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1), _
Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 1), Array( _
41, 1), Array(42, 1), Array(43, 1), Array(44, 1), Array(45, 1), Array(46, 1), Array(47, 1), _
Array(48, 1), Array(49, 1), Array(50, 1), Array(51, 1), Array(52, 1), Array(53, 1), Array( _
54, 1), Array(55, 1), Array(56, 1), Array(57, 1), Array(58, 1), Array(59, 1), Array(60, 1), _
Array(61, 1), Array(62, 1), Array(63, 1), Array(64, 1), Array(65, 1), Array(66, 1), Array( _
67, 1), Array(68, 1), Array(69, 1), Array(70, 1), Array(71, 1), Array(72, 1), Array(73, 1), _
Array(74, 1), Array(75, 1), Array(76, 1), Array(77, 1), Array(78, 1), Array(79, 1), Array( _
80, 1), Array(81, 1), Array(82, 1), Array(83, 1), Array(84, 1), Array(85, 1), Array(86, 1), _
Array(87, 1), Array(88, 1), Array(89, 1), Array(90, 1), Array(91, 1), Array(92, 1), Array( _
93, 1), Array(94, 1), Array(95, 1), Array(96, 1), Array(97, 1), Array(98, 1), Array(99, 1), _
Array(100, 1), Array(101, 1), Array(102, 1), Array(103, 1), Array(104, 1), Array(105, 1), _
Array(106, 1), Array(107, 1), Array(108, 1), Array(109, 1), Array(110, 1), Array(111, 1), _
Array(112, 1), Array(113, 1), Array(114, 1), Array(115, 1), Array(116, 1), Array(117, 1), _
Array(118, 1), Array(119, 1), Array(120, 1), Array(121, 1), Array(122, 1), Array(123, 1), _
Array(124, 1)), TrailingMinusNumbers:=True
'COPY RANGE
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

sOpenedWksht = Worksheets.Parent.Name 'Set Name to opened workbook
Windows(sCurrentWksht).Activate

End Sub