Free Microsoft Excel 2013 Quick Reference

can't activate open workbook based on cell value

I have a spreadsheet that opens an excel file based on the value in a given cell. This works fine. However, I need to pass data from my form to this newly opened file. I can't seem to get this file to become the active workbook so I can complete my paste action. I mind you, the file is already open...so I'm rather puzzled why this isn't working. I have to call the file based on a variable, as the file names always change.

sub macro23()

Windows("Recon Builder Beta2.xls").Activate
Sheets("Purchase Recon").Select
 Range("L3:L4").copy
 
     purchfile = Range("k1").Value
    
    Windows.Activate Filename:=purchfile
    Sheets("Recon").Select
 
    Range("L3:L4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

end sub
Thanks, JS


Hi,

I am trying to pull data from a specific range (e.g. I1:L15) from a closed workbook based on cell values on an open workbook. Thing is that the name of closed workbook is unknown but user will enter the file's location, name, tab & cell with desired values in the open file on different cells in the open workbook. For example, cells in open workbook will contain:

- Cell E3: "File Path" (i.e. "C:UsersJavierDocumentsBusiness DocumentsAutomated Files")
- Cell F3: "File Name" (i.e. "10001")
- Cell G3: Concatenate Formula that combines value on F3 with ".xls" (i.e. "10001.xls")
- Cell H3: "Tab Name" (i.e. "Sales Invoice")
- Cell I3: "Cell with desired value" (i.e. "H3")
- Cell J3: "Cell to show the value from closed workbook (i.e. value on cell H3 of 10001.xls, which is located in C:UsersJavierDocumentsBusiness DocumentsAutomated Files)

Would like to be able to repeat the above on through row 1000 (i.e. E3:J1000) and, based on this range, construct fixed graphs and reports.

Thanks,

Searched the forum and also made google search but couldnot solve my problem so posting here.

I want to pull data from closed workbook based on cell values of open workbook of column B and the source file name is on cell J1.
Actually I save monthly files and opening balnce of current month should take vakues from previous month file.

Suppose current month is May 2008. Then Column Column D for May month shold take value from column G of April 2008.
For simplicity the previous month’s name and thus source file name will be placed on cell J1.

The code should loop from column B of source file and current May 2008 file and should pull values for only those items which are in the current file in the Column B. Thus those products which are deleted or newly added item in the current item should not copied. Though for new item no name will be thre in the source file but for deleted items the item might be there in the source file but the code should ignore those value.

For eg. Product dnut is not there in the current month but present in source file so the value should not be imported for dnut.

How this can be done?

Attached two files for ready reference.
NB: Keep productApril2008 as close file and product May2008 as open file and run the code from productMay2008.

I'd like to copy an existing workbook (that's closed, preferably) and name
the copy based on cell values. So, for example in column A of the active
workbook I have a list of different excel workbooks that are closed (with the
file path), and in column B I have a list of names that I want to call the
copied workbooks. For all of the items in the list I'd like to copy the
respective workbook and give it the listed name. I figure I need a VBA loop,
but have no idea on the commands needed. Thanks.

Hello,

I have been using the following code to generate a new workbook based on cell value using a template workbook...


	VB:
	
 Range) 
    Dim wbNew As Workbook 
    If Target.Cells.Count > 1 Then Exit Sub 
     
     
    On Error Resume Next 
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then 
        Set wbNew = Workbooks(Target.Text) 
        If wbNew Is Nothing Then Workbooks.Open ThisWorkbook.Path & "" & "Template.xls" 
        On Error Resume Next 
         
         
        ActiveWorkbook.SaveAs ThisWorkbook.Path & "" & (Target.Text) 
         
         
        ActiveWorkbook.Close SaveChanges:=True 
         
         
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


This code work well for me, but there are three issues:

1) When I delete a cell, it tries to create a new workbook
2) When a workbook based on cell value already exists, and we try to create a new cell with the same value, it tries to overwrite the existing workbook ---> I want to make it so if it already exists it does not try to overwrite it
3) I cannot delete a row by right clicking the row and clicking delete, it freezes excel. I can only do it by selecting the row and going into Edit --> Delete.

I appreciate the help in fixing this.

Thanks!

i just need a simple macro that will create and save a new workbook based on the value of a cell in the current work book

Hello all,

I am trying to make a spreadsheet that has a macro that will allow me/user to open a specific workbook in another folder based on a specific cell value in the active spread sheet. I.E. if cell B2 has a value of 7101010 the macro will open that .xls file when used or any other part number typed into B2. Hopefully this is clear enough.

Hi,

I'm writing some code to open a sheet based on cell reference, go back to the original sheet, copy some data and then re-select the previously opened sheet and paste it in there. The problem I'm having is trying so re-select the sheet, as I'm not sure the code to use. Can anyone help?

Code so far:


	VB:
	
 
fname = "VparchiveInvoices" & ActiveCell.Value 
Workbooks.Open fname 
Windows("Payment Run - Stamp Invoices").Activate 
ActiveCell.Offset(0, 9).Range("A1").Activate 
Selection.Copy 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
As you can see, I just require the last line to activate the "fname" sheet again, before pasting.

I have a problem to copy worksheets from multiple workbooks based on a cell value to a master copy. My supervisor has been nagging at me to come out with program that will help to retrieve the data. As my VBA knowledge is so bad that i really need the help from some of the experts in this forum.

Every month i need to get the data from 120 excel files"Day". Example I only wanted the Cell "A1"=1 data i will key 1 at the cell "A1" of the master copy, i need the VBA to help me open up all the 120 files and look through all the worksheets to see if their cell "A1" =1.If it is true, it will copy the whole table and paste it back to my master copy "Shift 1". If not it will go to the next worksheet (Each workbooks has only 3 worksheets), after the 3rd worksheet, it will close the workbook and carry on open up one by one all the 120 workbooks.

Thank you Auto Merged Post Until 24 Hrs Passes;

Dear experts

The below code is what i have done. I only able to copy out the worksheet but have no idea how to select the worksheet which i wanted. eg like mastercopy cell A1 got value = 1 choose only those sheet with cell value = 1.

Can anyone one please help me.


	VB:
	
 Importdata() 
     
    Dim x As Long, z As Variant 
    Dim bk As Workbook, sh As Worksheet 
    Dim sh1 As Worksheet 
    Dim sh2 As Worksheet 
    Dim sh3 As Worksheet 
     
     '  Change the next line to reflect the proper
     '  name and workbook where the data will be
     '  consolidated
     
     ' Select the directory to open
    SaveDriveDir = CurDir 
    MyPath = "Q:/" 
    ChDrive MyPath 
    ChDir MyPath 
     
    Set sh = Workbooks("Mastercopy.xls").Worksheets("Shift1") 
    z = Application.GetOpenFilename(FileFilter:= _ 
    "Excel files (*.xls), *.xls", MultiSelect:=True) 
    If Not IsArray(z) Then 
        MsgBox "Nothing selected" 
        Exit Sub 
    End If 
     
     'Open loop for action to be taken on all selected workbooks.
     
    For x = 1 To UBound(z) 
         
         'Open the workbook(s) that were selected.
        Set bk = Workbooks.Open(z(x)) 
         'Check if sheet Date exists
        On Error Resume Next 
         
        Set sh1 = bk.Worksheets("Sheet1") 
        Set sh2 = bk.Worksheets("Sheet2") 
        Set sh3 = bk.Worksheets("Sheet3") 
         
        On Error Goto 0 
         ' if it exists, copy the data
        If Not sh1 Is Nothing Then 
            Set rng = sh1.Range("A2:K5") 
            Set rng1 = sh.Cells(Rows.Count, 1).End(xlUp)(2) 
            rng.Copy 
            rng1.PasteSpecial xlValues 
            rng1.PasteSpecial xlFormats 
        End If 
         
         
        If Not sh2 Is Nothing Then 
            Set rng3 = sh2.Range("A2:K5") 
            Set rng4 = sh.Cells(Rows.Count, 1).End(xlUp)(2) 
            rng3.Copy 
            rng4.PasteSpecial xlValues 
            rng4.PasteSpecial xlFormats 
        End If 
         
         
        If Not sh3 Is Nothing Then 
            Set rng5 = sh3.Range("A2:K5") 
            Set rng6 = sh.Cells(Rows.Count, 1).End(xlUp)(2) 
            rng5.Copy 
            rng6.PasteSpecial xlValues 
            rng6.PasteSpecial xlFormats 
            Application.DisplayAlerts = False 
        End If 
         
         'Close the District workbook without saving it.
        bk.Close False 
    Next x 
     
     'Message box to inform user the job is complete.
    MsgBox "The import is complete.", 64, "Done !!" 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi,

I am trying to write macro that, depending on the value in a particular cell, opens a specified workbook.

What i need is a macro that, when run,checks the value of cells in column GC. If the value is more than zero, opens the workbook specified in row GD. It should check until there is no more data.

GC GD
2 0 book1.xlsm
3 1.07 book2.xlsm
4 0 book3.xlsm
5 3.98 book4.xlsm

I hope that makes sense. Any help would be appreciated.

gs.

Hi all,

I am a beginner in Excel and I have just recently discovered this great forum! I hope someone here can help me with my problem since my own knowledge in VBA is limited.

It's sort of hard to explain, but what I would like to do is open workbooks that has certain dates in their filename. I would like to construct a VBA code that looks for the value "x" in row 2:2 and then use the text/value in the cell above in the filename (i.e. if cell contains "x" and the above cell contains the date "2010-08-20", open the workbook namned "dashboard-2010-08-20.xls").

So, I would like to create some kind of loop that look if the cell contains "x". If not, continue to the next cell. If it does contain "x", execute "open workbooks that include the date above in the filename" and do so as long as there is an "x".

I know I might come out I bit vague but I have attached a screenshot that might clarify things.

Attachment 33098

Thanks in advance!

/Meloco

Hi,

Is it possible to rename a sheet in a workbook based on the value in cell E2 (a number) concatenated with cell F2 (a date). The other problem is the date is in the format 11/07/2011 and i want it to be represented as 20110711.

I've tried concatenating the cells in the sheet but i lose the date formatting and it just does it as a number.

Basically in the end i want the sheet tab to say for eg.

33594037__20110711

Also it would be great if the workbook could be named the same.

Is this even possible??

Cheers, Stu

I am using the code below to format chart borders based on cell values. There are 32 charts embedded in the "charts" sheet and 32 rows of data in the "data" sheet. The charts are named "1" through "32". My hope is to format chart "1" based on the data in row 1, and so on. The macro is formatting the charts. However, not in the appropriate order. I have 8 rows of charts with 4 charts on each row. They are ordered by name from left to right, top to bottom. When I run the macro, it skips the 4th chart in each row (4, 8, 12, etc.) and returns to format them as charts 25 - 32. I had to delete those charts and create new ones at one point due to some formatting issues, so that may have something to do with it. Is there a way to make this work?


	VB:
	
 GraphLoop1() 
     
     
    For x = 1 To 32 
         
        Sheets("Data").Select 
        If Cells(x, 11).Value = "" Then 
            Sheets("Chart").Select 
            ActiveSheet.ChartObjects(x).Activate 
            With ActiveChart 
                .ChartArea.Border.LineStyle = none 
            End With 
        End If 
        Sheets("Data").Select 
        If Cells(x, 11).Value = "R" Then 
            Sheets("Chart").Select 
            ActiveSheet.ChartObjects(x).Activate 
            With ActiveChart 
                .ChartArea.Border.LineStyle = x1Solid 
                .ChartArea.Border.Weight = 4 
                .ChartArea.Border.Color = RGB(192, 0, 0) 
            End With 
        End If 
        Sheets("Data").Select 
        If Cells(x, 11).Value = "Y" Then 
            Sheets("Chart").Select 
            ActiveSheet.ChartObjects(x).Activate 
            With ActiveChart 
                .ChartArea.Border.LineStyle = x1Solid 
                .ChartArea.Border.Weight = 4 
                .ChartArea.Border.Color = RGB(255, 192, 0) 
            End With 
        End If 
        Sheets("Data").Select 
        If Cells(x, 11).Value = "G" Then 
            Sheets("Chart").Select 
            ActiveSheet.ChartObjects(x).Activate 
            With ActiveChart 
                .ChartArea.Border.LineStyle = x1Solid 
                .ChartArea.Border.Weight = 4 
                .ChartArea.Border.Color = RGB(0, 128, 0) 
            End With 
        End If 
         
    Next x 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I wrote a macro that creates a new worksheet based on the month and year and renames it based on that and it won't create a new worksheet until the first of every month nor will it create it if the worksheets name exists. For instance next month when it is ran it will create a new worksheet named "9-07".

Later on a user needs to do some updates then calculate some things, so I want it after it is running this other code to select the newly created worksheet "9-07". I have this referenced in cell A1 and it would be nice if you could activate a worksheet based on a cell reference. Does anyone know how? So cell A1 = the new worksheet "9-07".

I need to hide several rows, based on cell values found in one column, e.g. Column F has several subtotal rows. For every subtotal row that equals zero - I need to hide that row and/or some rows containing text above the row. There are several worksheets in my workbook (which will act as a template and be sent to many users-so code must be portable). Can someone help with a macro for this, please?

Hi Guys,

I've been looking around for the answer and found partial answer here:

Forum > Microsoft Office Application Help - Excel Help forum > Excel Programming > Change Pivot Table Filter Based on Cell Value.

The answer posted by T-J was:

Sub Apply_Date_Filter_From_Worksheet()
    Dim pvtTable As PivotTable
    Dim pvtField As PivotField
    Dim pvtItem As PivotItem
    Dim filterDate As Date
    
    Set pvtTable = Worksheets("Pivot_Sheet").PivotTables("PivotTable1")
    Set pvtField = pvtTable.PivotFields("Date")

    filterDate = CDate(Worksheets("Controls_Sheet").Range("B2"))
    
    On Error Resume Next  'in case date not found
    
    
        For Each pvtItem In pvtField.PivotItems
            If CDate(pvtItem.Value) = filterDate Then
                pvtItem.Visible = True
            Else
                pvtItem.Visible = False
            End If
        Next pvtItem
       
    
    
End Sub
I can adapt this to my workbook & it works okay, but I need to change the filter to a number of different values in a list, some of which might not be available in the data.

Background :

I'm taking 14,000 entries of Incident data & pivoting the incidents based on where they were resolved, the filter will be based on which resolution group has been given a 'tag' of Onsite/Remote/Customer, these would each have their own tab creating the list i'd like to pivot from.

I need the pivot to display all incidents resolved by resolution groups 'tagged' as onsite (I.e. all those groups in list one on Sheet 'Onsite')

These may not all be present in the incident data, i dont know if that will cause errors if it attmempts to set the filter to something that isnt there...

I have a workbook where I want it to print worksheets based on cell value. I always want the main worksheet (called Template) to print. I also want the worksheet associated with the value in cell A1:J1 (merged cell) to print. I thought I had the code right, or pretty close, but it doesn't seem to be working.

Public Sub PrintReport()
     
    Worksheets("Template").Select
     
    If Range("A1:J1").Value = "Example 1" Then
        Worksheets("Template").PrintOut
        Worksheets("Example 1").PrintOut
         
       If Range("A1:J1").Value = "Example 2" Then
        Worksheets("Template").PrintOut
        Worksheets("Example 2").PrintOut
             
        If Range("A1:J1").Value = "Example 3" Then
          Worksheets("Template").PrintOut
          Worksheets("Example 3").PrintOut
                
            End If
        End If
    End If
     
     End Sub
Is there something I'm missing here? Also, I want the Print Options pop-up box to appear as we sometimes need to change things there.

In my workbook sheet 11 has some ranges that need to have names based on cell values in sheet2 (for purposes of data validation lists).

Range S28:S46 will assume the name of sheet2A11 & sheet2A3.
(example name period_1unit_1)

Range U28:U46 will assume the name of sheet2A11 & sheet2A4.

Range W28:W46 will assume the name of sheet2A11 & sheet2A5

etc.

Right now I am calling the code when something is entered into A11.

I have tried if statement and select case, but I ran into complications with both.

I have posted both codes with the questions I have concerning those codes.

Can you help me either use one of these codes or come up with a better way?

SELECT CASE METHOD

Select Case
Target.Address

    Case "$A$ll"
        Sheet11.Range("S28:S46").Name = Target & Range("A3")
        Sheet11.Range("U28:U46").Name = Target & Range("A4")'
        Sheet11.Range("W28:W46").Name = Target & Range("A5")
        Sheet11.Range("Y28:Y46").Name = Target & Range("A6")
        Sheet11.Range("AA28:AA46").Name = Target & Range("A7")
    
    Case "$A$32"
        Sheet11.Range("S49:S54").Name = Range("A3") & Target
    
End Select
Nothing happens with this code, and I know it is because I do not have a value assigned to A11, but I have no idea what the user will enter into A11. I just need all those named ranges to occur once something is entered into A11.

IF STATEMENT METHOD

    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Address = "$A$11" Then Sheet11.Range("S28:S46").Name = Target &
Range("A3")
This code returns the desired result; however, I need to add to it, and I am not sure how. Something being entered into A11 results in 4 name codes. With an if statement I do not know how to display multiple results, which is why I tried the select case method

I am new to VBA. My thanks to the community for the education. I have built a spreadsheet for work and am nearly done, but have hit a snag. Using information found on the site, I have written code to hide/unhide rows and make other entries based on cell values. There are 3 main parts to my code, all in the same Worksheet Change macro. Two parts work fine.

The remaining part works great, hiding and unhiding rows when I change the value in the target cell. Unfortunately, when I select another cell everything unhides - including the parts that were hidden based on the target cell entry

Because it is a bit long (the case goes to 16), I have only posted partial syntax (repeat case 1 & 2 thru 16). After the problem sample, I have posted a complete macro sample. I do not well understand the rules for indenting code lines and apologize for any confusion:

Problem Section:

	VB:
	
 
    Select Case (Target.Value) 
    Case "0" 
        Range("97:112").EntireRow.Hidden = True 
    Case "1" 
        Range("97:97").EntireRow.Hidden = False 
        Range("98:112").EntireRow.Hidden = True 
    Case "2" 
        Range("97:98").EntireRow.Hidden = False 
        Range("99:112").EntireRow.Hidden = True 
    Case "16" 
        Range("97:112").EntireRow.Hidden = False 
    End Select 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Sample of complete code:

	VB:
	
 Range) 
     
    ActiveSheet.Unprotect Password:="ABC123" 
    Application.ScreenUpdating = False 
    Rows("88:93").EntireRow.Hidden = Range("C18").Value = "No" 
    Rows("95:117").EntireRow.Hidden = Range("C18").Value = "No" 
    Rows("31:36").EntireRow.Hidden = Range("J30").Value = "No" 
    Rows("70").EntireRow.Hidden = Range("J30").Value = "No" 
    Rows("118").EntireRow.Hidden = Range("J30").Value = "No" 
    Rows("140").EntireRow.Hidden = Range("J30").Value = "No" 
    Rows("157").EntireRow.Hidden = Range("J30").Value = "No" 
    If Target.Address = Range("J89").Address Then 
        Select Case (Target.Value) 
        Case "0" 
            Range("97:112").EntireRow.Hidden = True 
        Case "1" 
            Range("97:97").EntireRow.Hidden = False 
            Range("98:112").EntireRow.Hidden = True 
        Case "2" 
            Range("97:98").EntireRow.Hidden = False 
            Range("99:112").EntireRow.Hidden = True 
        Case "16" 
            Range("97:112").EntireRow.Hidden = False 
        End Select 
    End If 
    If Target.Address = Range("J30").Address Then 
        Select Case (Target.Value) 
        Case "No" 
            Range("H32:H34").ClearContents 
        End Select 
    End If 
    If Target.Address = Range("J38").Address Then 
        Select Case (Target.Value) 
        Case "No" 
            Range("J44").Value = 0 
        End Select 
    End If 
    ActiveSheet.Protect Password:="ABC123", UserInterfaceOnly:=True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi All,

i am trying to change the fill color of multiple autoshapes based on cell values using macros.

i have managed to change one shape using a worksheet_change event

but when i repeat the code i just get an "ambiguous name detected" error, which i assume is because i have two worksheet_change events that are named the same.

here is a copy of my working formula


	VB:
	
 
Private Sub Worksheet_Change(ByVal Target As Range) 
     
    If Range("FUNNELVAL2").value >= 0 And Range("FUNNELVAL2").value < Range("LSTYR2").value Then 
        ActiveSheet.Shapes("FUNNEL2").Fill.ForeColor.RGB = vbRed 
    ElseIf Range("FUNNELVAL").value > Range("LSTYR2").value Then 
        ActiveSheet.Shapes("FUNNEL2").Fill.ForeColor.RGB = vbGreen 
    Else: Range("FUNNELVAL").value = Range("LSTYR2").value 
        ActiveSheet.Shapes("FUNNEL2").Fill.ForeColor.RGB = vbYellow 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
how do i get this formula to repeat for other autoshapes and cell values in the same worksheet.

Please help t-bone

I am trying to link a filename based on cell value For example B3 = 01-06-08 and I have a file called 01-06-08.xls I want cell C4 to have something like this =IF(ISNA(VLOOKUP(A4,'Y:OperationsConsolidationReceiving2nd Shift Receiving Logs["B3".xls]Associates Daily Log'!$B$2:$F$18,3,FALSE)) so in the final result I am looking for =IF(ISNA(VLOOKUP(A4,'Y:OperationsConsolidationReceiving2nd Shift Receiving Logs[01-06-08.xls]Associates Daily Log'!$B$2:$F$18,3,FALSE))

I understand how to sort based on Ascending order and descending order but what if I have to sort based on cell value? For example, in Col. 'L' I have a "Type' header name and under that there are probably 10 different categories. Few of them are "Achieved", "Bypass" "Certifications", "Monitor",and "YTD" . I would like to know how can I sort all the rows with "Certifications" first and then comes rest of the data and finally at the buttom of the data is "Monitor". Please suggest. Thanks for your help in advance!!

Hello, I would like help with a formula or VBA that would show comments based on cell values. For example, if cell A1 has cell value '2143', I want comments to be 'Twinkle Twinkle Little Stars' and if cell D1 has cell value '4567', I want 'Humpty Dumpty Fall on a Wall', and etc. How can I do it? I have about 35 different cell value categories. I appreciate any help you can give me. Thanks in advance for your time and effort.

Hi,

I want to make a rectangle where the size of it is based on cell value. How to do that?

Regards

I have this certain value "Y" in all the sheets. In the summary sheet, I would like to get the cell address for this value in a given sheet. I know that you can get cell address using CELL worksheet function. However, for that you need to know the reference to the cell, which is what I am interested in.

I tried Hlookup to get column #. But couldn't figure out how to convert the number to letter so that I can use MATCH to get row # and then finally, index or address function to what I am looking for.

If someone knows how to convert column # to column letter OR knows a better/easier way to obtain cell reference/address based on cell value, that would be a great help!

Hopefully, I am clear enough.

Thanks!