I am trying to figure out the code which will generate txt file from my active workbook with Encoding: Unicode. Do you have any ideas how to do so? I have chinese characters in XLSX file and I must keep them in txt.
DS_LOOKUIP_.xlsx - yes this is the file stored on network, ids and pn inside.
Part numbers can be duplicated but ID is always different. Vlookup is taking the first available ID. sorted: part number lowest to largest and ID largest to lowest.
File has more worksheets, related to different functions.
In destination file I want to have ID in column I.
In destination workbook part numbers can be also repeted multiple times.
1. My macro is prepareing internal file with amound bill of materials defined by user. Every BOM is in separated worksheet. I can have just one or more than one, even 1k.
2. In column B I have part number and I want to find related ID for that part number from file strored on network.
3. In the file stored on network I have worksheet called IDs with all part numbers and IDs which are related to them.
4. I am runing the macro which includes formulas from 1st post and the macro is checing IDs for all numbers.
5. The loop is between all worksheets.
6. The excel sometimes crashs in that vlookup step. Sometimes it is for worksheet 1 sometimes it is for worksheet 20, so 20 times it works byt suddenly it crashes. It is always the same vlookup function from 1st post but for different worksheet only.
Unfortunately I cannot attache the files because I have them in the office and I am writing from home. It the creshe happen then after PC reboot it works again. I am trying to figure out what is causing the problems.
Don't you have all this information in previous code? In general it works well but when I have one BOM in one worksheet and I have for example 100 BOMs - 100 worksheets then I run the code 100 times for each worksheet in entire workbook then it crashes excel, sometimes in BOM 10 (worksheet 10) sometimes for BOM 99 (worksheet 99), this is my problem
My formula is trying to find the ID for part number in the file I am using the macro. I have a BOM and part numbers in one file, in second file I which is on network I have part numbers and related IDs.
In first file I want to find IDs using vlookup.
I want to only know if I can somehow replace the current vlookup function byt something else (can be vlookup in different form). No more.
I have a code like below. The same code is runing several times in one macro, sometimes excel crashs during this step. It is only happen if I am using the excel for a long time and I have done many operations before. With fresh excel it always works I can do it even 100 times. I am wondering why? Can be that it is related to RAM usage in that moment. Is there any possibility to replace my code by something more stable?
I want to close all workbooks with sheet1 <> BOM. and sheet1.tab.color <>5296274 or 49407 or 49407.
I have been fighting with it for two days already. I can just met one condition, impossible to have it for both together. Could you please support?
Re: Count worksheets base on its colour
Thanks, it works great!!!
I'd like to count how many of my sheets in workbook has tab in green. I need this check just for visible sheets.
I have 5 sheets +1 hiden, two sheets are green and two white. I would like to display how may tabs is green and how many is white except hiden sheet.
Re: UNC path istead of network drive letter in Vlookup VBA
Is it possible to do the same for ChDrive?
Re: UNC path istead of network drive letter in Vlookup VBA
I have solved it, there was mistake in path.
I have following functionCode
How can I replace K: drive on UNC path. I have used the full drive name but every time when vlookup is staring Excel asked me to select the file. Can I avoid it somehow? I have created my tool for more than one user and Id like to avoid to have drive letter in path.
Is there any way to make icon in my command bar for command buttons in ribbon a little bit bigger? I am adding buttons using code below.Code
Private Sub CreateMenuBar() With Application.CommandBars.Add .name = "MY NAME" .Position = msoBarTop .Protection = msoBarNoProtection .Visible = True With .Controls.Add(Type:=msoControlButton) .OnAction = "multiple" .caption = "Download" .Style = msoButtonIconAndCaption .FaceId = 4165 .TooltipText = ""multiple" .Width = myIconWidth End With End With End Sub
I have two buttons in userform which are switching between sheets in workbook (except last one) It works pretty good but I can not jump from first sheet to one before last.
Any ideas for the code?
Re: Sheet data transfering VBA
Active is sheet1, if number in column B is the same then replace MISSING in rest of sheets by value from column C(active sheet).
I have multiple sheets. In each of this sheets structure is always the same.
In column B, starting from B12 to B.value=END
In column I is ID number or text MISSING (if ID is not available) related with number from column B.
In column E is weight.
In column K is supplier ID.
I need macro which will be able to transfer new IDs inserted manually in column ID for the rest sheets where Number from column B appears. The condition only is that weight and supplier have to be the same. Id like to have it for all sheets except the last one.
I have 3 sheets.
In sheet 1 I have data in range(B12 to B20 (END)). In sheet 2 I have data in range(B12 to B50 (END)). In sheet 3 I have data in range(B12 to B20 (END)).
Considered number is in sheet 1 in Cell B 14, and in sheet two in cells B16 and B11. In sheet 3 in cell 14 too. Next considered number is in sheet 2 in B14, and in sheet 3 in cell B15.
The issue is, when I replace value MISSING by numeric value in one of sheets (active sheet only) Id like to transfer the same value to the rest of sheets if weight and supplier and number are the same.
Active sheet should be always MASTER.
Could you support?
Re: Help with loop for in two ranges in two different sheets
I have replaced if with for, and it still doesn't work. In general nothing happens after running this code.