Macro

[APML]

Hi All.. I'm not very good at macros or vba, pls keep this in mind while reading my issue.

In the view code section of a tab in my sheet, I have the code as per the screenshot below. These codes operate via a button on the sheet. I want to get rid of the button and just use a link to trigger a macro that does same thing. (which I know how to do).
My issue is that I don't know how to do this. i.e (i want the following to be a named macro).. Hope this question makes sense
Private Sub CommandButton2_Click()
Workbooks.Open "C:\CSVToday\1a.csv"
Workbooks("1a.csv").Sheets("1a").Range("A4:G2000").Copy
ThisWorkbook.Sheets("TodaysData").Range("C4").PasteSpecial Paste:=xlPasteValues
ActiveWindow.WindowState = xlMinimized
MsgBox ("Data Has Been Updated")

 

[Micron]

use a link to trigger a macro that does same thing. (which I know how to do)

Then move your button click event code into such a macro?

 

[APML]

Then move your button click event code into such a macro?

As mentioned I'm not so great at this, so pls excuse me if this sounds like a silly question. Where do I find the button click events?

 

[Micron]

You posted one.
Private Sub CommandButton2_Click()
So you'd move this code to the link code you say you know how to create?

Workbooks.Open "C:\CSVToday\1a.csv"
Workbooks("1a.csv").Sheets("1a").Range("A4:G2000").Copy
ThisWorkbook.Sheets("TodaysData").Range("C4").PasteSpecial Paste:=xlPasteValues
ActiveWindow.WindowState = xlMinimized

I'm not trying to be a smarta$$ - I don't actually know what you mean by using a link to run code so you got me there.

 

[APML]

You posted one.
Private Sub CommandButton2_Click()
So you'd move this code to the link code you say you know how to create?

Workbooks.Open "C:\CSVToday\1a.csv"
Workbooks("1a.csv").Sheets("1a").Range("A4:G2000").Copy
ThisWorkbook.Sheets("TodaysData").Range("C4").PasteSpecial Paste:=xlPasteValues
ActiveWindow.WindowState = xlMinimized

I'm not trying to be a smarta$$ - I don't actually know what you mean by using a link to run code so you got me there.

Ah sorry, Im probably using the wrong terminology. Im meaning to say that I'll create a hyperlink in a cell (lets say cell L1), when i hit the hyperlink it will trigger the named macro. I prefer this because I find the buttons awkward to use. Here's an example of what I mean:

End If
If Target.Range.Address = "$L$1" Then
Call ClearFilters

 

[APML]

Ah sorry, Im probably using the wrong terminology. Im meaning to say that I'll create a hyperlink in a cell (lets say cell L1), when i hit the hyperlink it will trigger the named macro. I prefer this because I find the buttons awkward to use. Here's an example of what I mean:

End If
If Target.Range.Address = "$L$1" Then
Call ClearFilters

Sorry just read the previous post prior to the last, I'm going to try that now, will let you know how I go. BTW, I really appreciate your help

 

[APML]

Yes, I've got it. 1 last question, how do I write the following as code (so I can use it in a macro like above)?

Do While fName <> "" & ""

If fName <> "1a.csv" Then 'or .txt or .csv or whatever

Kill "C:\CSVToday\" & fName



End If

fName = Dir

Loop

MsgBox ("All Files Hvae Been Deleted")


End Sub

 

[Micron]

Don't know what event you're using for this hyperlink selection. If it's the WorksheetSelection_Change event you don't really need the hyperlink?
Looks to me that what you posted should work, although I don't see why not Do While fName <> "" instead of Do While fName <> "" & ""
I'd probably write it this way though;

Do While fName <> ""
   If fName <> "1a.csv" Then Kill "C:\CSVToday\" & fName
   fName = Dir()
Loop

You should use code tags to maintain proper indentation and make code easier to read (vba button on posting toolbar).

 

[APML]

Worked perfectly, thanks for your help

 

[Micron]

You're welcome.