• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copying Formulas from another Sheet

Hi,

I keep getting an error on the bold, red part of the code below. I have a button on an active sheet (Data Load) that I need to go to another sheet (Controls) and paste formulas. Controls (M2:AD2) as a pasted down formula to Data Load (A2:A995). The reason I have to do it like this is because my org likes to save the tab as an Excel file and TXT file. So at the end, I need to paste Data Load A2:R995 as values. To avoid reference errors.

The error I get is: "run time error 1004 select method of range class failed"

VBA:
>>> use code - tags <<<

cpadilla0024 Mark with comments Your challenge lines to Your code.
Code:
Private Sub DataPrep_Click()


    Range("A2:R995").Select
    Selection.ClearContents
    Range("A2").Select
 
 
    Sheets("Controls").Select
    Range("M2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Data Load").Select
    Range("A2:A995").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A2").Select

    Dim i As Long, lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = lr To 2 Step -1
        If WorksheetFunction.Sum(Range("G" & i & ":R" & i)) = 0 Then
            Range("G" & i).EntireRow.Delete
        End If
    Next i
    Application.ScreenUpdating = True
    Range("A2").Select
 
 
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
 
    MsgBox "TXT FILE SAVED"

 
 

End Sub
 
Last edited by a moderator:
Back
Top