Link to home
Start Free TrialLog in
Avatar of jfrank14
jfrank14

asked on

excel macro help with changing cell values

hi,
i am trying to add values into a spreadsheet based on a macro.

basically the macro runs off a spreadsheet then opens a new spreadsheet and does what it needs to do.

i want to save the values before it opens the new spreadsheet into the MACRO spreadsheet.

I believe it should be right before or after:
FileCopy SelFiles(0), Fn

I want to take the value from "pathname" and place it in J:9
I want to take the value from "fn" and place it in A:9

current code:

Sub CopyWorkbook()

    ' modify path as required
'    Const PathName As String = "H:\Reports"
    
    Dim SelFiles() As String
    Dim Fn As String                        ' File name
    Dim Sp() As String
   
    
    If GetSelectedFiles(PathName, SelFiles) Then
        Fn = NewFileName(SelFiles(0))
        If Len(Fn) Then
            Fn = WithSeparator(PathName) & Fn & ".xlsx"
            FileCopy SelFiles(0), Fn
            Workbooks.Open Fn
            With Workbooks.Open(Fn)
                Application.Calculation = xlCalculationAutomatic
                Sp = Split(.Name, ".")
                'add date
                .Sheets("Avg Daily Vol").Range("A5").Value = _
                        Left(.Name, Len(.Name) - (Len(Sp(UBound(Sp))) + 1))
                        
                'increment MTD, QTD, YTD
                .Sheets("Input").Range("B20").Value = _
                        .Sheets("Input").Range("B20").Value + 1
                        
                        .Sheets("Input").Range("B21").Value = _
                        .Sheets("Input").Range("B21").Value + 1
                        
                        .Sheets("Input").Range("B22").Value = _
                        .Sheets("Input").Range("B22").Value + 1
            End With
            'CloseThisWorkbook
        Else
            MsgBox "No valid file name was supplied.", _
                   vbCritical, "Can't rename"
        End If
    End If
End Sub

Open in new window


Thanks!
Avatar of Faustulus
Faustulus
Flag of Singapore image

Hi,
Please specify the worksheet on which you wish to write to J9 and A9.
Your code has earned a few inconsistencies since I last saw it :-)
 Workbooks.Open Fn
            With Workbooks.Open(Fn)
                Application.Calculation = xlCalculationAutomatic
The first line of code quite obviously opens a workbook by the name of Fn. Note that Fn specifies the full path at this time.
The second line doesn't refer to the same workbook. Instead, it opens another one. I think Excel should give you a warning at that moment.
The third line, finally, has no relevance to opening and manipulating workbooks. Unless you are in the habit of changing your Excel application settings Calculation will be set to Automatic already. However, if you just want to make sure put the command at the top of your code (row 10 in your above post). Note that this is a setting of the application, applicable to all workbooks open or to be opened. You can't have a different setting for workbook(Fn).

Now, two lines below the above three you will find this code:-
.Sheets("Avg Daily Vol").Range("A5").Value = _
                        Left(.Name, Len(.Name) - (Len(Sp(UBound(Sp))) + 1))
This line writes the name of the just opened Workbook(Fn) to the cell A5 in the "Avg Daily Vol" sheet. You might change the range from "A5" to "A9". Note that, together with the preceding Split function Left(.Name, Len(.Name) - (Len(Sp(UBound(Sp))) + 1)) extracts the file name which is also included in Fn.

.Sheets("Avg Daily Vol").Range("J9").Value = PathName
would do the other part of your request, if the sheet is the correct one to write to.
Avatar of jfrank14
jfrank14

ASKER

Thanks for your reply!

I have moved the following per your request to row 10:
Application.Calculation = xlCalculationAutomatic

basically just wanted to make sure it was set to automatic. for some reason there were times where it was set to manual, so this was sort of a safety.

Regarding the values i want to change, i actually want to add them/change on the macro sheet, rather than the sheet that is being opened.

So the macro runs on Macro.xlsm (for example) , opens the sheet, and does the work, etc.

However,:
I want to take the value from "pathname" and place it in J:9
I want to take the value from "fn" and place it in A:9
This is all on the macro xlsm , rather than the file that is being opened.
This code should do the job,
        If Len(Fn) Then
            With ThisWorkbook.Sheets("Sheet1")
                .Range("A9").Value = Fn
                .Range("J9").Value = PathName
                .Save
            End With

Open in new window

Please change the sheet's name as required.
Remember, the workbook opened in line 16 can be addressed as ActiveWorkbook in line 17, or you can delete line 16 alltogether and use only line 17, but your present code opens the same workbook twice.
works great. only issue is with:
.save

i had to comment it out, and it worked fine, but when leaving it in the code,
it says: "run time error 438, object doesnt support this property or method"

can you please advise?

thanks!
also thanks for that spot. i removed:
Workbooks.Open Fn

on line 16.

appreciate it.
My bad. Sorry about that. Here is the improvement:-
        If Len(Fn) Then
            With ThisWorkbook
                With .Sheets("Sheet1")
                    .Range("A9").Value = Fn
                    .Range("J9").Value = PathName
                End With
                .Save
            End With

Open in new window

Thanks dude. That works great.

If I wanted to have another macro not part of the sub above, and wanted to call the macro using the sheet that is open from the above, how would i go about doing that? technically the workbook will be named whatever is Fn or .Range("A9").Value = Fn (minus the file extension).

example:
I want to copy B8:O11 on sheet: "Input"
Copy and paste values into sheet "Avg Daily Vol". G5:T8

on sheet Fn

Thanks!
any ideas Faustulus?

appreciate it!!
Hi, I've been travelling for the season and not looking in very much  - hmm, not at all.
I find it easiest to design a procedure by writing down what it is supposed to do and then "translate" the plan language into code. I'll take you through the (theoretical) steps here in the following. Please pay attention to the naming of procedures and variables which is one of the main tasks (if you do it right).

Step 1 - Define the task
[Private] Sub CopyInput()
    ' Copy from Input!B8:O11
    ' To Avg Daily Vol!G5:T8
End Sub

Open in new window

Step 2 - Define the variables
[Private] Sub CopyInput()

    Dim WsS As Worksheet     ' S = Source
    Dim WsT As Worksheet     ' T = Target

    Set WsS = Sheets("Input")
    Set WsT = Sheets("Avg Daily Vol")

    ' Copy from Input!B8:O11 To To Avg Daily Vol!G5:T8
    WsS.Range("B8:O11").Copy Destination:=WsT.Range("G5")
End Sub[

Open in new window

Step 3 - Make the procedure variable
[Private] Sub CopyInput(WsS as Worksheet, WsT as Worksheet)
    ' Copy from Input!B8:O11 To To Avg Daily Vol!G5:T8
    WsS.Range("B8:O11").Copy Destination:=WsT.Range("G5")
End Sub[

Open in new window

Now you can define the worksheets outside. Meaning, you can call the same procedure from several locations in your code, each one defining different source and target sheets. You could do the same for the ranges by assigning the strings that define them to variables as I have done for the worksheets above.
The above sub would be called by a sub like the following. Observe that the code for this caller is basically taken from the original design.
[Private] Sub CopyRanges()

    Dim WsS As Worksheet     ' S = Source
    Dim WsT As Worksheet     ' T = Target

    Set WsS = Sheets("Input")
    Set WsT = Sheets("Avg Daily Vol")

    CopyInput WsS, WsT
End Sub[

Open in new window

Happy New Year!
appreciate the help. so i tried the above, and getting a compile error:
"ambiguous name detected: copyinput".

also im a bit confused as to how it knows the (Fn) worksheet is the worksheet to use when looking at the sheets: "input" and "avg daily vol".

thanks and happy new year!
I replied your question in a rather generic way. My code wasn't intended to be pasted and used. The error message you received seems to indicate that you have more than one procedure by the name of CopyInput which might be the case if you just copied all my code into one module without observing that one "CopyInput" actually evolved from another incarnation of the same thought.

Fn defines a workbook. Worksheets("Input") would be a worksheet in the ActiveWorkbook, since no workbook is specified in this command. Else, it might be Workbooks(Fn).Worksheets("Input") which is generic code again because Fn must adhere to precise rules - different, depending upon whether the workbook is open or closed. The point is that there are numerous ways of defining the workbook you wish to manipulate. You must choose one because if there is no workbook you can't define a worksheet. Plodding ahead slowly and methodically might be your best plan of action.
Dim Wb As Workbook
Set Wb = Workbooks.Add(Fn)
Or
Set Wb = ActiveWorkbook
Or
Set Wb = ThisWorkbook ' the one containing the code

Then,
Dim WsS as Worksheet
Set WsS = Wb.Worksheets("Input")

Your questions indicate that you are on the right track, but you will need to be more persistent in demanding answers that you can fully understand. That us the litmus tests if you understand what it does it will do what you want.
Good luck.
Faustulus
i get it, but having trouble figuring out how to establish what Fn is.

So in the beginning of the Module the macro runs and determines what Fn is, I want to store that, and use it again later for the code requested above.

When I take what you have and work with it, it can't determine what Fn is.

I get variable not defined highlighting Fn below:
Sub CopyPaste()
Dim Wb As Workbook
Set Wb = Workbooks.Add(Fn)
CopyRanges

End Sub

Open in new window


Basically the Sub CopyPaste() will be called from a different button on my spreadsheet that is not with the other functions. So Ill run CopyWorkbook, then later manually call the sub CopyPaste, which will see if Fn is loaded, and if not, give a msgbox, if it is, then perform the copy/paste listed.

Here is my full code so far:

Option Explicit
Sub CopyWorkbook()

    ' modify path as required
Const PathName As String = "H:\Futures\Macros"    
    Dim SelFiles() As String
    Dim Fn As String                        ' File name
    Dim Sp() As String
   
   Application.Calculation = xlCalculationAutomatic
    
    If GetSelectedFiles(PathName, SelFiles) Then
        Fn = NewFileName(SelFiles(0))
        'new code
        If Len(Fn) Then
        With ThisWorkbook
                With .Sheets("F&O Daily")
                .Range("A11").Value = Fn
                .Range("J11").Value = PathName
            End With
                .Save
            End With
'end new code
        If Len(Fn) Then
            Fn = WithSeparator(PathName) & Fn & ".xlsx"
            FileCopy SelFiles(0), Fn
            'Workbooks.Open Fn
            With Workbooks.Open(Fn)
                
                Sp = Split(.Name, ".")
                'add date
                .Sheets("Avg Daily Vol").Range("A5").Value = _
                        Left(.Name, Len(.Name) - (Len(Sp(UBound(Sp))) + 1))
                        
                'increment MTD, QTD, YTD
                .Sheets("Input").Range("B20").Value = _
                        .Sheets("Input").Range("B20").Value + 1
                        
                        .Sheets("Input").Range("B21").Value = _
                        .Sheets("Input").Range("B21").Value + 1
                        
                        .Sheets("Input").Range("B22").Value = _
                        .Sheets("Input").Range("B22").Value + 1
            End With
            'CloseThisWorkbook
        Else
            MsgBox "No valid file name was supplied.", _
                   vbCritical, "Can't rename"
        End If
    End If
    End If
End Sub

Private Function GetSelectedFiles(Pn As String, _
                                  SelFiles() As String) _
                                  As Boolean
    ' 0086 V 1.0
    
    Dim FoD As FileDialog                           ' File Open Dialog
    Dim SelItem As Variant                          ' Selected Item
    Dim i As Long                                   ' Index for SelFiles
    Dim DefaultDate As Date
    
    DefaultDate = Application.WorksheetFunction.WorkDay(Date, -2)
    Set FoD = Application.FileDialog(msoFileDialogFilePicker)
    With FoD
        .Title = "Choose the workbook to copy"
        .ButtonName = "Create Copy"
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xls*", 1
        .InitialFileName = WithSeparator(Pn) & _
                           Format(DefaultDate, "MMMM d yyyy") _
                           & ".xlsx"
        .AllowMultiSelect = False
        If .Show Then
            For Each SelItem In .SelectedItems
               ReDim Preserve SelFiles(i)
               SelFiles(i) = SelItem
               i = i + 1
            Next SelItem
            GetSelectedFiles = True
        End If
    End With
    Set FoD = Nothing
End Function

Private Function NewFileName(Ffn As String) As String

    Dim Sp() As String
    Dim Fn As String                ' File name (old)
    Dim Fnn As String               ' File name New
    Dim Fd As Date                  ' File date
    
    Sp = Split(Ffn, "\")
    Fn = Split(Sp(UBound(Sp)), ".")(0)
    If IsDate(Fn) Then
        Fd = Application.WorksheetFunction.WorkDay(CDate(Fn), 1)
        Fnn = Format(Fd, "mmmm d yyyy")
        Fn = Fn
    End If
    NewFileName = InputBox("Copying the workbook" & vbCr & _
                           "of " & Fn & Format(CDate(Fn), "  (dddd)") & vbCr & _
                           "Please confirm or enter " & _
                           "the copy's file name." & vbCr & vbCr & _
                           IIf(IsDate(Fn), "(" & Format(Fd, "dddd") & _
                           ")", ""), "New file name", Fnn)
End Function

Private Function WithSeparator(ByVal PathName As String, _
                               Optional ByVal RemoveExisting As Boolean) _
                               As String
                       
    Do While Right(PathName, 1) = Application.PathSeparator
        PathName = Left(PathName, Len(PathName) - 1)
    Loop
    WithSeparator = PathName & IIf(RemoveExisting, "", _
                                   Application.PathSeparator)
End Function

Sub CopyPaste()
Dim Wb As Workbook
Set Wb = Workbooks.Add(Fn)
CopyRanges

End Sub
Private Sub CopyRanges()

    Dim WsS As Worksheet     ' S = Source
    Dim WsT As Worksheet     ' T = Target

    Set WsS = Sheets("Input")
    Set WsT = Sheets("Avg Daily Vol")

    CopyInput WsS, WsT
End Sub
Private Sub CopyInput(WsS As Worksheet, WsT As Worksheet)
    ' Copy from Input!B8:O11 To To Avg Daily Vol!G5:T8
    WsS.Range("B8:O11").Copy Destination:=WsT.Range("G5")
End Sub

Open in new window

Your code isn't going to run. I suggest that you format it properly so that you can see what is missing:-
If GetSelectedFiles(PathName, SelFiles) Then
        Fn = NewFileName(SelFiles(0))
        'new code
        If Len(Fn) Then
        With ThisWorkbook
                With .Sheets("F&O Daily")
                .Range("A11").Value = Fn
                .Range("J11").Value = PathName
            End With
                .Save
            End With

Open in new window

It seems that neither of the two IFs has an End If. There also appear to be two End With statements for one With statement. Excel will give you confusing error messages because it can't differentiate between Emd If and End With (or next) and may tell you that a statement is missing although it is actually there. You should establish and maintain a visual control by having the opening and End statements vertically aligned, with anything between them indented.

Now about your question,
Sub CopyPaste()
Dim Wb As Workbook
Set Wb = Workbooks.Add(Fn)
CopyRanges
End Sub

Open in new window

You are absolutely correct in that Fn must be specified. You can do that, either by supplying it as a parameter, like
Sub CopyPaste(ByVal Fn As String)
    Dim Wb As Workbook
    Set Wb = Workbooks.Add(Fn)
    CopyRanges
End Sub

Open in new window

or you determine it in some other way before you use it.
Sub CopyPaste()
Dim Wb As Workbook
Dim Fn As String

Fn = ThisWorkbook.Sheets("F&O Daily").Range("A11").Value
Set Wb = Workbooks.Add(Fn)
CopyRanges
End Sub

Open in new window

In this idea you would retrieve the Fn you have previously set. You could add a test after doing so, like,
Sub CopyPaste()
    Dim Wb As Workbook
    Dim Fn As String

    If Len(Fn) Then
        Fn = ThisWorkbook.Sheets("F&O Daily").Range("A11").Value
        Set Wb = Workbooks.Add(Fn)
        CopyRanges
    Else
        MsgBox "No file name"
    End If
End Sub

Open in new window

its not picking up the (Fn). So I run the full code which works perfect. (Fn) is specified, however then when I want to run the new code-set for the next step (which will be separate from the first code-set), it doesn't pull in the (Fn) which was previously chosen, so i get the msgbox "no file name".

How can I get it to store the (Fn) from above, and use that later? That's what I'm having trouble doing.

B/c I want to then say, if (Fn) is open, then copy and paste info into diff sheets:

 If Len(Fn) Then
        With ThisWorkbook
                With .Sheets("Input")

' Copy from Input!B8:O11
    ' To Avg Daily Vol!G5:T8
'paste as values
'msgbox "Input->Avg Daily Vol Copy/Paste Complete"


Thanks!
Hi Faustulus,
Anything on the above please?
Sorry about the delay.I'm afraid, I will continue to be rather busy for the next couple of weeks.
VBA is a good Christian. Its left hand doesn't know what its right hand might be doing. Picking up a file name in one operation doesn't make that same name available to another, unless you specifically enable such transfer. The way I understood your project you are doing so by writing the name to a cell in a worksheet.

Now, your most recent opening gambit of If Len(Fn) Then must follow an effort to retrieve FN from where it was stored. Obviously, that can't be done unless the workbook is open. Therefore, you will need to include this test in the procedure for retrieving Fn. You might go like this,

If Not The workbook having Fn is Open Then
    Open it, or discard all of the following (Exit Sub / Function)
End If
Fn = The specified workbook's.The specified worksheet's. Range("J9").Text
and now ...
If Len(Fn) Then ...

If the formatting of source and destination is the same you can just say,
Wb?.Sheets("Avg Daily Vol").Range("G5:T8") = Wb?.Sheets("Input").Range("B8:O11")
The size of the two ranges must be identical.
Another way, also copying the formats, is
Wb?.Sheets("Input").Range("B8:O11").Copy Destination:=Wb?.Sheets("Avg Daily Vol").Range("G5")
The target is defined as a single cell and Excel does the rest.
If you wish to retain the formats in the target cells you should use Copy / PasteSpecial which has this syntax,
Wb?.Sheets("Input").Range("B8:O11").Copy
Wb?.Sheets("Avg Daily Vol").Range("G5").PasteSpecial xlValues
Application.CutCopyMode = False
Here, again, the target is specified as the first cell only.

I hope this gets you over the top.
Faustulus
appreciate it. i think i'm almost there.

Getting stuck on here though.  Getting invalid qualifier highlighting: "REPORT" in the line: "If REPORT.Open Then"

Can you help? Also, how does the res of this look? I want to paste values.

Sub CopyPaste()
    Dim Wb As Workbook
    Dim Fn As String
    Dim REPORT As String
    REPORT = "F&O Report Instructions.xlsm"
    If REPORT.Open Then
    Fn = REPORT.Range("J9").Text
    If Len(Fn) Then
    MsgBox ("" & Fn & "")
        'Fn = ThisWorkbook.Sheets("F&O Daily").Range("A11").Value
        Set Wb = Workbooks.Add(Fn)
        Wb.Sheets("Input").Range("B8:O11").Copy
Wb.Sheets("Avg Daily Vol").Range("G5").PasteSpecial xlValues
Application.CutCopyMode = False
MsgBox "Values pasted successfully!"
    Else
        MsgBox "No file name"
    End If
End Sub

Open in new window

i understand you are busy,  but if you can help for a few minutes please. i feel i am almost there.
It really isn't that I grudge the few minutes to help you out. It is the few minutes to open the EE site and look at any of the 150-odd threads awaiting my attention whichI am trying to save. So, even your plea, heart rending though it is to me, doesn't reach me. Sorry.

   Dim REPORT As String
    REPORT = "F&O Report Instructions.xlsm"

Obviously, REPORT is a String. Therefore it can't be an Object. But since a Workbook is an object, REPORT can't be a workbook. Observe that the string you assign to REPORT isn't a complete path. This workbook will be found if it is open. If you wish to open it you must provide the full file name, including drive and path. So, you need a function like this one,
Private Function GetWorkbook(Wn As String) As Workbook

    Dim Wb As Workbook
    Dim Sp() As String
    
    Sp = Split(Wn, "\")
    Debug.Print Sp(UBound(Sp))
    On Error Resume Next
    Set GetWorkbook = Workbooks(Sp(UBound(Sp)))
    If Err Then
        Set GetWorkbook = Workbooks.Open(Wn)
    End If
End Function

Open in new window

Wn (Workbook Name) is provided by the calling procedure. However, there is no guarantee that the function will return a workbook. If the workbook doesn't exist at the address provided in Wn no workbook object will be returned. So, you have to test for that after calling the function.
Observe that the workbook, if open, requires the a shorter version of the Ffn which the function creates from Ffn.
Also note that when the Report workbook is opened it will automatically become the ActiveWorkbook, but not so when it is already found open. Therefore you may enlarge your code to activate Wb after Report is found.

Perhaps your program could look like this,
Sub CopyPaste()
    Dim Wb As Workbook                  ' current workbook
    Dim Report As Workbook              ' referenced workbook
    Dim Ffn As String                   ' full file name of Report
    Dim Fn As String                    ' retrieved file name
    
    Ffn = "C:\Somewhere\Somewhat\F&O Report Instructions.xlsm"
    Set Report = GetWorkbook(Ffn)
    If Report Is Nothing Then
        MsgBox "Couldn't find the Report", vbCritical, _
               "Missing workbook"
        Exit Sub
    Else
        Fn = Report.Range("J9").Text
    End If
    If Len(Fn) Then

Open in new window

Finally, I would urge you to indent your code correctly. In VBE you create an indent using the tab character. It is very easy. Below is the code you posted, with indents added.
Sub CopyPaste()
    Dim Wb As Workbook
    Dim Fn As String
    Dim REPORT As String
    REPORT = "F&O Report Instructions.xlsm"
    If REPORT.Open Then
        Fn = REPORT.Range("J9").Text
    If Len(Fn) Then
        MsgBox ("" & Fn & "")
        'Fn = ThisWorkbook.Sheets("F&O Daily").Range("A11").Value
        Set Wb = Workbooks.Add(Fn)
        Wb.Sheets("Input").Range("B8:O11").Copy
        Wb.Sheets("Avg Daily Vol").Range("G5").PasteSpecial xlValues
        Application.CutCopyMode = False
        MsgBox "Values pasted successfully!"
    Else
        MsgBox "No file name"
    End If
End Sub

Open in new window

You can see immediately that
   If REPORT.Open Then
        Fn = REPORT.Range("J9").Text
    If Len(Fn) Then
an End If is missing here. Once indenting has been done correctly, the second If is in exactly the position where you should see an End If.
hi - so in looking further, i determined that i need Report to = the current workbook.

and then

Fn = CurrentWorkbook.Range("A11").Text

Open in new window


can you help with this please?

thanks.
ASKER CERTIFIED SOLUTION
Avatar of Faustulus
Faustulus
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ok - I think i got this. thanks!
Thank you for bearing with me through my travails - still at them, but coming up for air more often now.
Faustulus