copy data to new workbook

[gssachin]

Dear All,

I am trying to open a new workbook and then copy date from main sheet to newly open workbook but getting error at
"Workbooks(wname).Activate" as "subscript out of range"

how to solve this.... I used workbook (2) instead of wname and its work but following macro not working..



Sub nworkbook()
Dim wname As String


wname = InputBox("Type here New File Name")


Workbooks.Add
ActiveWorkbook.SaveAs "C:\Users\Downloads\Excel_Training" & wname


ThisWorkbook.Activate


Sheet1.Activate


'Range("a1", Range("a1").End(xlDown).End(xlToRight)).Copy
Range("a1").CurrentRegion.Copy


Workbooks(wname).Activate


Range("a1").PasteSpecial (xlPasteColumnWidths)
Range("a1").PasteSpecial (xlPasteAll)


Workbooks(wname).Save
Workbooks(wname).Close
End Sub

 

[Fluff]

You are missing a \ from the end of the file path

 

[gssachin]

Ooops, my mistake...

But in original code it was there (i.e ""), My Codes are working fine, they ask user to provide file name, then it will save to given location, next they going to original workbook and copy the given range and getting error while activating newly created workbook.

I m trying to use Workbooks(wname).Activate is this wname is user given name (i.e. filename) but getting error "subscript out of range" instead of "wname" if I use "2" the its working and macro is pasting date in new workbook but not as I required (i.e Workbooks(wname).Activate)

 

[Fluff]

What is the value of wname?
Does it include the file extension?

 

[gssachin]

No user will only provide name he will not enter file extension. if user given the its working fine.
How to incorporate file extension in Workbooks(wname).Activate

 

[gssachin]

hey,

I tried this Workbooks(wname & ".xlsx").Activate and its work for me. Thanks for helping

 

[Fluff]

You're welcome & thanks for the feedback