Appending To current file VBA

Mustoe95

New Member
Joined
Nov 11, 2016
Messages
14
Hi all,

I have a vba set up already that works perfectly.

Shown below (Any simplifications would be brilliant)

Sub sbVBS_To_Delete_EntireRow()
Rows(1).EntireRow.Delete
ActiveSheet.Name = "Master"
'Updateby20150707
Dim Rng As Range
Dim xInterval As Integer
Dim xRows As Integer
Dim xRowsCount As Integer
Dim xNum1 As Integer
Dim xNum2 As Integer
Dim WorkRng As Range
Dim xWs As Worksheet
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
xRowsCount = WorkRng.Rows.Count
xInterval = Application.InputBox("Enter row interval. ", xTitleId, 100, Type:=1)
xRows = Application.InputBox("How many rows to insert at each interval? ", xTitleId, 1, Type:=1)
xNum1 = WorkRng.Row + xInterval
xNum2 = xRows + xInterval
Set xWs = WorkRng.Parent
For i = 1 To Int(xRowsCount / xInterval)
xWs.Range(xWs.Cells(xNum1, WorkRng.Column), xWs.Cells(xNum1 + xRows - 1, WorkRng.Column)).Select
Application.Selection.EntireRow.Insert
xNum1 = xNum1 + xNum2
Next
Sheets.Add After:=Sheets(Sheets.Count)
ActiveCell.FormulaR1C1 = "Alex"
Range("A2").Select
ActiveCell.FormulaR1C1 = "Andrew"
Range("A3").Select
ActiveCell.FormulaR1C1 = "Martin"
Range("A4").Select
ActiveCell.FormulaR1C1 = "Peter"
Range("A5").Select
ActiveCell.FormulaR1C1 = "Vince"
Range("A6").Select
ActiveCell.FormulaR1C1 = "Lee"
Range("A7").Select
ActiveCell.FormulaR1C1 = "Christine"
Range("A8").Select
ActiveCell.FormulaR1C1 = "Alison"
Range("A9").Select
ActiveCell.FormulaR1C1 = "Deb"
Range("A10").Select
ActiveCell.FormulaR1C1 = "Terri"
Range("A11").Select
ActiveSheet.Name = "Names"
mycount = 0
myrow = 0
Do
mycount = mycount + 1
oldrow = myrow + 1
Sheets("Master").Select
Do
myrow = myrow + 1
Loop Until Sheets("Master").Range("A" & myrow) = ""
Sheets.Add
ActiveSheet.Name = "Data" & mycount
Sheets("Master").Select
Rows(oldrow & ":" & myrow).Select
Selection.Copy
Sheets("Data" & mycount).Select
Range("A1").Select
ActiveSheet.Paste
Loop Until Sheets("Master").Range("A" & myrow + 1) = ""

With Sheets("Names").Range("A1:A10")
For n = 1 To 10
Sheets("Data" & n).Name = .Cells(n)
Next n
End With
'Updateby20140612
Dim xPath As String
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.Sheets
xWs.Copy
Application.ActiveWorkbook.SaveAs Filename:=xPath & "" & xWs.Name & ".xlsx"
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub






But The last step creates the new workbooks, but since that process has already happened, I want to replace the last step so that it appends the data to the workbooks that have already been completed.

Is this possible?
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top