Excel Macro - Create new files from ranges

Closed
Jiminie.Glick
Posts
3
Registration date
Wednesday February 15, 2012
Status
Member
Last seen
February 20, 2012
- Feb 15, 2012 at 10:37 AM
Jiminie.Glick
Posts
3
Registration date
Wednesday February 15, 2012
Status
Member
Last seen
February 20, 2012
- Feb 20, 2012 at 04:32 PM
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!

3 replies

Jiminie.Glick
Posts
3
Registration date
Wednesday February 15, 2012
Status
Member
Last seen
February 20, 2012
1
Feb 20, 2012 at 04:32 PM
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
1
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
805
Feb 16, 2012 at 06:19 AM
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
0
Jiminie.Glick
Posts
3
Registration date
Wednesday February 15, 2012
Status
Member
Last seen
February 20, 2012
1
Feb 20, 2012 at 04:28 PM
Thanks venkat1926! That helped me out a lot!!!
0