Excel Macro - Create new files from ranges Closed

Question

Hi.  I am trying to create a macro that will create new excel files for ranges of rows in a worksheet. 

I want to copy the range F5:S61 and paste it into a new worksheet as values with a file name of F5.  
Then copy the range F64:S120 and paste it into a new worksheet as values with a file name of F64. 


Repeat that 115 times.  The row numbers increase by 59 each time.  

Thanks for your help!

venkat1926 · Answer

NOT VERIFIED AS NUMBER OF TIMES IS TOO LARGE

Sub test()
Dim j As Long, k As Long, n As Long
Worksheets("sheet1").Activate
Do
n = 1
j = 5
Range(Cells(j, "F"), Cells(j + 56, "S")).Copy
Worksheets("sheet2").Cells(Rows.Count, "F").End(xlUp).Offset(1, 0).PasteSpecial
j = j + 59
n = n + 1
If n > 115 Then Exit Do
Loop
End Sub

Jiminie.Glick · Answer

Thanks venkat1926!  That helped me out a lot!!!

Jiminie.Glick · Answer

I ended up modifying it to this: 


Sub Copy_Section_Paste_New_Workbook()
Dim j As Long, k As Long, n As Long
n = 1
j = 5
Do
Range(Cells(j, "F"), Cells(j + 56, "S")).Copy

Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveWindow.Zoom = 85
    
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "_201201"
    Range("A5").Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(R[-2]C,R[-1]C)"
   
    ThisFile = Range("b1").Value
    ActiveWorkbook.SaveAs Filename:=ThisFile
    If ActiveWorkbook.Saved = False Then
        ActiveWorkbook.Save
    End If
    ActiveWorkbook.Close
    
j = j + 59
n = n + 1
If n > 115 Then Exit Do
Loop
End Sub

Thanks again!  I wouldn't have figured it out without your help!!!

Jiminie

Excel Macro - Create new files from ranges

3 responses

Similar discussions

Subscribe To Our Newsletter!