Results for "worksheet workbook recognition error"

Hi Giaco1234 and Welcome on Excel-Pratique

For your concern, you can try

With ThisWorkbook.Sheets("Test")
  .Range("B1") = .Range("A1")
End With

Thank you BrunoM455 for your prompt feedback.

Hello,

I hope you are well.

Please, I've been having problems for a while with my VBA compiler. It no longer recognizes the Worksheet and workbook objects and returns an Error as you can see in the photo below.

I tried to debug but can't since.

I read a comment about resetting VBA settings in Excel(Rétablir Interface VBA par défaut (excel-pratique.com)) but it seems I didn't do it right, since the problem hasn't been solved.

I need your help please.

I think I get what you're aiming for with the macro. So, when you open the file and select a cell like A7, you want the macro to grab certain cells from that row (like A7, C7, E7, G7) and paste them into a new workbook, right?

Hello and welcome to the forum

Try with the code herein

Sub Macro1()
Dim newwb As Workbook
Dim r As Integer
Dim TS As ListObject

Set TS = ThisWorkbook.ActiveSheet.ListObjects(1)

On Error Resume Next
r = TS.ListRows(ActiveCell).Range.Row - TS.HeaderRowRange.Row
If r = 0 Then MsgBox "No cell selected in table !", vbCritical, "Wrong selection": Exit Sub 'if selected cell in not in table range
On Error GoTo 0

Set newwb = Workbooks.Add
With ActiveWorkbook.ActiveSheet
    .Range("A1") = TS.DataBodyRange(r, 1).Value
    .Range("A2") = TS.DataBodyRange(r, 3).Value
    .Range("A3") = TS.DataBodyRange(r, 5).Value
    .Range("A4") = TS.DataBodyRange(r, 7).Value
End With
End Sub

rem : Please think to update the reference of your excel version in your account as "English" does not help. Use excel 2008, 2016,;... or if you are a MacUser --> MAC2011, MAC20xx

Sincerely

I would like the Macro to do the following:

When I open the file and select, for example, A7

Then I run the macro

In the macro, I have a setting that if I first selected a cell from row 7, then the macro places the 1st, 3rd, 5th and 7th cells from row 7 in a new workbook in the following way

1. in A1

3. in A2

5. in A3

7. in A4

Also, I would like to choose the save location

thanks again

Hello

Avoid using "row" because it is a reserved word by VBA

uses LRow

Sub RENAME_FILES(LRow As Long)

Dim LR As Long, i As Long, Filename As String, OldName As String, NewName As String, DIRECTORY As String, REF As String

  DIRECTORY = "\\192.168.2.6\elenalouroutziati_document\B R E A K D O W N S\BREAKDOWNS 2016\"

  With ThisWorkbook.Sheets("RECAP")

    LR = .Cells.Find("*", searchorder:=xlByLRows, searchdirection:=xlPrevious).LRow

    On Error Resume Next

    REF = .Range("C" & LRow).Value

    OldName = "CALC(P) - " & REF & " - " & .Range("L" & LRow).Value & " - " & .Range("M" & LRow).Value & " + " & "CALC(S) - " & .Range("W" & LRow).Value & " - " & .Range("X" & LRow).Value & " - " & .Range("F" & LRow).Value & ".xlsm"
    Filename = Dir(DIRECTORY & OldName)

    If Filename <> "" Then

      NewName = "CALC(P) - " & REF & " - " & .Range("L" & LRow).Value & " - " & .Range("M" & LRow).Value & " + " & "CALC(S) - " & .Range("W" & LRow).Value & " - " & .Range("X" & LRow).Value & " - " & .Range("F" & LRow).Value & " - " & Format(.Range("AI" & LRow).Value, "dd-mm-yy") & ".xlsm"

      Name DIRECTORY & Filename As DIRECTORY & NewName
    End If

    On Error GoTo 0
  End With
End Sub

it works perfectly, thank you so much

Just I replaced "ligne" by "row" so now the code is

Sub RENAME_FILES(row As Long)

Dim LR As Long, i As Long, Filename As String, OldName As String, NewName As String, DIRECTORY As String, REF As String

DIRECTORY = "\\192.168.2.6\elenalouroutziati_document\B R E A K D O W N S\BREAKDOWNS 2016\"

With ThisWorkbook.Sheets("RECAP")

LR = .Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).row

On Error Resume Next

REF = .Range("C" & row).Value

OldName = "CALC(P) - " & REF & " - " & .Range("L" & row).Value & " - " & .Range("M" & row).Value & " + " & "CALC(S) - " & .Range("W" & row).Value & " - " & .Range("X" & row).Value & " - " & .Range("F" & row).Value & ".xlsm"
Filename = Dir(DIRECTORY & OldName)

If Filename <> "" Then

NewName = "CALC(P) - " & REF & " - " & .Range("L" & row).Value & " - " & .Range("M" & row).Value & " + " & "CALC(S) - " & .Range("W" & row).Value & " - " & .Range("X" & row).Value & " - " & .Range("F" & row).Value & " - " & Format(.Range("AI" & row).Value, "dd-mm-yy") & ".xlsm"

Name DIRECTORY & Filename As DIRECTORY & NewName
End If

On Error GoTo 0
End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub

If Not Intersect(Columns("AI"), Target) Is Nothing Then

If Not IsDate(Target) Then Exit Sub
RENAME_FILES Target.row

End If
End Sub

Hello and welcome

To test

If not it must be the main file and the macros and 2-3 to rename files

Sub RENAME_FILES(Ligne As Long)
Dim LR As Long, Filename As String, OldName As String, NewName As String, DIRECTORY As String, REF As String

  DIRECTORY = "C:\Users\mciavaldini.TAMOILCY\Documents\test\"

  With Workbooks("CARGOES 2016").Worksheets("RECAP")
    LR = .Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

    On Error Resume Next
    REF = .Range("C" & Ligne).Value
    OldName = "CALC(P) - " & REF & " - " & .Range("L" & Ligne).Value & " - " & .Range("M" & Ligne).Value & " + " & "CALC(S) - " & .Range("W" & Ligne).Value & " - " & .Range("X" & Ligne).Value & " - " & .Range("F" & Ligne).Value & "*.xlsm"
    Filename = Dir(DIRECTORY & OldName)
    If Filename <> "" Then
      NewName = "CALC(P) - " & REF & " - " & .Range("L" & Ligne).Value & " - " & .Range("M" & Ligne).Value & " + " & "CALC(S) - " & .Range("W" & Ligne).Value & " - " & .Range("X" & Ligne).Value & " - " & .Range("F" & Ligne).Value & Format(.Range("AI" & Ligne).Value, "dd-mmm-yy") & ".xlsm"
      Name DIRECTORY & Filename As DIRECTORY & NewName
    End If

    On Error GoTo 0
  End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Count > 1 Then Exit Sub
  If Not Intersect(Columns("AI"), Target) Is Nothing Then
    If Not IsDate(Target) Then Exit Sub
    RENAME_FILES Target.Row
  End If
End Sub

I have a file "cargoes 2016" as a database where the datas are sorted per "REF"(column "C"), I would like that when the cell of the colum "AI" is updated for a specific "REF", it goes to a specific folder and rename automatically the file whose name include "ref". However being totally new in vba, I don't know how to refer to the good cells/column.

Basically, if I update the cell of the column "AI" (=date) in the row of ref "2016xx001", the file in the specific folder who includes in its name "2016xx001" is automatically renamed (without opening)

what I did until now is create a module with the action "rename file" and insert code under the worksheet that if cell ai is updated, "rename file" macro runs

Here below both codes

Sub RENAME_FILES()

Dim LR As Long, i As Long, Filename As String, OldName As String, NewName As String, DIRECTORY As String, REF As String

LR = Workbooks("CARGOES 2016").Worksheets("RECAP").Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

On Error Resume Next
For i = 2 To LR
DIRECTORY = "C:\Users\mciavaldini.TAMOILCY\Documents\test\"

REF = Workbooks("CARGOES 2016").Worksheets("RECAP").Range("C" & i).Value

OldName = "CALC(P) - " & REF & " - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("L" & i).Value & " - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("M" & i).Value & " + " & "CALC(S) - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("W" & i).Value & " - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("X" & i).Value & " - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("F" & i).Value & ".xlsm"

NewName = "CALC(P) - " & REF & " - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("L" & i).Value & " - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("M" & i).Value & " + " & "CALC(S) - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("W" & i).Value & " - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("X" & i).Value & " - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("F" & i).Value & Format(Workbooks("CARGOES 2016").Worksheets("RECAP").Range("AI" & i).Value, "dd-mmm-yy") & ".xlsm"

Filename = Dir(DIRECTORY & "*" & REF & "*" & ".xlsm")

If Filename <> "" Then

   Name DIRECTORY & OldName As DIRECTORY & NewName
  End If

Next i
On Error GoTo 0
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long, i As Long

LR = Workbooks("CARGOES 2016").Worksheets("RECAP").Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

For i = 2 To LR

If Not Intersect(Range("AI" & i), Target) Is Nothing And Range("AI" & i) <> "" Then

RENAME_FILES

End If

Next i
On Error GoTo 0

End Subcode

Please note that my table has around 100 rows, each "ref" being "2016xx001", 2016xx002, 2016xx003 etc

so if I update the column AI of the ref 2016XX003, then my code goes to find the file which includes "2016xx003" in its title, and rename it adding at the end of the name the column Ai value....

I know my problem is how to refer to the good cell....can someone help?

Hello,

merged cells are sources of all kinds of difficulties.

My proposal to fix your issue.

ActiveSheet.Range("A10:C30").Find("Mr. G").Select

Hello,

1. Copy the procedure named TableNames in a new module in the copy of your file.

2 . Run the procedure.

3. See the name manager (Ctrl + F3)

4. Copy the procedure named UpdateDataInSheets in a module 1 (delete previous version).

5. Run the procedure.

Don't forget to check the results !...

Regards.

Option Explicit
'Rename all tables with smart names
Public Sub TableNames()
Dim wb As Workbook
Dim ws As Worksheet
Dim lo As ListObject
Dim txt As String
    Set wb = ThisWorkbook
        For Each ws In wb.Worksheets
            Select Case ws.Name
                'sheets to ignore
                Case "B_Confin":
                Case Else:
                'sheets to be processed
                txt = Replace(ws.Name, " ", vbNullString)
                Set lo = ws.Cells(1).ListObject
                lo.DisplayName = txt
                Set lo = ws.Cells(6).ListObject
                lo.DisplayName = txt & "2"
            End Select
        Next ws
End Sub

Public Sub UpdateDataInSheets()
Dim loData As ListObject, lo As ListObject
Dim Cell As Range, r As Range
Dim n, arr(1)
Dim dt As Double
Dim nm As String
    Set loData = Range("Business_Confidence").ListObject
    If loData.DataBodyRange Is Nothing = False Then
        For Each Cell In loData.ListColumns(1).DataBodyRange
            dt = Cell.Offset(, 1).Value
            nm = VBA.Replace(Cell.Value, " ", "")
            On Error Resume Next
            Set lo = Range(nm).ListObject
            On Error GoTo 0
            If Not lo Is Nothing Then
                n = Application.Match(dt, lo.ListColumns(2).DataBodyRange, 0)
                If IsError(n) Then
                    arr(0) = Cell.Offset(, 1).Value
                    arr(1) = Cell.Offset(, 2).Value
                    With lo
                        'If .InsertRowRange Is Nothing Then
                            Set r = .HeaderRowRange.Cells(1).Offset(.ListRows.Count + 1)
                        'Else
                            'Set r = .InsertRowRange.Cells(1)
                        'End If
                    End With
                    r.Offset(, 1).Resize(, 2).Value = arr
                End If
            End If
        Next Cell
    End If
End Sub

Hi all,

I've just started to learn about coding in VBA and I have an error in the following code that I don't know how to resolve.

I want to use a CheckBox, the first part If block is working as it should but the second one is not.

When I run the code the error 91 appear saying "Object Variable or With block variable not set".

ActiveSheet.Range("A10:A30").Find("Mr. G").Select

Does anyone know how to resolve this issue ?

Thank you !

Private Sub CheckBox1_Click()

If CheckBox1.Value = True Then
ActiveSheet.Range("A10:A30").Find("").Select

Range(ActiveCell, ActiveCell.Offset(0, 2)).BorderAround _
ColorIndex:=1, Weight:=xlThin
Range(ActiveCell, ActiveCell.Offset(0, 2)).Merge
Range("A10:C10").BorderAround _
ColorIndex:=1, Weight:=xlMedium

ActiveCell.Value = "Mr. G"
ActiveCell.Font.Name = "Arial"
Range(ActiveCell, ActiveCell.Offset(0, 2)).HorizontalAlignment = xlCenter
Range(ActiveCell, ActiveCell.Offset(0, 2)).VerticalAlignment = xlCenter

End If

If CheckBox1.Value = False Then
ActiveSheet.Range("A10:A30").Find("Mr. G").Select
ActiveCell.ClearContents

End If

End Sub

I've taken your suggestions.

I've renamed all my tables for better understanding.

And I've added macros, and it WORKS!!!

I'm new to Excel and I'm starting to discover that this program is a very powerful software that I will enjoy.

Thanks again.

Good morning,

Hope all is well. I don't know if you all can help me. I am stuck and I don't know what to do?

I have one more document that is challenging to fix. It was working before. I don't know what is the reason of the problem in Macros.

How do you want me to proceed?

Thanks in advance

... sp=sharing

Hello Everyone.

If you are open, I would like your support once more.

I have a similar problem on my first plead above on macro error message. Where Jean-Eric have help me so wonderfully.

I have tried all the codes that I have received in the pass, none of them have worked.

Please view document on the link below, if you are opened in helping me out. The file is large, was not able to paste same on this website.

Thanks in advance.

I really don't know what to say.

Jean-Eric. I really appreciate your continual support, always.

You demonstrates a character of a true friend. One that shows your personality with out complaints, dependable, reliable and supportive.

It seems like procrastination is your worst enemy. and helping others are your best friend.

Thank you again Jean-Eric. for your commitment and passion for excel.

Hello

A proposal to consider.

Regards.

Public Sub UpdateDataInSheets()
Dim loData As ListObject, lo As ListObject
Dim Cell As Range, r As Range
Dim n, arr(1)
Dim dt As Double
Dim nm As String
    Set loData = Range("PMI_17").ListObject
    If loData.DataBodyRange Is Nothing = False Then
        For Each Cell In loData.ListColumns(1).DataBodyRange
            dt = Cell.Offset(, 1).Value2
            nm = VBA.Replace(Cell.Value2, " ", "")
            On Error Resume Next
            Set lo = Range(nm).ListObject
            On Error GoTo 0
            If Not lo Is Nothing Then
                n = Application.Match(dt, lo.ListColumns(1).DataBodyRange, 0)
                If IsError(n) Then
                    arr(0) = Cell.Offset(, 1).Value2
                    arr(1) = Cell.Offset(, 2).Value2
                    With lo
                        'If .InsertRowRange Is Nothing Then
                            Set r = .HeaderRowRange.Cells(1).Offset(.ListRows.Count + 1)
                        'Else
                            'Set r = .InsertRowRange.Cells(1)
                        'End If
                    End With
                    r.Resize(, 2).Value2 = arr
                End If
            End If
        Next Cell
    End If
End Sub

Hello Excel World.

I have a problem. I don't know if you can help me out here.

I have a macros name "Update Sheets" on one sheet #1 "PMI"

When I click the Macros to copy and paste each (country, date and value) into each work sheet database.

I get an error message that states, "Run-Time Error"9" Subscript Out of range".

How can I fix this?

Please view documents below.

Thanks in advance

Corrie