Copy range from 1 sheet to another workbook with VBA

[sanjuss2]

Hi
I need a VBA Code to create a macro to get rid of tiresome work….
I am using Office 2010.
I have an folder name D:\Policy Data in which Policy Record.xlsx is File which contains 6 sheets.
Out of these sheets, 1 sheet named "OUTPUT" has contain cell A1 : Branch Name & B1 : Today Date & I want to copy cell range from A2:AQ10456 to another workbook as values which must have name as Branch Name with date.xlsx because in this sheet I have used Array formulas to get desired on the basis of A1: Branch Name & B1: Today Date
In manual practice I have to copy cell into another workbook as values & save it as ‘branch name with Date’ for all 37 branches everyday.
Can anyone help me to get out of this annoying work??
Thanks a lot in advance…

[sanjuss2]

This code I have got from Chandoo.org to copy range cells to another workbook

Sub copy2report()
'Workbooks.Open "C:\Report.xls" 'change path as required
Range("C5:M500").Copy 'define the range to copy
'Windows("Report").Activate
'Sheets("Data").Select
Workbooks.Add
Range("C1").PasteSpecial xlPasteAll
Application.CutCopyMode = False
ActiveWorkbook.SaveAs "C:\Report" & Format(Now(), "DD-MM-YYYY") 'change date format as required
End Sub

This code I have got from MrExcel Forum for selecting file name after copying data to another workbook

Sub cpyNwb2()
Dim sh As Worksheet, wb As String, newWb As Workbook
wb = ThisWorkbook.FullName
Set sh = ActivieSheet
Set newWb = sh.Copy
newWb.Sheets(1).Name = Range("A2").Value
newWb.SaveAs Range("A1").Value
Workbooks.Open wb
newWb.Close
End Sub


Kindly look into this..... is it useful or how i can merge this to get desired results......
I want values in new workbook which have name from define cell values.......

[sanjuss2]

Default Re: Copy range from 1 sheet to another workbook with VBA

I have used this code & it works perfect...
I have made 1 change that instead of two cells I have merged it to 1 for giving name to new workbook....
Any way thanks.........

Sub GenerateNewFile()
Dim wbI As Workbook, wbO As Workbook
Dim wsI As Worksheet, wsO As Worksheet
Dim nPath As String
nPath = "D:\" & ThisWorkbook.Sheets("Output").Range("C1").Value & ".xlsx"
Set wbI = ThisWorkbook
Set wsI = wbI.Sheets("Output")

Set wbO = Workbooks.Add

With wbO

Set wsO = wbO.Sheets("Sheet1")

.SaveAs FileName:=nPath

wsI.Range("A3:AE10300").Copy

wsO.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With
End Sub

[sanjuss2]

Can anyone provide help for finding accurate rows & columns to copy into new workbook in above code

wsI.Range("A3:AE10300").Copy