Using VBA to automatically save the file name from the worksheet cells.

**carlandtina02** · 11-12-2012, 08:38 PM

Hi Folks,

I am after a way of saving the current worksheet (sheet2 Service Report) to a new book and using cells C16 (Customer Name) and F10 (Date) to automatically save as the file name. I also need the folder path to be "C:\Documents and Settings\carl.walker\My Documents\Forms\".

I have had a crack at it with the code below, although I can get both bits to work well seperatly, I dont understand enough for me to combine them for what I want them to do.

If anyone could help me with this it would be much appreciated.....
Thanks in advance :o)
Carl

FIRST LINE OF CODE:

Please Login or Register  to view this content.

SECOND LINE OF CODE:

Please Login or Register  to view this content.

**patel45** · 11-13-2012, 03:48 AM

FIRST LINE OF CODE:

Please Login or Register  to view this content.

SECOND LINE OF CODE:

I can not help you, I don't know the content of cells B16 F10

**carlandtina02** · 11-13-2012, 04:40 PM

Hi Patel,

I have uploaded the workbook in question for you to veiw, Idealy I would like both pieces of code merged so that the info taken from cells B16 and F10 become the name of the saved file.

Many Thanks
Carl :o)

**patel45** · 11-14-2012, 03:26 AM

you attached an empty workbook, then

I can not help you, I don't know the content of cells B16 F10

**carlandtina02** · 11-14-2012, 03:46 PM

Hi,

Sorry about that, here's the original.

Regards
Carl

**patel45** · 11-14-2012, 04:04 PM

SECOND LINE OF CODE:

Please Login or Register  to view this content.

1) A folder name can not have chars like / \ : * |
2) There is nothing in column F

**carlandtina02** · 11-15-2012, 06:32 PM

Hi Patel,

Thanks for that, but what I need is something like the below code to work if possible.
The user form puts the date in F10 and underscore seems to save ok as a filename.
If you know a way of getting the following to work that would be great.

Sub SaveMyWorkbook()

Worksheets(Array("ServiceReport")).Copy
With ActiveWorkbook &_

Dim strPath As String
Dim strFolderPath As String
strFolderPath = "C:\Documents and Settings\carl.walker\My Documents\Forms\"
strPath = strFolderPath & _
Sheet2.Range("b16").Value & " " & _
Sheet2.Range("f10").Value & ""

End Sub

Kind Regards
Carl

**patel45** · 11-16-2012, 02:09 AM

Carl, if you want solve you have to attach a correct file, as I suggested, no file no answer from me.

**carlandtina02** · 11-16-2012, 12:10 PM

Hi Patel,

I'm a little confused with what you have said so I have attached another workbook, the sheet I want the code to work on is sheet 2(Service Report).

Hope this helps and sorry for any confusion.

Regards
Carl

**patel45** · 11-16-2012, 12:40 PM

I repeat a second time
1) A folder name can not have chars like / \ : * |
In column B descriptions start with R/c, then you can not save with this name because ther is the char /

**carlandtina02** · 11-17-2012, 08:15 AM

Ah I see the confusion now!

The referenced cells B16 and F10 are on sheet 2 (Service Report) not sheet 1 (Parts List). I only want to be able to save sheet 2 (Service Report) using its cells B16 and F10 to save as the filename.

Sorry for the confusion I know I asked for the current sheet to be saved when I should of asked for Sheet 2 to be saved instead.

Kind Regards
Carl

**patel45** · 11-17-2012, 09:09 AM

The referenced cells B16 and F10 on sheet 2 (Service Report) are empty

**carlandtina02** · 11-17-2012, 02:02 PM

Hi Patel,

Cells B16 and F10 get populated by the userform attached to that sheet. B16 (Account Details) will be text and numbers and F10 (Date) is set in the following format dd_mm_yy.

Hope this helps

Regards
Carl

**patel45** · 11-17-2012, 02:12 PM

Again
Carl, if you want solve you have to attach a correct file, as I suggested, no file no answer from me.

**carlandtina02** · 11-17-2012, 02:51 PM

As requested.

Regards Carl

**patel45** · 11-17-2012, 03:49 PM

Please Login or Register  to view this content.

**carlandtina02** · 11-17-2012, 04:54 PM

Patel,

Thank you very much, I know I haven't been the easiest novice to communicate with!

I settled with sub (savemyworkbook) as you have it working perfectly.

Again Many Thanks
Carl

Using VBA to automatically save the file name from the worksheet cells.

LinkBack

Thread Tools

Rate This Thread

Display

Using VBA to automatically save the file name from the worksheet cells.

Re: Using VBA to automatically save the file name from the worksheet cells.

Re: Using VBA to automatically save the file name from the worksheet cells.

Re: Using VBA to automatically save the file name from the worksheet cells.

Re: Using VBA to automatically save the file name from the worksheet cells.

Re: Using VBA to automatically save the file name from the worksheet cells.

Re: Using VBA to automatically save the file name from the worksheet cells.

Re: Using VBA to automatically save the file name from the worksheet cells.

Re: Using VBA to automatically save the file name from the worksheet cells.

Re: Using VBA to automatically save the file name from the worksheet cells.

Re: Using VBA to automatically save the file name from the worksheet cells.

Re: Using VBA to automatically save the file name from the worksheet cells.

Re: Using VBA to automatically save the file name from the worksheet cells.

Re: Using VBA to automatically save the file name from the worksheet cells.

Re: Using VBA to automatically save the file name from the worksheet cells.

Re: Using VBA to automatically save the file name from the worksheet cells.

Re: Using VBA to automatically save the file name from the worksheet cells.

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions