Xtykutl

I am currently working on an Excel document where I would like to click on a cell and have a box pop-up with options of more command buttons to choose from. When the user clicks on one of the buttons within the user form the button it would then move a selection of that line of A:G to another sheet. I will have multiple lines and would like to code to adjust to the box where the initial command was initiated from. I have the first button and the user form complete and it is working well. The issue is on the next set of buttons to copy, paste and delete the line. Here is what I have so far. Any help would be appreciated.

Private Sub FireChief_Click()

Dim i As Long: i = 1

Dim n As Long

Dim nLastRow As Long

Dim lastRow As Integer

With ActiveSheet

    For n = nReference To nLastRow Step 1



ActiveSheet.UsedRange



Set r = ActiveSheet.UsedRange

nLastRow = r.Rows.Count + r.Row + 1

Set Reference = Selection

Next Copy_to_Range()

ActiveWorksheets.Range("Reference").Copy

   Destination = Worksheets("Fire Chief").Range("nLastRow")

Worksheets("Fire Chief").Columns("A:G").AutoFit



End Sub 

So instead of the code listed above, I tried to rework it with some of the input that was provided. Basically this in a way would work like an Access document, and when the button was clicked it would do all of the work for the end user. I work in a fire dept and would like to make it easier on those who have to use it. There are 10 sheets that I have to work with and once I figure out one I will rewrite to match for the other. The new code is below

Option Explicit

Sub BaseFireMarshal_Click()



End Sub



Sub FireChief_Click()

Dim i As Long: i = 1

Dim n As Long

Dim nLastRow As Long

Dim lastRow As Integer

Dim B As Integer

Dim R As Range

    Set R = Sheet5.Range("A2:G999")

    If Worksheets("ActiveSheet").Range("A:XFD").Copy_ = True Then

    Dim continue As Boolean

        continue = True

        If continue = True Then



            Worksheets("Sheet5").Activate

            B = Worksheets("Sheet5").Cells(Rows.Count, 1).End(xlUp).Row

            Worksheets("Sheet5").Cells(B + 1, 1).Select

            ActiveSheet.Paste

            Worksheets("Fire Chief").Columns("A:G").AutoFit

            Worksheets("Sheet5").Deactivate

        End If



        If Application.CutCopyMode = False Then 

ThisWorkbook.Worksheets("ActiveSheet").Cells(1, 1).Select

        End If



End Sub







Private Sub cmdClose_Click()

Unload Me

End Sub

I would like to click on a cell and have a box pop-up with options of more command buttons to choose from.

you could do this with grouping columns, from the "data" tab. essentially it collapses/expands a portion of the worksheet.

the button it would then move a selection of that line of A:G to another sheet

cut and paste

would like to code to adjust to the box where the initial command was initiated from

gonna wanna use "selection.column" or "selection.row" in your VBA code for this.

Here is what I have so far.

as for your code, I never use named ranges. I always use range(cells(row,column),cells(row,column)) because then its dynamic.

Try doing that and get back to us.