Solved Delete cells in WS4 from info gained in WS1

May 27, 2013 at 10:30:04
Specs: Windows 7
Hi Guys,
A little poser that I can do manually, but require a form control button to do the following at the press of a button

WS1 has a list of names. These names appear in various worksheets in a denoted order from WS4
WS4 has the list of names in column AP with column AQ having data associated to the name in column AP
The names in column AP also have worksheets titled with those names

What I Require is as follows

WS1 will tell me which name can be deleted from a VLOOKUP function. The answer appears in cell G17
The name that appears will then require for the worksheet with the name as its title to be delete, and also in WS4, wherever the name appears in column AP, to be deleted along with the data in column AQ ('Tony Gibb' worksheet deleted, and in WS4 lets say AP30 = 'Tony Gibb', then AP30 and AQ30 to be cleared of data)

As always guys, still learning VBA so I have been mangling recorded macros together to get some where. Any help would be greatly appreciated


Sub Macro2()
'
' Macro2 Macro
'

'
    WorksheetName = Range("G17")
        Sheets(WorksheetName).Visible = True
            Sheets(WorksheetName).Select
                ActiveWindow.SelectedSheets.Delete
  
    WorksheetName = Sheets("LEGEND").Range("AP2:AQ2")
        rowNum = Sheets("REMMEMB").Range("J17")
  
   With Sheets(WorksheetName).Cells(1, rowNum)
            .ClearContents
   End With

End Sub


This is what I have so far but am coming up with a Run-Time Error 13. Type Mismatch for this part of the VBA

With Sheets(WorksheetName).Cells(1, rowNum)
.ClearContents
End With

Any ideas


See More: Delete cells in WS4 from info gained in WS1

Report •

✔ Best Answer
May 27, 2013 at 13:42:19
rowNum = Sheets("REMMEMB").Range("J17")
  Sheets("LEGEND").Range("AP" & rowNum & ":AQ" & rowNum).ClearContents

A couple of other points to remember for future code:

1 - If you wish to eliminate the dialog box related to deleting the sheet, you can disable Alerts before deleting the sheet.

2 - Rarely, if ever, do you need Select an object in VBA in order to perform an operation on it. For example, you can delete a Sheet or clear the contents of a Range directly within VBA just by referencing it in the instruction.

3 - A Sheet does not need to be Visible in order to delete it with VBA.

Sub DeleteSheetAndData()
'Determine Worksheet Name
   WorksheetName = Range("G17")
'Disable Alerts, Delete Sheet, Enable Alerts
       Application.DisplayAlerts = False
         Sheets(WorksheetName).Delete
       Application.DisplayAlerts = True
'Determine Row Number and Clear Data
   rowNum = Sheets("REMMEMB").Range("J17")
     Sheets("LEGEND").Range("AP" & rowNum & ":AQ" & rowNum).ClearContents
End Sub


Note: There is no error handling in this code, so if the Sheet you are trying to delete doesn't exist, or if the contents of J17 can't be used as a Row number, the code will simply fail with a cryptic VBA error message.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
May 27, 2013 at 12:30:53
Since I can't see your data, there are a couple of things in your code that I don't understand.

1:

WorksheetName = Sheets("LEGEND").Range("AP2:AQ2")

How are you pulling a WorksheetName from a range of 2 cells?

2:

Cells(1,rowNum)

The syntax for the Cells property is Cells(RowIndex, ColumnIndex). I realize that rowNum is nothing more than a variable name, but since you used it as a ColumnIndex argument, I don't know if you made an error in your usage or simply choose a poor variable name. I wouldn't use a variable name that implied a Row number as an argument that is supposed to represent a Column.

Perhaps if you explained these 2 items, I could be of more assistance with your problem.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
May 27, 2013 at 12:43:40
WorksheetName = Sheets("LEGEND").Range("AP2:AQ2")
rowNum = Sheets("REMMEMB").Range("J17")

With Sheets(WorksheetName).Cells(1, rowNum)
.ClearContents
End With

This bit of code is probably garbage. What I am trying to do is as follows

The sheet titled 'LEGEND' has a list of names in column AP and corresponding data in AQ. The Row that I need to go to is depicted by Sheet 'REMMEMB', Range "J17"

Therefore if 'REMMEMB!J17 = 5, I need to be able to clear the contents of 'LEGEND!AP5 and AQ5, if J17 = 42, then AP42,AQ42


Report •

#3
May 27, 2013 at 13:40:33
Sub Macro2()
'
' Macro2 Macro
'

'
    WorksheetName = Range("H17")
        Sheets(WorksheetName).Visible = True
            Sheets(WorksheetName).Select
                ActiveWindow.SelectedSheets.Delete
                
    colNum = Sheets("REMMEMB").Range("K16")
        rowNum = Sheets("REMMEMB").Range("K17")
  
   With Sheets("LEGEND").Cells(rowNum, colNum)
            .ClearContents
   End With
   
             colNum = Sheets("REMMEMB").Range("L16")
                 rowNum = Sheets("REMMEMB").Range("K17")
        
   With Sheets("LEGEND").Cells(rowNum, colNum)
                    .ClearContents
   End With

End Sub

Hi there, have been trying various ways and have come up with the above. My only problem is that the information being cleared from the cells is not quite correct.

I need to be able to clear both column AP and AQ with the same instruction. The way the VBa works at the moment, the Row number changes before the second phase of the clear takes place. Is there a simple instruction to amend this?


Report •

Related Solutions

#4
May 27, 2013 at 13:42:19
✔ Best Answer
rowNum = Sheets("REMMEMB").Range("J17")
  Sheets("LEGEND").Range("AP" & rowNum & ":AQ" & rowNum).ClearContents

A couple of other points to remember for future code:

1 - If you wish to eliminate the dialog box related to deleting the sheet, you can disable Alerts before deleting the sheet.

2 - Rarely, if ever, do you need Select an object in VBA in order to perform an operation on it. For example, you can delete a Sheet or clear the contents of a Range directly within VBA just by referencing it in the instruction.

3 - A Sheet does not need to be Visible in order to delete it with VBA.

Sub DeleteSheetAndData()
'Determine Worksheet Name
   WorksheetName = Range("G17")
'Disable Alerts, Delete Sheet, Enable Alerts
       Application.DisplayAlerts = False
         Sheets(WorksheetName).Delete
       Application.DisplayAlerts = True
'Determine Row Number and Clear Data
   rowNum = Sheets("REMMEMB").Range("J17")
     Sheets("LEGEND").Range("AP" & rowNum & ":AQ" & rowNum).ClearContents
End Sub


Note: There is no error handling in this code, so if the Sheet you are trying to delete doesn't exist, or if the contents of J17 can't be used as a Row number, the code will simply fail with a cryptic VBA error message.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
May 27, 2013 at 14:15:30
DerbyDad003, thankyou very much, I am slowly getting to grips with VBA and thank you for your patience. Works a treat

Report •

Ask Question