Making a Macro add a date after completing action

**Bestia** · 12-22-2014, 01:12 AM

Hey Guys

I have created a Macro that creates a new CSV from a worksheet in my workbook
What I am trying to do is make it add a the date to B5 of my sheet called gobalsettings

Each time I try I keep getting the error

Run-time error '1004'
Method 'Range' of object'_Gobal" failed

Sub CreateCSV()

Dim strFileName As String

strFileName = Application.GetSaveAsFilename(filefilter:="Comma Seperated Values (*.csv), *.csv")

If strFileName <> "False" Then
    Sheets("products").Copy
    ActiveWorkbook.SaveAs strFileName, xlCSV
End If

Range("GobalSettings!B4").Value = Date

End Sub

**HaHoBe** · 12-22-2014, 01:20 AM

Hi, Bestia,

try

Worksheets("GobalSettings").Range("B4").Value = Date

instead.

Ciao,
Holger

**Bestia** · 12-22-2014, 01:38 AM

Thanks I tried that and got error message

Run-time error '9':
Subscript out of range

Maybe I need to switch back to the original workbook

I tried

Workbook("Product_Prices").Worksheets("GobalSettings").Range("B4").Value = Date

But that didnt work either

**HaHoBe** · 12-22-2014, 01:01 PM

Hi, Bestia,

once you refer to a workbooik you should have tghe extensio to that as well as part of the name.

So if you have an xlsm workbook it may look like

Workbook("Product_Prices.xlsm").Worksheets("GobalSettings").Range("B4").Value = Date

But I think you may simplify if GlobalSetting is located in the same workbook Prodduct:

Sub CreateCSV()

Dim strFileName As String

strFileName = Application.GetSaveAsFilename(filefilter:="Comma Seperated Values (*.csv), *.csv")

If strFileName <> "False" Then
    Worksheets("GobalSettings").Range("B4").Value = Date
    Sheets("products").Copy
    ActiveWorkbook.SaveAs strFileName, xlCSV
End If

End Sub

which would only alter the date if a string for savoing the csv is chosen.

Ciao,
Holger

Making a Macro add a date after completing action

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Making a Macro add a date after completing action

Re: Making a Macro add a date after completing action

Re: Making a Macro add a date after completing action

Re: Making a Macro add a date after completing action

Thread Information

Users Browsing this Thread

Similar Threads

Macro Not Completing, No Errors

Need help completing an IF function with date & time as inputs

making action buttons to add and subtract cells

Help completing VBA code that emails me when expiry date 30 days before.

[SOLVED] VBA Macro not completing it's task.

Bookmarks

Bookmarks

Posting Permissions