1

I am trying to write a code where the user clicks the button and he gets data from the current day from one Excel file, then copy and transpose in another file automatically. The debugger says I have an error in the beginning of the code - Compile error: Object required- in the Public Sub CommandButton1_Click() part. I am new to VBA and I don't have a clue on what's happening. I already tried searching for answers about Sub errors but I couldn't find anything similar to my problem.

Thanks in advance! Here is my code for the moment: 

    Public Sub CommandButton1_Click()

    'Variables

    Sheets("Today_Data").Select
    Dim LastRow As Long, nRow As Long, eRow As Long, eRow2 As Long, eRow3 As Long
    Set LastRow = SelectedSheets.Range("A" & Rows.Count).End(xlUp).Row

'''verify where is the last row updated today'

    For nRow = 5 To LastRow Step 1
    If Cells(nRow, 1).Value = Date Then

       '''copy and paste only the cells that have been updated today

        ''Copy and paste columns 1-4 WITHOUT transposing
        SelectedSheets.Range(SelectedSheets.Cells(nRow, 1), SelectedSheets.Cells(nRow, 2), SelectedSheets.Cells(nRow, 3), SelectedSheets.Cells(nRow, 4)).Select
        Selection.Copy

        'verify where is the next empty row on the destiny sheet to paste
        Set eRow = Worksheets("Test").Range("A" & Rows.Count).End(xlUp).Row

        SelectedSheets.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks_:=False, Transpose:=False, Destination:=Worksheets("Test").Range(Cells(eRow, 1), Cells(eRow, 2), Cells(eRow, 3), Cells(eRow, 4))

        ''Copy and paste odd columns 7-19 transposing
        SelectedSheets.Range(ActiveSheet.Cells(nRow, 7), SelectedSheets.Cells(nRow, 9), SelectedSheets.Cells(nRow, 11), SelectedSheets.Cells(nRow, 13), SelectedSheets.Cells(nRow, 15), SelectedSheets.Cells(nRow, 17), SelectedSheets.Cells(nRow, 19)).Select
        Application.CutCopyMode = False
        Selection.Copy

        'verify where is the next empty row on the destiny sheet to paste
        Set eRow2 = Worksheets("Test").Range("A" & Rows.Count).End(xlUp).Row

        SelectedSheets.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks_:=False, Transpose:=True, Destination:=Worksheets("Test").Range(Cells(eRow2, 7), Cells(eRow2, 9), Cells(eRow2, 11), Cells(eRow2, 13), Cells(eRow2, 15), Cells(eRow2, 17), Cells(eRow2, 19))

        ''Copy and paste even columns 6-20 transposing
        SelectedSheets.Range(ActiveSheet.Cells(nRow, 6), SelectedSheets.Cells(nRow, 8), SelectedSheets.Cells(nRow, 10), SelectedSheets.Cells(nRow, 12), SelectedSheets.Cells(nRow, 14), SelectedSheets.Cells(nRow, 16), SelectedSheets.Cells(nRow, 18), SelectedSheets.Cells(nRow, 20)).Select
        Application.CutCopyMode = False
        Selection.Copy

        'verify where is the next empty row on the destiny sheet to paste
        Set eRow3 = Worksheets("Test").Range("A" & Rows.Count).End(xlUp).Row

        SelectedSheets.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks_:=False, Transpose:=True, Destination:=Worksheets("Test").Range(Cells(eRow3, 6), Cells(eRow3, 8), Cells(eRow3, 10), Cells(eRow3, 12), Cells(eRow3, 14), Cells(eRow3, 16), Cells(eRow3, 18), Cells(eRow3, 20))

    End If

    Next

End Sub
Improve this question
8
  • Your variables LastRow, eRow,eRow2 and eRow3 are declared as variable type Long which is an integer type. Set can only be used when assigning a value to an Object. Remove the word Set from your variable assignments and you won't get the compile error any longer. See this answer here which shows the same issue you have. Commented May 30, 2018 at 11:50
  • Possible duplicate of VBA Excel "Compile error: Object Required" Commented May 30, 2018 at 11:57
  • Thanks for the fast answer man but actually when I remove all my Set Excel continues with the Object Required error, and it says the problem is the LastRow = SelectedSheets.Range("A" & Rows.Count).End(xlUp).Row part. I've read the other answer about this error but still I can't understand what's happening Commented May 31, 2018 at 8:39
  • hmmm I did test lastnight and it compiled when Set was removed. I can have another look when im home. Commented May 31, 2018 at 8:44
  • Set is only used with an Object so you shouldn't be using Set for these variables. When you say you're still getting the Object required error after removing Set, is it still a Compile error OR is it now a Runtime Error '424'? Commented May 31, 2018 at 11:38
Related questions
1
Error with creating command button during runtime
0
Vba Button Code error
0
Object variable or with block variable not set trying to run macro from CommandButton
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.