• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need Simple VBA

Istiyak

Istiyak

Member
Hi Team,


I m not very mch powerful in VBA bt preety sure you guys help me.


Need Simple VBA For Given Details.


Sheet 1

cell Details

A1 Mr. Null

A2 Week2

A3 Site0452


Sheet 2

name=ANYTHING


Need to creat 1 folder in current directory where file saved with the content in A2.

Then save "sheet 2" as a workbook in above folder with name of the content A3.

Then Alert "Details Exported Succesfully in ---Path or location.

Then Alert "Thank You ----Content of A1".

Summry

Folder : Week2

File Name : Site0452

Sheet : ANYTHING


Any query plz let me know.


Regards

!$T!
 
Try this

[pre]
Code: 
Dim sh As Worksheet
Dim dirName As String

Set sh = ActiveWorkbook.Worksheets("Sheet6")
dirName = sh.Range("A2").Value
MkDir dirName
Worksheets("Sheet2").Copy
With ActiveWorkbook
.Worksheets(1).Name = .Worksheets(1).Range("A2").Value
.SaveAs CurDir & "" & dirName & "" & sh.Range("A3").Value
MsgBox "Details Exported Succesfully in " & ActiveWorkbook.Path
MsgBox "Thank you " & sh.Range("A1").Value
.Close
End With
[/pre]
 
Sorry, mis-read it a bit

[pre]
Code: 
Dim sh As Worksheet
Dim dirName As String

Set sh = ActiveWorkbook.Worksheets("Sheet1")
dirName = sh.Range("A2").Value
MkDir dirName
Worksheets("ANYTHING").Copy
With ActiveWorkbook
.SaveAs CurDir & "" & dirName & "" & sh.Range("A3").Value
MsgBox "Details Exported Succesfully in " & ActiveWorkbook.Path
MsgBox "Thank you " & sh.Range("A1").Value
.Close
End With
[/pre]
 
Thanks Dear


jst one worry why this code saving file on last saved location and not on the current file directory.


really Cheers to u...


Regards

Istiyak
 
Hi, Istiyak!


I tried the code and it works fine, I think. It saves the selected "ANYTHING" sheet to a workbook named as A3 in a folder called as A2.


I would only make this correction.

Add (unquoted) "On Error Resume Next" line before "MkDir...", and "On Error Goto 0" after it.


It's because the second time you run the macro, it raises an error while trying to create a folder that exists.


Regards!
 
Hi SirJB7


i m not talking about the raising error.,,


I m talking about the given line

Actual line

.SaveAs CurDir & "" & dirName & "" & sh.Range("A3").Value

Proposed line

.SaveAs thisworkbook.path & "" & dirName & "" & sh.Range("A3").Value


(Hope u will understand)


Plz let me know.


Regards

Istiyak
 
You must log in or register to reply here.
Back
Top