cpadilla0024
Member
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.
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: