Excel Macro - Create new files from ranges

[Closed]
Report
Posts
3
Registration date
Wednesday February 15, 2012
Status
Member
Last seen
February 20, 2012
-
Posts
3
Registration date
Wednesday February 15, 2012
Status
Member
Last seen
February 20, 2012
-
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

Posts
3
Registration date
Wednesday February 15, 2012
Status
Member
Last seen
February 20, 2012
1
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
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
Posts
3
Registration date
Wednesday February 15, 2012
Status
Member
Last seen
February 20, 2012
1
Thanks venkat1926! That helped me out a lot!!!