Code To Copy Contents Of DDE Feed - Modify Worksheet
Dec 14, 2011
I am using the following code to copy the contents of a DDE feed.
Private Sub Worksheet_Calculate()
Application.CutCopyMode = False
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?
What is the proper VBA code to copy the contents of column T and column W (beginning at row 2 and ending at the last nonempty cell) in sheet 1 to column D and column E in sheet 3? Column T and column W always contain the same number of nonempty cells.
I have a workbook with lets say 10 sheets. 9 of the 10 sheets are identical as far as layout, they just hold different information for different employees. I need to get a range of cells that are NOT empty and copy that data to the 1 different sheet (summary page). A6 through A25 is where the data is going to be, but all those cells may not be filled up, so I want to drop the empty cells from the range. So If only A6-A15 are filled, then those are the only ones that get copied. ALSO (dont you love that) I need to take that number range and have columns A, F and I as well. So if I have A6-A15, I will need the data out of F6-F15 as well.
I have a multiple sheet workbook designed to collect information which is all collated on the last sheet. On one sheet I have a textbox (not the drawing textbox) which is designed to take a string of characters of a specific lenght (150chars) and works well. I'm just trying to get the code to work with a textbox on the same sheet for now, then when its working change it to copy over to another sheet.
Sub TextBox_To_TextBox() Dim x As Integer Dim PreEmp As TextBox, PreEmp2 As TextBox Dim theText As String Set PreEmp = ActiveSheet.TextBoxes("PreEmp") Set PreEmp2 = ActiveSheet.TextBoxes("PreEmp2") For x = 1 To PreEmp.Characters.Count Step 150 theText = PreEmp.Characters(Start:=x, Length:=150).Text PreEmp2.Characters(Start:=x, Length:=150).Text = theText Next End Sub
I'm calling the sub routine but nothing seems to happen.
I have an Excel invoice set up and working well. It does a bunch of things with macros - e.g. on save it increments the invoice number well as creates a jpeg screenshot for the invoice archives. I have added an additional worksheet (titled 'VAT') to the workbook. The new 'VAT' worksheet has five simple columns; Invoice no, Subtotal, VAT, M.O.T. and Total.
What I need:- On saving the workbook I would like to add a macro function that copys the final contents of the Invoice no (H2), Subtotal (C37), VAT (C38), M.O.T. (F38) and Total (I38) cells from the 'Sales Invoice' worksheet to the newly created 'VAT' worksheet in the respective columns. I would like this to be cumulative, i.e. continue to add the contents of the afore mentioned cells to the appropriate columns in the 'VAT' worksheet every time the invoice is saved. I would also like to have the Subtotal, VAT, MOT and Total columns summed and outputted in a cell of their own - but hopefully I can handle that.
I have a list in Excel, and it has the company in one column, and it's information in the next x rows until there is a blank row (4-7 rows). I get that you can copy the rows under the Company, transpose next to the company, and then delete the contents of the cells that you just took the data from, but I have 6200 rows do to this to, for 500+ companies. I tried recording a macro for two entries, and this is what I have (see below), but how do I modify this so that I don't need to type in every single range, and it will do it to the whole column? The data is in column A, I am posting in column B.
Basics for Macro Requirement:
1. Find the blank cell in row A 2. Skip the next cell/row (this is the company) 3. Select all the cells beneath the company cell, until it reaches the blank cell underneath 4. Copy, transpose these cells next to the Company cell (transpose in column B) 5. Delete the contents in row A that were just transposed 6. Find the next cell with data (company) 7. Repeat Steps 2-6
I have data beginning in cell A6 and continues through cell A13 for this instance. I want data in cell A6 on sheet 1 to be copied to cell B1 in sheet 2; data in cell A7 sheet 1 to be copid to cell C1 sheet2 and so on till all of the data in the continuous range beginning in cell A6 of sheet 1 has been copied to row 1 beginning in cell B1 of sheet2. This seams relatively easy but below is my failed attempt at this.
Code: Sheets("Data Request").Select Dim DATAREQUEST As Long, TYPESRws As Range TYPES = WorksheetFunction.Max(Range("A" & Rows.Count).End(xlUp).Row) DATAREQUEST = Range("A6", "A" & TYPES).Rows.Count Sheets("Data Retrieval").Select For i = Range("B1", "1" & DATAREQUEST).Columns.Count To 1 Step -1 Sheets("Data Retrieval").Select [B1] = i ActiveCell.Offset(1, 0).Select Next i
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 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 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!
When I hit the print button the worksheet prints on the paper in the bin. However, there are times when I need to print the worksheet on different paper which requires me to go into the properties and change the paper source from Automatically Select to Manual Feed. I have been trying to created a macro what will switch to Manual Feed, print the worksheet and then switch back to Automatically Select but have been unsucessfull.
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 trying to make a VBA that will find a certain value (BELCS), select the value, and copy it to another worksheet. this is what i have so far:
For Each BELCS In ActiveSheet. Range("B2:B" & ActiveSheet.Range("B51161").End(xlUp).Row) If BELCS.Value = "BELCS" Then BELCS.Select Selection.Copy Sheets("BELCS").Select ActiveSheet.Paste End If Next BELCS
i am trying to get it to select the whole row when it finds that value but when it runs it says that i can't make a selection of the BELCS,
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