Object Required at Long Variable (???)

K

KhallP

Board Regular
Joined
Mar 30, 2021
Messages
157
Office Version
  1. 2016
Platform
  1. Windows
I created a code to pass data from one excel file to another (it is a little confusing project because I am in a company), I believe that the code is correct in general but when I run the program it gives an error called "Object Required" in the variable " k ", can someone help me?



Module (Public Variables):

VBA Code: 
Option Explicit

Public myRecentFile As String
Public counter, counter2 As Integer, res_1, res_2
Public sh As Worksheet, ch As Worksheet
Public k As Long



WorkBook Open:

Code: 
Private Sub Workbook_Open()

Call recentFilesSpecificFolder

End Sub



Principal Code:

Code: 
Public Sub WriteCells()
  
    res_1 = 0
    res_2 = 0
  
    ws = Workbooks(myRecentFile).Sheets.Count
    ws2 = Workbooks("EEC QEC.xlsm").Sheets.Count
    k = ws2.Cells(Rows.Count, "H").End(xlUp).Offset(1).Row  <--------------------- Error is there!!!!!
  
    For counter = 1 To ws
        For counter2 = 1 To ws2
            Set ch = Workbooks(myRecentFile).Worksheets(counter)
            Set sh = Workbooks("EEC QEC.xlsm").Worksheets(counter2)
            If ch.Name = "QEC 12 IF" Or ch.Name = "QEC 22 IF" Or ch.Name = "QEC 24 IF" Or ch.Name = "QEC 41 IF" Or ch.Name = "QEC 42 IF" Or ch.Name = "QEC 43 IF" Or ch.Name = "QEC 44 IF" Then
                If sh.Name = "QEC 1.2 - montagem" Or sh.Name = "QEC 2.2 -SALA LIMPA" Or sh.Name = "QEC 2.4 Logística" Or sh.Name = "QEC 4.1 - MONTAGEM MANUAL(past)" Or sh.Name = "QEC 4.2 - Desmoldagem" Or sh.Name = "QEC 4,3 - RTM" Or sh.Name = "QEC 4,4 - HOT DRAPE" Then
                    If Range("H" & k).Value = "" Then
                        ch.Range("W110").Copy _
                        sh.Range("H" & k)
                        ch.Range("AI110").Copy _
                        sh.Range("I" & k).Offset(0, 1)
                  
                    Else
                    Do Until ActiveCell.Value = ""
                        ActiveCell.Offset(1).Select
                        res_1 = res_1 + 1
                    Loop
              
                        ch.Range("W110").Copy _
                        sh.Range("H" & (k + res_1))
                        ch.Range("AI110").Copy _
                        sh.Range("I" & (k + res_1)).Offset(0, 1)
                        res_1 = Empty
              
                    End If
                End If
              
              
            ElseIf ch.Name = "QEC 11 IF" Or ch.Name = "QEC 21 IF" Or ch.Name = "QEC 23 IF" Or ch.Name = "QEC 25 IF" Or ch.Name = "QEC 26 IF" Or ch.Name = "QEC 27 IF" Or ch.Name = "QEC 28 IA" Or ch.Name = "QEC 31 IF" Then
                If sh.Name = "QEC 11 IF- Pintura" Or sh.Name = "QEC 2,1 Corredor" Or sh.Name = "QEC 23 IF- SALA DE CORTE" Or sh.Name = "QEC 2.5 Acabamento" Or sh.Name = "QEC 2,6 - Ultra sons" Or sh.Name = "QEC 2,7 - flow" Or sh.Name = "QEC 2.8 - Gabinetes" Or sh.Name = "QEC 3,1- Autoclave" Then
                    If Range("D" & k).Value = "" Then
                        Workbooks("EEC QEC.xlsm").Range("D" & k).Value = ch.Range("W110").Value + ch.Range("AI110").Value
                  
                    Else
                    Do Until ActiveCell.Value = ""
                        ActiveCell.Offset(1).Select
                        res_2 = res_2 + 1
                    Loop
              
                        Workbooks("EEC QEC.xlsm").Range("D" & (k + res_2)).Value = ch.Range("W110").Value + ch.Range("AI110").Value
                        res_2 = Empty
                      
                    End If
                End If
            End If
          
            res_1 = Empty
            res_2 = Empty

        Next counter2
    Next counter
  
counter = Empty
Call Fill_2021

End Sub



Private Sub Fill_2021()
  
    Workbooks("EEC QEC.xlsm").Worksheets("2021").Select
    For counter = 1 To ws
        Set sh = Workbooks("EEC QEC.xlsm").Worksheets(counter)
        If Range("B" & k).Value = "" Then
                If sh.Name = "QEC 1.2 - montagem" Or sh.Name = "QEC 2.2 -SALA LIMPA" Or sh.Name = "QEC 2.4 Logística" Or sh.Name = "QEC 4.1 - MONTAGEM MANUAL(past)" Or sh.Name = "QEC 4.2 - Desmoldagem" Or sh.Name = "QEC 4,3 - RTM" Or sh.Name = "QEC 4,4 - HOT DRAPE" Then
                    Workbooks("EEC QEC.xlsm").ActiveCell.Value = sh.Range("H" & k).Value
                    ActiveCell.Offset(0, 1).Select
                              
                              
                Else
                    Workbooks("EEC QEC.xlsm").ActiveCell.Value = sh.Range("D" & k).Value
                    ActiveCell.Offset(0, 1).Select
              
                End If
              
                      
        Else
                Do Until ActiveCell.Value = ""
                    ActiveCell.Offset(1).Select
                Loop
                  
  
                If sh.Name = "QEC 1.2 - montagem" Or sh.Name = "QEC 2.2 -SALA LIMPA" Or sh.Name = "QEC 2.4 Logística" Or sh.Name = "QEC 4.1 - MONTAGEM MANUAL(past)" Or sh.Name = "QEC 4.2 - Desmoldagem" Or sh.Name = "QEC 4,3 - RTM" Or sh.Name = "QEC 4,4 - HOT DRAPE" Then
                    Workbooks("EEC QEC.xlsm").ActiveCell.Value = sh.Range("H" & k).Value
                    ActiveCell.Offset(0, 1).Select
                              
                              
                Else
                    Workbooks("EEC QEC.xlsm").ActiveCell.Value = sh.Range("D" & k).Value
                    ActiveCell.Offset(0, 1).Select
                              
                End If
          
        End If
        Exit For
                  
    Next counter
                      
counter = Empty
Call Fill_Ilumination
                  
End Sub



Private Sub Fill_Ilumination()

    Workbooks("EEC QEC.xlsm").Worksheets("Iluminação Exterior").Select
    For counter = 1 To ws
            Set sh = Workbooks("EEC QEC.xlsm").Worksheets(counter)
            If Range("N" & k).Value = "" Then
            
                    If sh.Name = "QEC 1.2 - montagem" Or sh.Name = "QEC 2.2 -SALA LIMPA" Or sh.Name = "QEC 2.4 Logística" Or sh.Name = "QEC 4.1 - MONTAGEM MANUAL(past)" Or sh.Name = "QEC 4.2 - Desmoldagem" Or sh.Name = "QEC 4,3 - RTM" Or sh.Name = "QEC 4,4 - HOT DRAPE" Then
                        Workbooks("EEC QEC.xlsm").ActiveCell.Value = sh.Range("H" & k).Value
                        ActiveCell.Offset(0, 1).Select
                      
                    Else
                        Workbooks("EEC QEC.xlsm").ActiveCell.Value = sh.Range("D" & k).Value
                        ActiveCell.Offset(0, 1).Select
                      
                    End If
                  
            Else
          
                Do Until ActiveCell.Value = ""
                    ActiveCell.Offset(1).Select
                Loop
              
              
                If sh.Name = "QEC 1.2 - montagem" Or sh.Name = "QEC 2.2 -SALA LIMPA" Or sh.Name = "QEC 2.4 Logística" Or sh.Name = "QEC 4.1 - MONTAGEM MANUAL(past)" Or sh.Name = "QEC 4.2 - Desmoldagem" Or sh.Name = "QEC 4,3 - RTM" Or sh.Name = "QEC 4,4 - HOT DRAPE" Then
                    Workbooks("EEC QEC.xlsm").ActiveCell.Value = sh.Range("H" & k).Value
                    ActiveCell.Offset(0, 1).Select
                              
                              
                Else
                    Workbooks("EEC QEC.xlsm").ActiveCell.Value = sh.Range("D" & k).Value
                    ActiveCell.Offset(0, 1).Select
                              
                End If
              
            End If
                  
    Next counter
                
counter = Empty
          
End Sub


Public Sub recentFilesSpecificFolder()

    Dim myFile As String, fileExtension As String, recentDate As Date

    myDirectory = Environ("userprofile") & "\Documents\Projeto_Luis\Andre\EEC\QE"
    fileExtension = "*.xls"

    If Right(myDirectory, 1) <> "\" Then myDirectory = myDirectory & "\"

    myFile = Dir(myDirectory & fileExtension)
    If myFile <> "" Then
        myRecentFile = myFile
        recentDate = FileDateTime(myDirectory & myFile)
        Do While myFile <> ""
            If FileDateTime(myDirectory & myFile) > recentDate Then
                myRecentFile = myFile
                recentDate = FileDateTime(myDirectory & myFile)
            End If
        myFile = Dir
        Loop
    End If
    MsgBox "Path: " & myDirectory & vbCrLf & "File: " & myMostRecentFile
  
    Call WriteCells

End Sub
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
ws2 is a variant containing a number as set in the line above, while your code implies that ws2 should be a worksheet
 
Upvote 0
Solution
offthelip said:
ws2 is a variant containing a number as set in the line above, while your code implies that ws2 should be a worksheet
Click to expand...
a new error appeared right here, could you help me?

Error: "Object doesn´t support this property



VBA Code: 
 ElseIf ch.Name = "QEC 11 IF" Or ch.Name = "QEC 21 IF" Or ch.Name = "QEC 23 IF" Or ch.Name = "QEC 25 IF" Or ch.Name = "QEC 26 IF" Or ch.Name = "QEC 27 IF" Or ch.Name = "QEC 28 IA" Or ch.Name = "QEC 31 IF" Then
                If sh.Name = "QEC 11 IF- Pintura" Or sh.Name = "QEC 2,1 Corredor" Or sh.Name = "QEC 23 IF- SALA DE CORTE" Or sh.Name = "QEC 2.5 Acabamento" Or sh.Name = "QEC 2,6 - Ultra sons" Or sh.Name = "QEC 2,7 - flow" Or sh.Name = "QEC 2.8 - Gabinetes" Or sh.Name = "QEC 3,1- Autoclave" Then
                    If Range("D" & k).Value = "" Then
                        Workbooks("EEC QEC.xlsm").Range("D" & k).Value = ch.Range("W110").Value + ch.Range("AI110").Value <---------------- Error There!!
                   
                    Else
                    Do Until ActiveCell.Value = ""
                        ActiveCell.Offset(1).Select
                        res_2 = res_2 + 1
                    Loop
               
                        Workbooks("EEC QEC.xlsm").Range("D" & (k + res_2)).Value = ch.Range("W110").Value + ch.Range("AI110").Value
                        res_2 = Empty
                       
                    End If
                End If
            End If
           
            res_1 = Empty
            res_2 = Empty
           
        Next counter2
    Next counter
 
Upvote 0
I think the error is because you need to choose which worksheet in the workbook EEC QEC you are trying to set
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
"The specified dimension is not valid for the current chart type" error
Replies
0
Views
1K
KhallP
K
K
  • Question
"object doesn't support this property or method"
Replies
5
Views
568
HaHoBe
HaHoBe
K
  • Question
I can't copy the files to another Excel spreadsheet
Replies
0
Views
387
KhallP
K
K
  • Question
How can I do to assign the value to my cell according to the "SUM" function (present in another Workbook)
Replies
1
Views
370
Fluff
Fluff
K
  • Question
The program does not write the value on the sheet
Replies
3
Views
521
HaHoBe
HaHoBe

Forum statistics

Threads
1,214,870
Messages
6,122,021
Members
449,060
Latest member
LinusJE

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top