VBA cutting and inserting multiple rows
Results 1 to 8 of 8

Thread: VBA cutting and inserting multiple rows
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    49
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default VBA cutting and inserting multiple rows

    I am trying to code this correctly. Please Help

    I am working with data sets taking up 10 rows, no merged cells and all cells the same height, each job list takes up 10 rows.



    after selecting 10 rows making up 1 job, I want to cut them and insert them 10 rows up (moving the job above down to take its place)

    I hope this makes sense.

    I am using

    Sub Move_Up()
    Selection.Cut
    Selection.Offset(-10, 0).Select
    Selection.Insert Shift:=xlDown
    End Sub

    This works really well and there is no problem here.

    The problem is when I try to move the job (contained in the 10 rows) down and move the job below up

    I really hope this is making sense.

    This is not working.

    I am using

    Sub Move_Dn()
    Selection.Cut
    Selection.Offset(10, 0).Select
    Selection.Insert Shift:=xlDown
    End Sub

    This just appears to move the selection box down, but no data comes with it.

    I need it to be a cut and insert as this data is in a Gantt chart.

    I would really appreciate help with this

    Thanks

    JasonBing

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,897
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA cutting and inserting multiple rows

    You need to move down 20 rows like
    Code:
    Sub Move_Dn()
    Selection.Cut
    Selection.Offset(20, 0).Insert Shift:=xlDown
    End Sub
    Otherwise you paste directly below the existing selection, when is then cut, so the data moves back up to where it started
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    49
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA cutting and inserting multiple rows

    Wow. That was a fast reply. you are a dead set legend mate. Thanks works really well

    it has been a long day and you have helped me a great deal to get this finished

    Thanks


  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,897
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA cutting and inserting multiple rows

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Aug 2019
    Posts
    49
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA cutting and inserting multiple rows

    Hello again. I am not sure if I need to create a new thread but here we go

    I have a table of data recording details for each job an example of which is below.

    First Name Surname DOB Address email other other other other other other other other other other
    bob dylan 1-Aug 123 street 1@2.com 12345 12345 12345 12345 12345 12345 12345 12345 12345 12345
    sally taylor 1-Sep 124 street 3@2.com 54321 54321 54321 54321 54321 54321 54321 54321 54321 54321
    frank abignail 1-Oct 125 street 2@2.com 21564 21564 21564 21564 21564 21564 21564 21564 21564 21564
    tim sherman 1-Nov 126 street 5@2.com 65241 65241 65241 65241 65241 65241 65241 65241 65241 65241
    paul tims 127 street 4@2.com 65478 65478 65478 65478 65478 65478 65478 65478 65478 65478
    sam taylor 2-Dec 128 street 6@2.com 987987 987987 987987 987987 987987 987987 987987 987987 987987 987987

    What I am trying to do is create a macro to create a new sheet for a selected job using a template.

    Function 1, is to select the row of the selected Cell
    Function 2, is to create a new sheet from a template sheet and name it according to the content of the selected cell
    Function 3, is to copy the data from the selected row into row2 of the newly created sheet.
    Function 4, check to see if there is a sheet with this name and pop up a message box and end. (there is already a way to navigate to the sheet if there is one)

    The code I have created the new sheet fine. So if I select last name dylan, it creates a new sheet called dylan. I have no idea how to get the new sheet created to match a template sheet still working on that.

    The problem I have is that the data copied to the new sheet is for the last row of the selected data table. it doesn't matter which name I select the new sheet has sam taylors data in row 2.

    I would love some help with this as well as how to get the new sheet to be created from a template sheet

    Thanks for the help it is greatly appreciated.


    Sub test()


    Dim lastRow As Long
    Dim thisRow As Long
    Dim nextRow As Long
    Dim sheetCount As Long
    Dim selectedCells
    Dim newSheet As Worksheet


    On Error Resume Next


    Application.ScreenUpdating = False


    lastRow = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, "A").End(xlUp).Row
    selectedCells = Application.Selection.Value


    For sheetCount = 1 To UBound(selectedCells, 1)
    Set newSheet = Sheets.Add(After:=Sheets(Sheets.Count))
    newSheet.Name = selectedCells


    nextRow = 2
    For thisRow = 2 To lastRow
    If Sheets("Sheet1").Cells(thisRow, "A").Value = selectedCells(sheetCount, 1) Then
    Sheets("Sheet1").Cells(thisRow, "A").EntireRow.Copy Destination:=newSheet.Cells(nextRow, "A")


    End If

    Next thisRow
    Next sheetCount


    Sheets("Sheet1").Activate
    Range("A1").Select


    Application.ScreenUpdating = True


    End Sub

  6. #6
    New Member
    Join Date
    Aug 2019
    Posts
    49
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA cutting and inserting multiple rows

    GOOD NEWS

    I have an update. I am getting the create new from template now but still getting the wrong data

    Sub test2()


    Dim lastRow As Long
    Dim thisRow As Long
    Dim nextRow As Long
    Dim sheetCount As Long
    Dim selectedCells
    Dim newSheet As Worksheet


    On Error Resume Next


    Application.ScreenUpdating = False


    lastRow = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, "A").End(xlUp).Row
    selectedCells = Application.Selection.Value


    For sheetCount = 1 To UBound(selectedCells, 1)
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    Set newSheet = Sheets(Sheets.Count)
    newSheet.Name = selectedCells


    nextRow = 2
    For thisRow = 2 To lastRow
    If Sheets("Sheet1").Cells(thisRow, "A").Value = selectedCells(sheetCount, 1) Then
    Sheets("Sheet1").Cells(thisRow, "A").EntireRow.Copy Destination:=newSheet.Cells(nextRow, "A")


    End If

    Next thisRow
    Next sheetCount


    Sheets("Sheet1").Activate
    Range("A1").Select


    Application.ScreenUpdating = True


    End Sub



    This creates a new sheet with cell name and copies the template!

    Still doesn't check to see if there is already one and stop code yet.

    Cheers

  7. #7
    New Member
    Join Date
    Aug 2019
    Posts
    49
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA cutting and inserting multiple rows

    Now it is pasting the right data. I just need figure out how to get it to search and stop if there is a sheet with this name with a message box!!

    YAY

    Sub CreateJobCard()


    Dim lastRow As Long
    Dim thisRow As Long
    Dim nextRow As Long
    Dim sheetCount As Long
    Dim selectedCells
    Dim newSheet As Worksheet


    On Error Resume Next


    Application.ScreenUpdating = False


    lastRow = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, "A").End(xlUp).Row
    selectedCells = Application.Selection.Value


    For sheetCount = 1 To UBound(selectedCells, 1)
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    Set newSheet = Sheets(Sheets.Count)
    newSheet.Name = selectedCells


    nextRow = 2
    For thisRow = 2 To lastRow
    If Sheets("Sheet1").Cells(thisRow, "A").Value = selectedCells Then

    Sheets("Sheet1").Cells(thisRow, "A").EntireRow.Copy Destination:=newSheet.Cells(nextRow, "A")
    nextRow = nextRow + 1

    End If

    Next thisRow
    Next sheetCount


    Sheets("Sheet1").Activate
    Range("A1").Select


    Application.ScreenUpdating = True


    End Sub

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,897
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA cutting and inserting multiple rows

    As this is a totally different question, you need to start a new thread.
    Thanks
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •