Update of Copied Excel sheet [Closed]

Report
-
 Blocked Profile -
Hello,
I want to make copies of excel sheet such that if I delete rows/columns in main sheet they should be deleted in copy too.


System Configuration: Windows / Chrome 58.0.3012.0

2 replies


Its called save as.....
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 3493 users have said thank you to us this month

Posts
2631
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 6, 2020
428
Hi Vinayak,

Could you give the following code a try and see how that works out for you? Keep in mind that actions done by macro's can't be reversed by using the blue arrows, so better test this on a copy of your workbook.

Here is the code:
Sub RunMe()
Dim x, y As Integer, mCheck As Boolean, ws As Worksheet

If Selection.Columns.Count > 16383 Then
    x = ActiveCell.Row
    y = Selection.Rows.Count
ElseIf Selection.Rows.Count > 1048575 Then
    x = ActiveCell.Column
    y = Selection.Columns.Count
    mCheck = True
Else
    Exit Sub
End If

For Each ws In Worksheets
    If mCheck = False Then ws.Rows(x & ":" & x + y - 1).Delete
    If mCheck = True Then
        ws.Select
        Range(Columns(x), Columns(x + y - 1)).Delete
    End If
Next ws
Sheets("Main").Select
End Sub


After pasting the code in a standard module, you can go back to Excel and hit Alt+F8 to display available macro's. Clicking on the options button will allow you to create a shortcut button. I used CTRL+d.
The rows or columns need to be connected.

Best regards,
Trowa