I got this code from someone who is not currently available,
I modified it a little so that when I pick a picture it always lands on j96, but I need it to line up exactly on top of the picture underneath it
I have also attached a sample.
The pictures will always be exactly the same size
I have a code here, which thanks to Jindon helps on yesterday, But now, I want to change the code to count by column instead of rows, to continuous copy & paste my date, How can it to modify:
Sub test() Dim home As Worksheet Dim Filename As String, myDir As String, fn As String Set home = ThisWorkbook.ActiveSheet With Application.FileDialog(msoFileDialogOpen) .AllowMultiSelect = False If .Show = -1 Then Filename = .SelectedItems(1) myDir = Left$(Filename, InStrRev(Filename, "")) fn = Mid$(Filename, InStrRev(Filename, "") + 1) With home.Cells(Rows.Count, "E").End(xlUp)(2).Resize(2) .Formula = "='" & myDir & "[" & fn & "]MAN_SUM'!k6" .Value = .Value End With End If End With End Sub
I need a VBA code for the Excel sheet that i am cuttently working in that will do the following:
i have text and pictures in range O86:W97 that must be automaticaly romoved & replaced by another range after the above mentioned code hase complete
the range that must be replaced with the following, O101:w112 Pdf code must run then O113:w124 pdf code must run then O125:w136 pdf code must run then O137:w148 pdf code must run then O149:w160 pdf code must run then
note that the ranges run in a sequence 101to112 then 113to124 then 125to136 and so on
Now this needs to be repeated 190 time starting from range O101:w112. The range O86:w97 forms part of my print area of A1:W97, this means i will have 190 PDF saved pdf sheets when i the vba code is complete.
I managed to do the combine the row if column B matches. However column D(quantity, number value), i want the quantity to add-up if column B matches. Any idea how do I modify the code below to do that?
for example: TDG-**002 Tuna Cheese Pizza Bar (KG) KG 30 TDG-**002 Tuna Cheese Pizza Bar (MG) MG 30 TDG-**002 Tuna Cheese Pizza Bar (KG) KG 30
will combine to become TDG-**002 Tuna Cheese Pizza Bar (KG) KG 60
I was trying to see if this code would work for my application where I want to compare two forms. Basically the master is filled out and then sent to a vendor. When it comes back from them rather than going through then entire thing and trying to determine if they changed or added anything manually I was hoping to use something like this code to do it.. I tried this code using a similar setup, basically a master file and an update file but it wouldn't actually populate the changes in the changes sheet on the master form..
I am using the following code, slightly modified, written by Tom Urtis.
I would like to enhance the code such that it will filter on two criteria to enable more specific results to obtained e.g. from the possibilities below I would like to get "Jones Manchester".
Data list:- Jones Blackburn Jones Liverpool Jones York Jones Manchester Jones London EC1 Jones London N12
Private Sub Worksheet_Change(ByVal Target As Range) With Target If .Address "$D$1" Or .Cells.Count > 1 Then Exit Sub ActiveSheet.AutoFilterMode = False If Len(.Text) > 0 Then Dim myVal$, myVal2$ myVal = .Value myVal = "*" & myVal & "*" If WorksheetFunction.CountIf(Columns(1), myVal) > 0 Then
I wanted to change a column text to call intial Caps. I found this macro code on the microsoft web site
Sub Proper_Case() ' Loop to cycle through each cell in the specified range. For Each x In Range("C1:C5") ' There is not a Proper function in Visual Basic for Applications. ' So, you must use the worksheet function in the following form: x.Value = Application.Proper(x.Value) Next End Sub
I need to change the macro to have a input box (asking what column to convert text to initial CAPS)
Private Sub CommandButton1_Click() Dim strFirstAddress As String With Sheet1.UsedRange Set rngFind = . Find(ComboBox1.Text, LookIn:=xlValues) If Not rngFind Is Nothing Then strFirstAddress = rngFind.Address Do rngFind.EntireRow.Copy sheet3. Range("a" & sheet3.Rows.Count).End(xlUp).Offset(1, 0) Set rngFind = .FindNext(rngFind) Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress End If End With Unload UserForm2 UserForm4.Show End Sub
The problem is it searches the whole sheet, what I want it to do is only search one colomn (colomn A). I know its prob an esay thing to change but I can't for the life of me do it!
I have quite a few Excel templates that need to be modified by changing/adding information or sheets to them. Instead of working through the lot manually, it would be good if I could do the same through code. With my little knowledge I can do this for workbooks. Need to typical code snippets or suggestions? e.g. to open a template file, save it with the original name, etc. The usual Dim srcWB as workbook, Set srcWB= ABC.xls and Workbooks(ABC.xls). open do not seem to work with template files (ABC.xlt)
I am trying to design a tool which dynamically results in different cells depending on users inputs. Yet, I am willing to protect most of the cells to avoid any user involuntarily (or voluntarily!) modifying the formulas/contents of the calculated (and locked) cells. Is there any way to protect my sheets, allowing only a certain macro to modify the locked and protected cells?
I have the code below set to change the background color based on certain conditions. What I need to do is change it so that if none of the conditions are met, it does not change the color at all. As it is written now, if anything is entered in a cell that has a background already set, but does not meet these conditions, the cell turns white.
I am using the following code to copy the contents of a DDE feed.
Code: Private Sub Worksheet_Calculate() Worksheets("Sheet2").Range("A1").EntireRow.Insert Shift:=xlDown Worksheets("Sheet1").Range("A3:F3").Copy Worksheets("Sheet2").Range("A1:F1").PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False End Sub
What I want to do is only copy the row when the columns contain data, as it stands at the moment it's copying blank rows to sheet 2. Is there a way that I can do this?
I found the code below and it works perfectly if I want to copy all the other sheets to a master sheet. But, I need to specify specific sheets. Basically I have a workbook consisting of multiple sheets and multiple "master" sheets so I need to specify in the code which sheets it should be copying.
I searched many sites and used codes which delete Rows based on criteria. In my case those codes works, BUT it took so much time about 30 min since there is about 75 thsd rows, and that solution in not time saver. I recorded code below and it is done in seconds. make it dynamic, VBA, since number of rows is every month larger. Basicly, I need code which delete rows based on Column B, where walue is "R"
Code: Sub DelRowsBasedOnOneCriteria() ' ' DelRowsBasedOnOneCriteria Macro
I'm working on a spreadsheet to compile and print checks. All the check information except the date and first check number is contained in a Wins sheet. I also have a Checks sheet which contains a master check. I used text boxes on the master check to contain individual check information. Each text box is filled in from the Wins sheet by means of formulas. The problem I'm having is how to modify the formula entries in the following code to cycle or loop through all checks required. A sample spreadsheet is attached.
I have used the following code behind a projects Command button for some time, and works well in Excel 2002/2003. It's used to look for and insert a Picture file, located on the users PC, select cell C2, re-size it to fit inside a bordered cell area and then nudge it over, off the border line.
Sub Load_Image() Dim oPict, PictObj Dim sImgFileFormat As String 'Open file GetPict: oPict = Application.GetOpenFilename("All Pictures (*.tif; *.bmp; *.jpg; *.gif; *.jpeg; *.png; *.cpt; *.tiff),*.tif; *.bmp; *.jpg; *.gif; *.jpeg; *.png; *.cpt; *.tiff") If oPict = False Then End Range("C2").Select Set PictObj = ActiveSheet.Pictures.Insert(oPict) With PictObj .ShapeRange.LockAspectRatio = msoFalse .ShapeRange.Width = 712# .ShapeRange.Height = 510# End With PictObj.Select With PictObj Selection.ShapeRange.IncrementLeft 1# Selection.ShapeRange.IncrementTop 1# End With Range("A1").Select
Unfortunately, Excel 2007 doesn't seem to identify the Cell reference "C2". It modifies the Picture size OK but does not position the picture in the correct position. I've tried re-recording it but 2007 misses most of actions.??? Despite my efforts looking on other forums, I don't seem to find a code that works on both version of Excel.
So far my VBA will copy my range of cells in Excel and paste them into Powerpoint but I'm totally stuck as to how to resize the image from there. I've tried a bunch of different methods and I get some pretty crazy results but can't seem to punch through to a solution. What I'd like it to do, at the end of the VBA is:
Set the lock aspect ratio to false Set the Height to 5.5" Set the Width to 9.83" Set the horizontal position to .08 from Top Left Set the Vertical position to .58 from Top Left
I cannot seem to get my save as portion to work at all. I commented it out at the bottom. This is all in Excel and PPT 2007.
Here is what I have so far:
Dim ppApp As PowerPoint.Application Dim ppSlide As PowerPoint.Slide On Error Resume Next Set ppApp = GetObject(, "PowerPoint.Application") On Error GoTo 0
Ultimately the behavior I'm looking for with this macro is to copy a filtered range of cells from Excel and paste it as a picture into Powerpoint (up til this point I'm golden) then resize the image on the slide, save the presentation, then exit PPT.
Code below. I need it to NOT run if the sheet week2 doesn't exist. Currently it gives a runtime error '9' out of range. This is due tot he sheet not being present because sometimes it is not generated.
Sub RemoveColWeek2sheet() Dim ColNo As Integer Dim rng As Range Set rng = ThisWorkbook.Sheets("Week2").UsedRange
I have a spreadsheet that has columns hidden from column Z to the end and rows hidden from row 50 to the end thus when viewing the spreadsheet the user sees the working area and hidden area in blue. What I would like to do is have the spreadsheet window minimize to the size of the spread sheet and also allow the user to drag the window around the screen. Is this possible if so do I need VBA Code.
I may be being a bit of a tool here, but I can't find a way to centre colums without selecting the columns (ie using with selection, centre, end with). I've written some variations below, none of which work.
if it was possible to create a shape, such as a rectangle, with a hollow centre so you can see the cell positioned behind it? E.G. the shape could cover cells B3:D5 so you could see cell C4 through the hollow centre/gap?
In Excel 2010, is there any way to paste a picture into a small picture/diagram box, and upon double clicking the picture it would explode to a larger size? And I guess double clicking it again would make it return to its original (smaller) size. I would be pasting several pictures into several different picture/diagram boxes and would need this to be a relative reference so that upon selection it explodes the appropriate picture?