Run-time error 1004

I am getting a run-time error in a script that i have used without incident in another file. This is just the piece of the script that is giving me issues. All it is supposed to do is clear out one tab, go to another tab, copy a variable range length, go back to the first tab and paste it as values.

There are no strings in any of the copy cells that are excessively large.

Nearly all the cells are formulas.

anyone?
Sub TableMaker()
 
' Clean out old data in "TableMaker" tab
    Sheets("TableMaker").Select
    Range("A8:K8").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete
    Range("a8").Select
    Application.CutCopyMode = False
    
'Copy Data to "Expansion Va-Vb"
    Sheets("Expansion Va-Vb").Select
    Range("A21:J21").Select <------------------------------------------This is where the error hits
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("TableMaker").Select
    Range("A8").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
 
End Sub

Open in new window

mhjdowdAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Saurabh Singh TeotiaConnect With a Mentor Commented:
Have you place the code in the sheet module of your workbook, If yes then please change the same to the new module of the workbook..that is go to insert-->insert module and paste the code there and post that you wont face a error on the same.
Saurabh...
0
 
Saurabh Singh TeotiaCommented:
Alternatively you can use this code and it will do what you are looking for.
Saurabh..

Sub TableMaker()
 
' Clean out old data in "TableMaker" tab
Dim ws As Worksheet, ws1 As Worksheet
Set ws = Sheets("TableMaker").Select
Set ws1 = Sheets("Expansion Va-Vb").Select
    ws.Range("A8:K" & Cells(65536, "a").End(xlUp).Row).Delete
    ws.Range("a8").Select
    Application.CutCopyMode = False
    
'Copy Data to "Expansion Va-Vb"
    
    ws1.Range("A21:J" & ws1.Cells(65536, "a").End(xlUp).Row).Copy
    ws.Range("A8").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
 
End Sub

Open in new window

0
 
mhjdowdAuthor Commented:
Congratulations. This now ranks as my fastest time from question to complete solution on EE. You rock the house.
0
 
Saurabh Singh TeotiaCommented:
Lol@ its for sure a good feeling, Thanks for the same and grade.
Saurabh...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.