Previous Topic Next Topic

Update of Copied Excel sheet

Closed
Vinayak - Updated on Mar 21, 2019 at 01:19 PM
 Blocked Profile - Mar 21, 2019 at 01:18 PM
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

Answer 1 / 2
Blocked Profile
Mar 21, 2019 at 01:18 PM
Its called save as.....
1
Answer 2 / 2
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
 509
Mar 21, 2019 at 12:47 PM
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
0