Need to copy the data into new sheet with cell based file name.

Hi Experts,

Need to get the below result through VBA code based on the attached sample workbook.

1. I need to copy the entire data from sheet1 into new sheet with paste special values without blanks.
2. Sheet needs to be saved on the desktop irrespecive of any user with file name based on the sheet2 cell E1.

Regards,
Anwitha

Please Login or Register  to view this content.

Similaly above codes, I am applying with sheet25, with below codes and its not working. Any rason?


Application.ScreenUpdating = False
Worksheets.Add before:=Sheets("sheet2")
With Sheets("Sheet25").UsedRange
.AutoFilter 1, "<>"
.Copy Sheets(Sheet25).Cells(1)
.AutoFilter
End With
Sheets("sheet1").Copy
With ActiveWorkbook
.SaveAs CreateObject("WScript.Shell").SpecialFolders("DeskTop") & _
"\" & ThisWorkbook.Sheets(Sheet25).[I7].Value & ".xlsx"
.Close False
End With
Application.DisplayAlerts = False
ThisWorkbook.Sheets("sheet1").Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

1) Don't change Sheets(1) in my code.
2) replace "sheet1" withe the sheet name that you have oroginal data.
3) replace Sheets("sheet2).[e1] with actual sheet name and cell address.
Do not forget to add double quotes.
Sending This from phone.

Oh....Thank you so much for your immediate response...Now its really working......Thanks a lot again...

You are welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Hello Jindon,

I have applied this code with new workbook but, the data is copying with formulas, i need it as values and range should be copied frm A to Z column

here is the code i used. Please check and help.

Please Login or Register  to view this content.

Try add one line

Please Login or Register  to view this content.

added the above code, but still values are appearing with formulas..also as i said it need to copy the collumn A to Z only....now it is copying entire sheet.

Then try change to

Please Login or Register  to view this content.

If i give the range as .Columns("a:z").Copy Sheets(1).Cells(1) then, column H&I comes as "00.01.1900" instead of actual dates.

If i remove the above code, then it will copy entire sheet with all workings..

Upload a workbook that you are working with.