Search results

I get it right after I select the second file. I tried adding Application.DisplayAlerts but that messes up the formula. I'm running Excel 2010. It has me puzzled especially since you do not get that message.

The issue is, that once I have selected the files, I get a dialog box asking to Update Values from fName1. How do I avoid that?

That's the issue, the file name and the sheet name will always be different names. That is why I chose GetOpenFileName. Any suggestions?

I'm not sure I understand.

Thanks! I still get the dialog box and have to choose fName1. Thoughts?

OK, here are the two files. SalesLimit1 will be the previous file or fName1 and SalesLimit2 will be the current file or fName2. When the macro runs it asks: Update Values: & fName1 & What the macro does is opens fName1 (SalesLimit1) and puts a VLOOKUP in column O of fName2 (SalesLimit2). The...

I have this macro that works but it asks for me to choose the file fName1 three or four times. Why? Sub GetFile() Dim fName1 As Variant, wbo As Workbook Dim fName2 As Variant, wba As Workbook Dim WBName As Worksheet fName1 = Application.GetOpenFilename(filefilter:="Excel Files (*.XLS)...

I figured it out. Since the workbook only has one worksheet in it, I do not need to assign the worksheet a name. This code worked: Range("O2").FormulaR1C1 = "=VLOOKUP(RC[-1],'[" & fName1 & "]'!R2C14:R7C14,1,0)"

So, I'm working on this code and still get a Run-Time Error 438 on the VLOOKUP. Anybody?

So, I tried this and get a Run-Time Error 438 Range("O2").FormulaR1C1 = "=VLOOKUP(RC[-1],'[" & fName1 & "]" & WBName & "'!R2C14:R7C14,1,0)" It is like something in this part of the code is not correct: Set wbo = Workbooks.Open(fName1) wbo.Activate 'New code Set WBName = wbo.ActiveSheet...

Monty: Thanks! But... The issue is that both the workbook and worksheet names will change with fName1. This line of code: Range("O2").FormulaR1C1 = "=VLOOKUP(RC[-1],'[SalesLimit1-01262017-AM.xls]SalesData-012620'!R2C14:R7C14,1,0)" Needs to have this part...

Here you go: =VLOOKUP(N2,'[SalesLimit1-01262017-AM.xls]SalesData-012620'!$N$2:$N$7,1,0) Where wbo would replace SalesLimit1-01262017-AM.xls]SalesData-012620

OK, wbo = SalesLimit1-01262017-AM and wba = SalesLimit2-01272017-PM The macro is allowing the user to choose the spreadsheets. On the first selection you choose SalesLimit1-01262017-AM, on the second selection you choose SalesLimit2-01272017-PM. The macro is adding a 1 to all rows with data in...

One other thing, there is only 1 worksheet in the workbook wbo, but its name varies.

I am working on some code where I identify a workbook as "wbo" by way of GetOpenFileName. Set wba = Workbooks.Open(fName1) I am trying to do a VLOOKUP like this: Range("O2:O15").FormulaR1C1 = "=VLOOKUP(RC[-1],wbo R2C15:R150C15, 1, 0)" How do I reference wbo in the formula? Thanks.

Ratan. Absolutely perfect! I would have never figured this out. I can scrap static data, but this was beyond me. I cannot thank you enough.

I am wondering if someone can help me with this. I know very little about this type of VBA coding. I can scrap simple data, but this is a challenge. So here I will say many, many thanks! I have a list of numbers in an Excel spreadsheet and want to loop through them to extract data from the...

OK, I commented out Step 7 and the macro ran. So, how do I fix Step 7?

Good morning. I added the break and the macro ran like it is supposed to. I then added a break at Step 6. The macro was fine. So I added a break to Step 7. I did notice that the macro does pull in the data from Access, but it never pulls in the header names. So it is hanging between Step 6 and...

Yes. I am able to enter the dates. The data also pulls into the spreadsheet. Then, that is where it seems to hang-up.

Hello Narayan. Sorry it took me a while to reply. I had the flu and am just catching up. I added the Debugs to the macro. The macro never makes it to the first Debug. Right after it pulls the data into the spreadsheet, it just hangs there with the blue circle. As a matter of fact, nothing...

I have a macro that has been working for a while. For the last two months it has not been working. It pulls the data into the spreadsheet from Access and then just freezes. There are no errors, no nothing, just the little blue circle like it is doing something. But it just hangs there. Can...

I figured it out. Some of the worksheet names had & or - in them. That was making the formula angry.

The scenario: I have a workbook with140 worksheets with various names. I used a macro to extract the worksheet names. I pasted those names in M1:M140 and gave it a named range of Tabs in the Totals worksheet. In the 140 worksheets in cell B3 is one of 5 unique names that I want to sum on. The...

I have a macro that errors out on the line of code that says ActiveWorkbook. The exact same code runs well in one place but not the other. Any idea why? sFileName = "MN Packout Log " & Format(Now, "mm-dd-yy") & ".xlsm" sBasePath = "C:\General Use\UPS Daily Tracking\Logs\"...