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
Pedro Montanari
  • 19
  • 1
  • 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](https://stackoverflow.com/questions/28564505/vba-excel-compile-error-object-required?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa) which shows the same issue you have. – Samuel Everson May 30 '18 at 11:50
  • Possible duplicate of [VBA Excel "Compile error: Object Required"](https://stackoverflow.com/questions/28564505/vba-excel-compile-error-object-required) – Samuel Everson May 30 '18 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 – Pedro Montanari May 31 '18 at 08:39
  • hmmm I did test lastnight and it compiled when `Set` was removed. I can have another look when im home. – Samuel Everson May 31 '18 at 08: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'`? – Samuel Everson May 31 '18 at 11:38
  • On closer inspectionThere are a lot of issues within your code, which is understandable if you're new to VBA. Your code will need to be re-written as each time I've managed to fix one error a new one is presented at the next line (I got as far as the first paste line trying a few different things). I'd recommend to read some VBA documentation (I'll leave some links in another comment) about `Range`, `Copy` and `Paste` (if required). Also it might be useful to read about how to avoid using `Select` and `Copy/Paste` in VBA. – Samuel Everson May 31 '18 at 12:36
  • [Range](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-object-excel), [Copy](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-copy-method-excel), [Paste](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-pastespecial-method-excel), [How to avoid Select in VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Samuel Everson May 31 '18 at 12:41
  • Thanks man, I think I'll read the documentation first and then I'll try to code differently from other approaches – Pedro Montanari Jun 01 '18 at 07:15

0 Answers0