I am copying some data from one workbook and pasting the data into another workbook depending on certain criteria (using autofilter) using excel VBA. For only One column i need to multiply data by 100 (i.e. convert it into percentage) For example column A in source worksheet has all values and i will paste it in column D of target worksheet. But before pasting i need to multiply the data by 100 so column D in target worksheet has values in %.
It's merging the two sheets together. however, it's pasting over the last row of data. For example, gl download has data from row 4 to row 100. The Macro is pasting data from ap modified starting in row 100 of gl download instead of row 101. How can i correct this?
Sub C_Merge() Dim w1 As Worksheet, w2 As Worksheet Set w1 = Sheets("ap modified") Set w2 = Sheets("gl download") Dim lr1 As Long, lr2 As Long
I am trying to do what I thought was a simple look-up. On one sheet in colum A I have some text and in colum B I have a number. On a second sheet i have a list of text that may or may not match the text on the first sheet in colum A. If the text in colum A matches one of the text items in the list, I want to have the value in colum B appear next to it in colum C. I tried to modify a vlookup formula but would only get a value error if there was a match.
I have an excel sheet with a few buttons which open up separate user forms:
The first button "Add Exhibitor" works by bringing up a user form which enters data into the separate excel sheet called amends in the next available row, the data is initially selected by a stand number which if it already exists in the "amends" sheets brings up an error.
The second button "Modify Exhibitor" is what i am having the problem with. When the user clicks this button it brings up an identical form but for modification. What i need it to do is when the user selects the stand number in the drop down box: For example: H1-A-01, i need it to pull the data from the row in the "amends" data sheet which matches that stand number and put it into the user form so the user can modify and make changes, i would ideally like to be able to track these changes as well.
I am new to VBA and have tried many things so far online, i have managed to get the company name from the correct row and column to work but i cant figure out how i did it or how to make the rest of the data do the same?
The code i am using is below & an example of the form with data removed can be downloaded from here: [URL] ....
X Y Z Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep A B C
The above format is how I want my spreadsheet to look like. On another sheet within the workbook, I have a chart with some control options that allow me to vary X, Y, Z (I can vary either of these individually). Anytime I vary any one of X,Y,or Z, then the values in A,B,and C all change. This is what I am looking for, a Macro that will allow me the opportunity to everytime I change or vary an option (X,Y,Z), I can hit "Save Scenario" and the new values will be appended to my "Report" sheet. The values of A-C and X and Y are kept on a sheet entitled "FY 07". X is maintained on a sheet called "Inputs". So in all, X,Y, Z and A,B,C are all copied for each scenario.
I have a workbook with a database of 15 different products. We receive information with current prices for each of the 15 products on a weekly basis. This info comes in one single worksheet. I have created a different file with one worksheet for each product to track price behavior over time. I need to update these database everytime I get a new report. What I was trying to do is to develop a Macro to copy the data from a "Master" worksheet that includes all 15 products info to the respective worksheet for each product. Information is sorted in columns having the most recent date in the lowest column. I would need the Macro to copy the new data below the last price for each product. I would copy the data into the "MAster" worksheet, then run the Macro who would copy each row and paste it in its respective worksheet in the row rightafter the previous one.
I am using 'Dim' to define a string for SheetName, a variable.
I have tried with no success various ways to try and paste data to a sheet with this variable. I know the variable is correct as I am using it to make a new sheet then rename it and also move it.
I have a range that changes the data constantly, I have to watch that data changing. I am trying to work on a macro that copy that data and paste to another sheet. What would be the code to find the next empty row and paste my data there. Data is in Sheet1, range A17:E32... and it needs to be pasted in sheet2 starting in F2.
This code works great for me, but I need some help modifying it. This code searches for data in the whole sheet, two things I would like to do is limit the search range to one column and then to find the whole string, not partials, for example, find 331 without finding all the 33's or 31's.
Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range If Target.Address = "$A$1" Then Set MyRange = Cells.Find(what:=Target.Value, After:=Target) If Not MyRange Is Nothing Then MyRange.Select End If End Sub
I'm trying to implement a very simple macro to paste a set of cells - already copied to the clipboard from a CSV file - into a worksheet.
Basically, all I need it to do is to allow the user to open that other CSV file, hit CTRL+A and CTRL+C, then go to the relevant sheet in my workbook and run the macro, which should simply paste the copied data into that sheet (starting at cell A30).
Here's what I have so far - I keep getting an error when the macro reaches the ActiveSheet.Paste command.
VB: Sub UpdateData() ' ' UpdateData Macro ' Dim strSheetName As String
I am trying to secure a worksheet using data validation. I want to prevent users pasting over data validation fields, however I want to allow users to be able to still use the copy/paste feature as long as the data is valid.
Attached is a sample that I found on the forums. The current code does not allow any paste functionality.
I work in a correctional centre. Some of the correctional officers have little training to use Excel or other programs, but are required to complete spreadsheets on line. Although we have heavily protected the worksheets and locked cells, we find that in some cases the staff can still mess up the worksheets by copying and pasting data inappropriately.
Is there a way to turn off the ability to paste data into the worksheet, and to force each field to be completed manually? This would eliminate the problem.
I am trying to paste a single cell value to a range that will vary according to row counts in two different columns. I'm pasting in column B so I want to use .Range("B" & BRowCount) as my starting value. I then want to paste the value down column B until I reach the last used row in column C (i.e., .Range("B" & CRowCount)). None of the paste range code that I am trying seems to work.
Code: With CS BRowCount = CS.Range("B65536").End(xlUp).Row BRowCount = BRowCount + 1 .Range("B" & BRowCount).PasteSpecial Paste:=xlPasteValues .Range("B" & BRowCount).Copy CRowCount = CS.Range("C65536").End(xlUp).Row '---->Paste Code Inserted Here End With
to copy Word Page1 in excel sheet1 cell(J2), to copy Word Page2 in excel sheet1 cell(J3) to copy Word Page3 in excel sheet1 cell(J4) . . to copy Word Page10 in excel sheet1 cell(J11)
I am trying to create a macro that will take inputs entered into a range of cells in 1 worksheet and then paste it into another range on another sheet. The target range is selected based on a condition and once the values have been pasted then the target range should be locked.
I have been trying the following code but keep getting the Run-time error 1004 telling me that the PasteSpecial method of range class has failed. Can some one explain what I may be doing incorrectly?
Code:
Dim i As String i = Range("Month").Value
Select Case i
Case "January" Sheets("INPUT SHEET").Select Range("Input_In").Select Selection.Copy
I want to create a function called PasteGrid(SourceRange As Range, GridHeight As Integer, GridWidth As Integer)
What this will do is take a long single row or column of data and paste it into a grid with a defined GridHeight and GridWidth, I have came up with the below so far:
Code:
Function PasteGrid(SourceRange As Range, GridHeight As Integer, GridWidth As Integer) Dim rRange As Range Dim rCell As Range Dim DestinationRange As Range Dim GridWidthCount As Integer Dim GridHeightCount As Integer
[Code]....
Something appears wrong and when following the code when it hits this line:
i have data on the left; a Ref number.. A0, A1, A2 etc etc and each of these has an associated 'output'
What i need to do is have the ability to input random numbers (like in column E) then hit the macro and it put in the ref code next to it
so far i have the following which only goes as far as shown above.. it errors and doesn't complete the list
Sub find_paste() Dim output As Variant For counter = 5 To 10 Set output = Range("E" & counter) With Worksheets("data") Range("B5:B13").Select Selection.find(What:=output, After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate ActiveCell.Offset(rowoffset:=0, columnoffset:=-1).Copy output.Activate ActiveCell.Offset(rowoffset:=0, columnoffset:=1).PasteSpecial End With Next counter
When I've use the filter in excel, I can't seem to cut and paste/copy and paste the filtered data to another area. When I try to, it cuts/copys the entire data range, not the filtered one.
I have got a workbook with 20 or more worksheets. Every quarter I need to paste/append new data on these worksheets (which has already got existing data from previous quarters). All worksheets have different rows of data, for eg One worksheet may have 10rows, the other may have 50. Hence when I am writing my code, I cannot specify the destination cell as for eg A11, or A51.
How do I write up a VBA code that looks for the last row of data on these various worksheets and then pastes the new information on the next row(new row).
I was in the clear for a while, and now this error is back - even though I haven't changed my code. I cannot seem to get rid of it now. I have read the knowledge base articles on the MS site and still, no luck. I have the following code in a module. I am erroring whenever I try to paste (line 120). The error message is ErrNumber -2147417848:=Automation error The object invoked has disconnected from its clients....
I need a way to take the below example and combine each entry into 1 line.
ALRSItem Number/QuantityStockingDescription On HandUnit Actual Cost170PC1832PCB1un43.75circuit de PC1832 nouvelle version 5010 (P832)Avg. Unit Value:43.750000Costing Method: Moving AverageCosting Unit:un250282un2.05Braquette pour contact anti-vandale 112 ADEMCOAvg. Unit Value:1.025000Costing Method: Moving AverageCosting Unit:un
I would like to take each part and place the specific part information provided into 1 line for each part number.
It's refreshing to know that to make VBA even more lightning fast it doesn't check the For limiting variable. /sarcasm mode off
Does the following surprise anyone? Surprises me. (And yes, I know what an Exit For is. This code is designed solely to illustrate the point.)
Sub JimmyLoopVar() Dim i As Long, K As Long K = 6 For i = 0 To K If (i > 3) Then K = 2 Debug.Print i; ; K Next End Sub So the loop runs 7 times regardless of K! (At least on XL03 )
Every month I download a report from a state agency that I need to reformat, create charts from and send reminder emails based on some of the data. The report is downloaded as an EXCEL workbook containing a single worksheet. The worksheet is preformatted as a printed report. I have no control of this formatting and I can not get the data as a .csv file. The report contains 3 sections. See detailed report description below.
What I want to do: 1.Is copy the 3rd section of the report to a new worksheet. 2.Sort the new worksheet by name, and date. 3.Filter some names based on resource type. 4.Create a new file, which will be attached to an email to my boss for follow-up.
Originally I used the built-in EXCEL macro recording function and this worked for a couple of months. Then the number of rows in the 2nd section varied and my generated worksheet failed to sort correctly, and I lost the column headings so my filter no longer worked. The results were not something I could forward. I do not know how to modify the macro to search for the literals which end section 2 or start section 3 so I can copy the correct data to the new worksheet. I am not a VBA programmer, just an old-fart trying to do some administrative volunteer support for an important program, and really do not want to become one.
I am trying to modify the below code in order for it to:
1) Check the cells in Column A to find any blank cells and zero values. If the macro finds a blank cell or a zero then delete the entire row. 2) Check the cells in Column D for any blank cells and "N/A" (this N/A is text, it is not an excel error). If the macro finds a blank cell or "N/A" then delete the entire row.
Here is the old code
Sub DL() Dim lastrow As Long, i As Long With ActiveSheet On Error Resume Next .Columns(3).SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete On Error GoTo 0 lastrow = .Cells(Rows.Count, 3).End(xlUp).Row For i = lastrow To 1 Step -1 If .Range("C" & i).Value = 0 And .Range("D" & i).Value = 0 Then .Rows(i).Delete If .Range("C" & i).Value = "N/A" Then .Rows(i).Delete Next i End With End Sub Here is the modified code...
Sub DL() Dim lastrow As Long, i As Long With ActiveSheet On Error Resume Next .Columns(3).SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete On Error GoTo 0 lastrow = .Cells(Rows.Count, 3).End(xlUp).Row For i = lastrow To 1 Step -1 If .Range("A" & i).Value = 0 And .Range("A" & i).Value = "" Then .Rows(i).Delete If .Range("D" & i).Value = "" and .Rang("D" & i).Value ="N/A" Then .Rows(i).Delete Next i End With End Sub
1) Is this the correct and efficient way to accomplish the task?
2) How to i change this in order to select a specific sheet?
Basically my code works fine if I enter a number into the reference cell. But for my actual application, I need it to update the reference cell's background color when I enter other data. B21 has a =IF(COUNT(B18, B19, B20) < 3,"",B18 * B19 * B20) statement. So as I modify B18, B19, B20 it should update B21's background color. Here is the VBA:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B21")) Is Nothing Then If Target < Worksheets("OEE Data").Range("K6").Value Then Target.Interior.Color = vbRed ElseIf Target >= Worksheets("OEE Data").Range("K6") And Target < Worksheets("OEE Data").Range("L6") Then Target.Interior.Color = vbYellow ElseIf Target >= Worksheets("OEE Data").Range("L6") Then Target.Interior.Color = vbGreen Else Target.Interior.Color = vbWhite End If End If End Sub
Obviously my VBA isn't sound and hence won't apply any background to B21. If I just change it to AA1 and enter the criteria into AA1, it's background changes without problem. The reason I need this in VBA is because in the cFormatting I need the first condition to be 'is equal' ="" (white background) or it defaults my BLANK CELL to being > 1 (Green background). This is why I need 4 conditional statements.
I have imported text from a database that has names shown as "last name, first name" in a single cell and am trying to figure out if it is possible to modify those names to "first name last name." I would also like to see if I could just have the first name transferred to a different cell.
I'm trying to keep some basketball stats, and from ESPN the field goals are reported as 10-16, or 5-14, to indicate how many field goals were made out of attempts.
When I paste this into Excel it automatically interprets it as a date. So 10-16 turns into Oct-16, and 5-14 turns into May-14. How do I stop this?
My project requires working with 3 different files, in my example here: Delivery Status, Warehouse, and Clients. Delivery Status is the master file where the information is gathered at first. Then when the product arrives, from the column "AI" I choose the Destination which it could be a warehouse or client. So far the code that I have can paste the information in different sheets within the File Warehouse, but can not make the difference between the File warehouse and the file Clients.
In the Delivery Status file I can choose from the Column AI whether the destination is a warehouse or a client and I need Excel to paste the information in the proper file. Have in mind that I have a lot of sheets in both files. I am attaching an example files with the code.
