VBA XlDown - XLRIGHT issue Subscript error

[jamesmev]

Any help on this. I keep getting a subscript error Not sure where the error is triggering. When I test it errors out at selecting the worksheet. I've adjusted multiple times trying to resolve.

Sub SaveSelectionAsTextFile()
Dim myFolder As String
Worksheets("Table5").Select
Range("A2").Activate
Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).Select
Selection.Copy
'ActiveSheet.Activate
'myRange.Copy
'This temporarily adds a sheet named "Test."
Sheets.Add.Name = "Test"
Sheets("Test").Select
ActiveSheet.Paste
'Ask user for folder to save text file to.
myFolder = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")
'Save selected data as text file in users selected folder.
ActiveWorkbook.SaveAs filename:=myFolder, FileFormat:=xlText, CreateBackup:=False
'Remove temporary sheet.
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
'Indicate save action.
'MsgBox "Text File: " & myFolder & "Saved!"
'Go to top of sheet.
Range("A1").Select
End Sub

 

[jamesmev]

Any help on this. I keep getting a subscript error Not sure where the error is triggering. When I test it errors out at selecting the worksheet. I've adjusted multiple times trying to resolve.

Sub SaveSelectionAsTextFile()
Dim myFolder As String
Worksheets("Table5").Select
Range("A2").Activate
Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).Select
Selection.Copy
'ActiveSheet.Activate
'myRange.Copy
'This temporarily adds a sheet named "Test."
Sheets.Add.Name = "Test"
Sheets("Test").Select
ActiveSheet.Paste
'Ask user for folder to save text file to.
myFolder = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")
'Save selected data as text file in users selected folder.
ActiveWorkbook.SaveAs filename:=myFolder, FileFormat:=xlText, CreateBackup:=False
'Remove temporary sheet.
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
'Indicate save action.
'MsgBox "Text File: " & myFolder & "Saved!"
'Go to top of sheet.
Range("A1").Select
End Sub

This code is also saving the workbook as the new name.
I don't want it to save the workbook - just the text file.

 

[Joe4]

I find example and explanations much more helpful that trying to figure out what code (that isn't working right) is supposed to be doing on data that we have no idea what it looks like.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[jamesmev]

I find example and explanations much more helpful that trying to figure out what code (that isn't working right) is supposed to be doing on data that we have no idea what it looks like.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

The end goal - is to copy Table 5 from a2 xldown/xlright create a txt file that the user can save on their desktop

 

[Joe4]

Try replacing this part:

Range("A2").Activate
Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).Select
Selection.Copy

with just this one line:

Range("A2").CurrentRegion.Copy