+ Reply to Thread
Results 1 to 5 of 5

Move Rows to another spreadsheet with Macros and Button

Hybrid View

  1. #1
    Registered User
    Join Date
    07-30-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Move Rows to another spreadsheet with Macros and Button

    I have this spreadsheet I want to use daily for tracking parts, each day we will start a fresh spreadsheet for a total of 30/31 spreadsheets in the workbook depending on the month, I would like to move items that have not arrived to the following day spreadsheet without cutting and pasting from day to day as there could be multiple rows, I have made a few failed attempts at writing a macro and attaching to a button and was hoping someone could help, below is a sample of my spreadsheet, there are about 70 rows in each spreadsheet for me to enter data.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,714

    Re: Move Rows to another spreadsheet with Macros and Button

    Hi do you need a separate button for each item? If not, a simpler macro could copy the sheet, rename it with the next day's date, then delete all rows for which there is no date in the "Received" column (or "Arrived" if you prefer).

    If you neet to specify each row individually, a checkbox or "yes/no" column could be used rather than the received column to determine whether to copy the row.

  3. #3
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,714

    Re: Move Rows to another spreadsheet with Macros and Button

    for the first option try this macro:

    Sub NewDay()
    Dim NewName As String, NewDate As Date, LastRow As Long, x As Long
    NewName = "New Name"
    On Error GoTo nxt
    NewDate = DateValue(ActiveSheet.Name & " " & Year(Date)) + 1
    NewName = Format(NewDate, "dd mmmm")
    
    nxt:
    If NewName = "New Name" Then
        NewName = InputBox(ActiveSheet.Name & " is not recognised as a date. Please enter a name for the new sheet", "new name", NewName)
    End If
    ActiveSheet.Copy Before:=ActiveSheet
    ActiveSheet.Name = NewName
    LastRow = Cells(Columns(8).Rows.Count, 8).End(xlUp).Row
    
    For x = LastRow To 11 Step -1
        If Columns(8).Rows(x).Value = "" Then Columns(8).Rows(x).EntireRow.Delete
    Next x
    
    End Sub

  4. #4
    Registered User
    Join Date
    07-30-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Move Rows to another spreadsheet with Macros and Button

    That was pretty cool, thanks for the post, but really not going to work as a whole spreadsheet, I would need for them to be individual rows moved each with their own button. I installed some buttons on the spreadsheet already but when it came to writing the macros I do not understand how to make all of them work so that when I press any given one the chosen ones move to the next days spreadsheet in the next avaliable line on the following day.

  5. #5
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,714

    Re: Move Rows to another spreadsheet with Macros and Button

    ok, try these modifications to the macro you run when you click a button. It assumes:
    1) the sheet you want to copy to is the sheet immediately to the left of the active sheet
    2) the rows you want to copy into in the destination sheet are the same as the rows you are copying from in the source sheet (this appears to be how your original macro was designed)

    Sub moverecord()
    Dim DestSheet As String,  CopyRow As Long
    DestSheet = Sheets(WorksheetFunction.Max(1, ActiveSheet.Index - 1)).Name ' sheet to copy to. Assumes sheet to the left of the active sheet
    CopyRow = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row
    Range("B" & CopyRow & ":" & "K" & CopyRow + 1).Copy Destination:=Sheets(DestSheet).Range("B" & CopyRow & ":" & "K" & CopyRow + 1)
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy button macros to rows below
    By pdale18 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-19-2013, 08:29 PM
  2. Macros to move multiple rows of data for same unique identifier into one.
    By TPMIS in forum Excel Programming / VBA / Macros
    Replies: 31
    Last Post: 07-05-2013, 03:14 PM
  3. Button for selection to move rows.
    By fareen in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-05-2011, 03:39 PM
  4. Macros to move multiple rows to one row.
    By trangie in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-16-2011, 02:39 PM
  5. [SOLVED] Button to move rows to other tab
    By Stacie Fugate in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-01-2005, 01:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1