Auto insert "Group" in Private VBA

[Mehmud Khan]

Hii,

While working on a project I stuck on argument. Could any one help me into this.

Issue :

When I click on "Insert Day" button (feeded with VBA coding of copy > paste of few cells) under sheet, than few cells are copied and get paste as I want. But they are not getting grouped as in first data.

Please refer the attached data file than you would be able to understand better.

Solution looking for :

When I click on the "Insert Day" button, than VBA insert the data as I feeded, but the same VBA also insert the Group as into previous and simultaneously when I click on next day than another data of day will minimize the grouping.

Hope you understood and will give better response.

Thanks and Best Regards!
Mehmud Khan

 

[Kenneth Hobson]

Sub Insert_Day()
  Dim v As Integer
  With Worksheets("xData")
    v = .Visible
    .Visible = xlSheetVisible
    .[A57:T66].Copy _
      Cells(Rows.Count, "A").End(xlUp).Offset(11)
    .Visible = v
  End With
  [A21].Select
  Application.CutCopyMode = False
End Sub

 

[Mehmud Khan]

Hello Kenneth,

Thanks for your reply!

The above VBA codes help me to paste the data from hidden sheets.
But it's not the solution which I am looking for...

As in the attached screenshot, only data are getting paste from another sheet but the VBA is not Grouping the same data as the above one.

Please refer the attached picture.

Thanks and Regards!
Mehmud

 

[Kenneth Hobson]

What is the purpose offsetting 11 rows? The usual need is to offset one.

 

[Mehmud Khan]

Hi,
The reason behind the Offset (11) is, if I run the macro for copy > paste (Insert Day) than, that data must paste after 11 cells of last active selected cells.
If you are still confused than, please remove the offset (11) and run the macro than you can see the problem. The new day data will be insert without any gap.

If do you have better solution than please provide me.

But still I am looking for the Grouping VBA...

Thanks !
Mehmud

 

[Kenneth Hobson]

Group outlines are row or column properties so it is not surprising that a range copy/paste of cells did not include them.

Did you (1) want to ungroup the other rows and then include them and the newly pasted range rows in one group or (2) add the pasted range row's as a separate group?

 

[Mehmud Khan]

hello,

Actually, I was looking for the VBA as attached.
Please refer the attached file and click on Insert Day button, then you will be able to understand my problem actually.

I have created that VBA by recording....

 

[Kenneth Hobson]

So, you went with grouping option (2).

Select, Selection, Activate, and such are seldom needed. It is the method that the recorder uses. If you want to go beyond the macro recorder, see: http://www.tushar-mehta.com/excel/vba/beyond_the_macro_recorder/index.htm

I would do it like this:

Sub Insert_Day()
  Dim v As Integer, r As Range
  With Worksheets("xData2")
    v = .Visible
    .Visible = xlSheetVisible
    .[A57:T82].Copy _
      Cells(Rows.Count, "A").End(xlUp).Offset(27)
    .Visible = v
  End With
  Set r = Cells(Rows.Count, "E").End(xlUp).Offset(-24)
  Range(r, r.End(xlDown)).Rows.Group
  Application.CutCopyMode = False
End Sub

 

[Mehmud Khan]

Ohh great! Now its looks good!
Thanks for your interest sir !!!


Thanks a lot!