Excel VBA: how to cut multiple rows and paste in a new file and save

albertxu

New Member
Joined
Jan 30, 2015
Messages
3
I have daily repetive tasks that need a VBA to help me out.
I have a file which is updated daily, the excel file contains like:
First Name|Last Name|Email| Password
John| Lee | Johnlee#hotmail.com | blabla
......

So here are my tasks, when a client ask for like 100 accounts, I cut 100 accounts starting ROW 2 and open a new excel file and paste those accounts (100 rows) from ROW 2 in that new file, and then I COPY the first row of the original file and paste that in the first row of the new file.
And then save the new file into the same folder.
And then save the original file.
I am thinking of putting a form in the file, one button and two text controls. One text control for inputting the number of rows which need to be cut and pasted each time, other text control for inputting username of the client, which will be used in the file name of the new file. I need the new file save in the name of "Number_username.xls".
I am very new to VBA and need your help. Thank you.
 
Last edited:

ParamRay

Well-known Member
Joined
Aug 6, 2014
Messages
1,195
.
.


Use this as a starting point:


Code:
Sub MoveRows()

  Dim varRowsToCut    As Variant
  Dim wbkDestination  As Workbook
  Dim strFileName     As String
  
GetRows:
  varRowsToCut = Application.InputBox( _
    Prompt:="Enter number of rows to cut:", Type:=1)
  
  If varRowsToCut = False Then
    Exit Sub
  ElseIf varRowsToCut <> Int(varRowsToCut) Or varRowsToCut < 1 Then
    MsgBox "Number is not valid.", vbExclamation
    GoTo GetRows
  End If
  
  Set wbkDestination = Workbooks.Add
  strFileName = ThisWorkbook.Path & "\" _
      & Format$(Now, "yyyymmddhhmmss") & ".xlsx"
  
  With ThisWorkbook.Worksheets(1)
    .Rows(1).Copy wbkDestination.Sheets(1).Range("A1")
    .Rows("2:" & 1 + varRowsToCut).Copy
  End With
  
  With wbkDestination
    .Sheets(1).Rows(2).Insert
    .SaveAs strFileName
    .Close
  End With
  
  ThisWorkbook.Worksheets(1).Rows("2:" & 1 + varRowsToCut).Delete
  ThisWorkbook.Save
  MsgBox "New file saved:" & vbCrLf & strFileName, vbInformation

End Sub
 

Forum statistics

Threads
1,081,981
Messages
5,362,535
Members
400,679
Latest member
alecalec202

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top