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
I have browsed for two days looking for material on effecting this. The technique quoted most is watch for undolist for paste & auto fill then undo the change and paste the value again with specific formatting needs. This technique can be implemented in a worksheet_change event handler. The problem that I am facing is that the user can copy and past while my VBA is running. Once it is caught inside my own application class object event handler all that undo stack is already cleared. Therefore I have nothing that tells me if the user has entered the value via a paste action. By the time the value is pasted, all that formatting would have been entered. For example, a value that is interpreted by another workbook as DATE will have my destination formatting changed to DATE as well even though it is designed to be TEXT at design time.
So far I have not been able to think of a scheme to deal with this.
My basic intention is to always ensure the destination cell formatting remains as TEXT. If something is already interpreted as DATE after the paste even if I can convert to TEXT the string will appear totally differently. I have to find a way to paste the whole thing as TEXT in order to keep the string the same. The problem is that there is no intrinsic PASTE event in VBA. Without a method to undo that paste I cannot catch this at all.
I have two workbooks at the moment, one which holds a whole host of customer and accounts details and another which holds only the customer details. What I'm trying to do is cross reference the two to determine if a customers details appear on both and if they do highlight this, ultimately I'd like it to pull the customers account number back but even if it highlights a match that would be sufficient.
The only unique criteria I have to do this are customer surname and customer postcode.
In the Customer details spreadsheet I'm attempting to enter a formula which will look at the specific surname & postcode held on the row in which it is entered. It will then look at all of the rows on the other spreadsheet and determine whether any of them meet both criteria.
If they do I'd like it to display "match" if they don't I'd like it to display "no match"
My task is to combine two large databases into one spreadsheet by extending the number of columns. The data from each database is 90% matching based on an identification number, however occasionally there are additional rows or unmatched identification numbers that need to be kept for analysis.
When this happens, there needs to be a blank row inserted to represent the missing data in the rest of the corresponding row.
I am having trouble finding a quick way to do this because I have approximately 12,000 rows (and columns up to DV when combined).
for example:
p1 data data data data p2 data data data data
[Code] .....
needs to become:
p1 data data data data
[Code] .....
I am guessing I will need a macro of sorts, So far I have made one column that tell me if the ID's are matching or not (1 or 0) and if they are not matching (0) I manually insert the rest of the row that is missing or make space for the duplicate data (which needs to remain).
I have two tables, Table1 one has only customer codes in it, and I have Table2 with plenty of customer codes and those customer name, surname, age, location...
And I want to match and copy each of those customer codes in Table 1 all the information which is on Table2
Table 1: 50025 50026 50086
Table 2: Considering that name, age and location is each in separate cell
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 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 %.
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.
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.
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 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:
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'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.
i have selected cells from workbookA (b2:b8) , i want those selected cells gets pasted in workbookB in a transpose way.
like B1 cell in workbookA pasted into A2 cell in workbookB , B3 cell pasted into D2 , B4 cell pasted into B2 and B5 cell pasted into G2......
i have code but not working
Code:
Private Sub CommandButton1_Click() Dim STRFILENAME As String Dim O As Workbook Windows("Copy of Bill Schedule Form1.xlsm").Activate Sheets("Sheet1").Activate ActiveSheet.Range("b2:b8").Select
In column A I have the age of a sale i have completed (compared against todays date) and from B - N of the same row i have the data that coresponds to the sale. As Column A changes daily by 1 i would like to only keep the rows whereby column A is between 1 - 10. For any that are above 10 I would like to cut and paste to a second sheet, strangely entitled "older than 10 days" !
as there are formulas after column N which calculates the data i would only like to cut columns B - N of the specific row.
I think I need to create a macro that first sorts column A so I have the oldest data at the top and then a loop to check if these are above 10, i then want to cut from B - N and paste into another sheet.
