Here is the situation:
I have a worksheet called ORDER where I have pallets and their contents: 1 barcode per pallet and 9 barcodes per box (9 boxes on a pallet).
Capture.JPG
On another worksheet called DESPATCH, I am trying to get a formula so when I enter the pallet barcode *p2* for instance in a cell. it automatically shows the 9 boxes details (without having to scan each box again).
Can I make my inventory item to a barcode and easily to be scanned, should I make it in Excel? Word? And how?
View 5 Replies View RelatedIm tryinig to set up a time sheet and need to have a barcode number that is scanned replaced with a name. Is there a way to look up the barcode scanned into a field (such as C3) and have that barcode number be replaced by a name?
View 1 Replies View RelatedI've been tackling this data capture/paste issue for a week or so. I found the string below which does provide a good foundation for my challenge. But, my basic level of understanding macros limits my modifications to meet my needs.
[URL] ......
I have 20 worksheets in my master file corresponding to Excel files individual associates will update weekly. After the associates have updated their individual files for the week, I want to capture the data entered and paste values into a master file containing a worksheet for each associate (sharing the same name as the individual associate file). All of these files are housed on team SharePoint sites.
I need a macro to perform several steps after clicking a "Run Update" macro button in the master file:
Open individual associate fileIn master file, search for each Initiative listed in column B (starting cell B3) in the individual associate file (in column B starting at cell B11)If Initiative is found in individual associate file, copy adjacent data in columns D:J for the respective rowIn master file, paste values to the corresponding Initiative row for the corresponding week's worth of dataIf Initiative is not found in the individual associate file, move to the next Initiative listed in the master fileRepeat these steps for each individual associate file
Linking would be the easiest way to accomplish this if I wanted to have a multitude of weekly individual files for the associates. However, I'd rather each associate have one file for them to update (basically overwriting their previous week's entries).
I need to ensure the paste values corresponds to the appropriate day of the week. In simpler terms, if the date in the individual associate file in cell D9 reads Oct 1, 2012, the data captured from that row needs to be pasted to the corresponding row/column in the master file that reads the same date.
I'm scanning data into a spreadsheet using a barcode scanner, but I want the starting character and trailing characters to be removed after I hit enter.
For example...let's say I'm scanning the following text in cell A1:
=W05281212345600
When I hit enter (or the barcode scanner does auto-enter), I want cell A1 to read:
W052812123456
I will always want the "=" and the last two zeros removed from the 16 character number, leaving the 13-character number in its place.
I tried using Excel's various truncate functions (LEFT,RIGHT,MID, etc.), but that only places the corrected text in another cell. That would be fine if I could copy that truncated text back over the original text...but that created a loop problem.
I have data in some of the cells within range A26:A39
These cells are populated via an IF function on another worksheet. Even though the cells appear blank (as in the value returned is ""), there is a formula in these cells. I think it's called formula blank?
I am looking for a way to copy the data from the cells within the range which are not blank (ie: not = "") and paste this data elsewhere on the sheet in a list with no blank spaces in between.
I anticipate that there will be 4 non blank cells within this range.
Ideally I would have data from the nonblank cells copied and pasted to cells
A40
A41
A42
A43
I am trying to create a spreadsheet that will have a lot if invoice totals (over 3000) can I scan these invoices and make them so they link to the cell the are refered to in the spreadsheet similar to a comment box?
View 4 Replies View RelatedI have an excel workbook that I have created to use in the creation of purchase orders. I have a spot for the vendor in cell D10 and my items are in the range of B17 to N17.
What I would like to have is a macro that I can assign to a button that does the following.
I have a worksheet called "Purchase Order" that has all the information in it.
When you click on a button named "Process", it will take everything entered into B17 - N28 and copy it to a worksheet named "PO# History" starting in cell B2 and moving down. Each time I click on "Process" I need the information in the range above to get entered into the next empty line on the "PO# History" worksheet.
This way I can maintain a list of items purchased and then play with them in a pivot table/graph and such.
Also, What I need it to do also is when process is clicked, the macro needs to pull the vendor name from cell D10 and put it in column A on "PO# History" for each row it adds from B17-N28.
I want a worksheet to have alot of data from another worksheet that keeps being updated.
View 3 Replies View RelatedI have data that I need to copy into a new worksheet, that is fine, no problem, however, the range of cells that I need to copy is different each month, and as I am collating several different sheets into one new sheet, I need to be able to select the active range, I.E, the cells that contain data. This month the range is A2:P56, however, next month could be A2:P123. Is there any way I can use VBA to select the active range? Sorry if this is explained badly, its difficult for me to type what I want to say!
View 3 Replies View RelatedI am trying to copy some cells from a worksheet and do a paste special to paste values at a different location.
But I am getting this error 400.
VB:
Sub CopyFeedResultData(inc As Integer, Feed_Fraction)
'
' CopyFeedResultData Macro
[Code].....
is it possible to copy from say (A1,A8,A9,A17) to (A1,A2,A3,A4) on another worksheet? Is their a formula for this?
View 3 Replies View RelatedI had originally posted this question on another thread since the problem i've been trying to solve is quite similar to the discussion on that thread.
I have a workbook with only one worksheet. This worksheet contains data in only the first column. However this data can be either tab or space delimited. The first few rows are junk data which i am not concerned with. I have a header and footer row and the useful cells are in between these.
What I am trying to solve is this:
1) Add a new worksheet, rename it to "COPY" and place it after the original sheet. This I have been able to achieve using the code below.
Dim PageSheet, CopySheet As Worksheet
Set PageSheet = ActiveSheet
PageSheet.Activate
Sheets.Add
Set CopySheet = ActiveSheet
CopySheet.Name = "COPY"
CopySheet.Select
CopySheet.Move After:=PageSheet ...............
I am looking for a visual basic script (macro) wherin I can copy fields from one sheet based on condition to main sheet. Sample sheet is enclosed as sample.xls. Here is what I intend to do: two tabs are there in sheet. master and work.
Need to check in "work" tab sheet if x name exists and if yes then copy three cells namely Input, Direct/.Indirect and code and paste all three cells in master sheet in front of X name wherever x name appears in resource name column.... Ultimately work sheet will be searched for all names which are in Master Tab and three field will be filled accordingly. Finally all the resources in resource Column will have three addditional field in mster sheet taken from work sheet.
I'm creating a simple program that copy one or more specific cell values and place it on a specific cell in another sheet using loop to make it easier... I'm having a difficult time trying to figure it out..
Example
from sheet1 A1:A5
Sheet1
A | B | C
1 P45
2 P46
3 P47
4 P48
5 P49
and place P45, P47 and P48 on another cell, to be specific in C1,C2 and C3, in a different sheet
Sheet2
A | B | C
1 P45
2 P47
3 P48
4
i would like the code that allows me to copy enything I typing in column a sheet1 to column a in sheet2
View 3 Replies View RelatedIam looking for macro to copy rows based on partial cell content of a column. I have an excel spreadsheet called "arc.xlsx" from which I would like to copy data to other few new excel files when certain criteria are met. The excel file contained location is C:Documents and SettingsxxxxDesktopCompany.
Below is a sample of arc.xlsx
GP CUST_NO BR CUST_NAME day mo year
I1 999999 1 SMITH 0 8 9
I1 999999 ab SMITH 4 8 9
I1 999999 cd SMITH 4 10 9
I1 999999 1 SMITH 4 1 10
[Code]...
I would like the macro to copy rows that have 'ab' in the column c (with title BR)and save it in a new excel file with name ab.xlsx in the same location folder.And the same for 'cd', '01' and '02' by saving the data in files with name cd.xlsx, 01.xlsx so on.
I need to copy certain values of multiple pivot tables of different worksheets into a new worksheet.
To be more clear I attached an example worksheet:
In "Sheet1_pivot table" and "Sheet2_pivot table" I have two pivot table with data divided by Area, Year, quarter and month.
In "Table" i have a summary table where, depending on the values of cell B2, B3 and B4, the summary table has different values copied from the 2 pivot tables.
I was thinking of a code like this but i have problems getting the values from the pivot tables
VB:
Sub else_if()
If Range("B2") = "North" And Range("B3") = "October" Then
ElseIf Range("B2") = "South" And Range("B3") = "October" Then
Else
.
.
.
.
Else
MsgBox ("Area not present")
End If
End Sub
Copy Ranges From all Sheets.xlsx
I need a macro that will copy a range of cells, it is always in the B2:B7 range from all the worksheets in a workbook. This is just a sample of the actual workbook, which has 100's of worksheets. The macro also needs to maintain any formatting [conditional or regular].that has been added to a particular cell. The ranges need to be pasted in a worksheet named "content" and arranged in columnar sequence, please see attached sample.
I have set up the attached workbook to try to show the results that I need using the matching shaded cells in the worksheets.
The first part of the problem is that all data in columns A, B, C & H (Input1) needs to be copied to columns A, B, C & F in Input2 with the addition of a Zero value in columns D & E (sample data lines 1 - 3).
The second part of the problem is that all data in columns A, B, & F (Input2) where the cell in column C is equal to ZERO needs to be copied to columns A, B & C in Input 3 (sample data Lines 4 - 11).
It would also be useful to be able to copy data in columns A, B, & F (Input2) where the cell in column C is NOT equal to ZERO to columns A, B & C in Input 3 (sample data Lines 12 - 25). However, there will be often be more than value in column C (Input2) dictated by the date in Column A and number in Column B. The value in Column F (Input2) will always be the same for each of those rows, so it does not need to be duplicated in Column C (Input3).
This would have to be manually triggered by linking it to a button or menu item, not sure what would be the best option. As data is entered at various times of the week, the routine would either have to overwrite existing matching data or carry on from where it last finished!
How to restrict copy paste for certain cells in excel?
For Example : Range ("A1:A20) and Range ("C5:C20")
I'm just trying to copy certain cells from one worksheet & add them to the next available row on the next sheet, my data is going right down to the end of the sheet though (row 1048554) & instead of placing them on the same row its placing them in the row underneath the previous data in the next column. My code is below & I've included a table at the very bottom of how the data is coming out.
Sub Copydata()
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Sheets("PO").Select
[Code]....
I am using the following macro which copy and paste certain info from one sheet to another. The macro I am using is
Code:
Sub test()
With Sheets("Invoice")
.Range("A4:C17").Copy
Sheets("Transactions").Range("b" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
End With
End Sub
Is it possible to copy the value of B2 in the sheet (Invoice) as well and paste it for as many entries there are in the rows 4:17 in the sheet "Transactions". For example say i have items in row 4:10 when it copies the info to the sheet Transactions then it must copy the entry that is in B2 on sheet Invoice also in column a 4:10.
Copy and paste each cell below when M is true or greater then 0 to a new worksheet called Price Summary. If cell is false or 0, do not copy. I would like the text in N to paste to the first available row in A and copy M as well to the corresponding B. I only need the values of these cells to copy and not the formulas. Been trying to get this to work for a while with no luck.
Excel 2007MN17$018$01920$021$022$023$024$025$026$027$028$029$030$031$032$033$034$0250# Vessel Pricing TESTWorksheet FormulasCellFormulaM17=IF(L17=TRUE,INDEX(_250lb12inlegs,B2),"$0")N17=IF(L17,"12 in.
[Code].....
I would like to copy several cell entries from one worksheet ("Entry") and paste then into a new line of another worksheet ("Contact DB") which will serve as a repository/database. Ideally, I would like to clear the values in the first "Entry" worksheet once this is done.
The thing that is throwing me off is that the data entered into my first "Entry" worksheet is not all on one row. However, I have recorded all the starting point references and destination cells (i.e. "B2" from "Entry" --> "A5" of "Contact DB") so it's ready to go into code.
Using the following code (which I tested for copying and pasting the first cell of interest) but I can't seem to get it to work:
Code:
Public Sub Macro1()
Application.CutCopyMode = False
Sheets("Entry").Range("B2").Copy
Sheets("Contact DB").Range("A5").Paste
End Sub
I'm brand new to vba so I'm struggling to understand the code and where it goes. That brings me to Q2 (and a dumb one it is). Where do I put this code? I already have one bit of code (for allowing multiple entries in a dropdown list) for the "Entry" worksheet under the "Microsoft Excel Objects" folder. Is this where this copy and paste could would go too? Or do I need to insert a "module?"
i have a workbook that has several worksheets. i may create new worksheets at any time. i want to create a macro that will select a certain field of cells and copy it to a random spreadsheet of my choosing. i know how to make a macro that copies cells and pastes them to a different worksheet, i just want the macro to ask me which worksheet i want it to copy to.
View 9 Replies View RelatedI have exported some raw data into excel. On another sheet in the same workbook I have a formatted sheet which will be my final report. I would like to know how I can copy data from one sheet to another via VBA code using the auto_open() function.
View 2 Replies View RelatedI'm currently working on a maintenance task sheet for a couple of generators. I have successfully created button macros to input dates. I would like a macro to copy all the cells that are RED (due to a condition rule) plus their associated fields to the left from worksheet "Schedule" to worksheet "Tasks Due". [URL]
Here's the pre-programming flow, I just don't know the code to do it:
Scan worksheet "Schedule" for red cells in following Range
H8:I16,H18:I22,H24:I35,H37:I41,H43:I49,H51:I59,H61:I72,H74:I75,H77:I84,H86:I89,H91:H100
If cell = red Then Copy Row A:I
Insert Row into sheet ("Tasks Due")
If red cell is in worksheet "Schedule" H8:I16 then insert copied cells in worksheet "Tasks Due" one below cell = Fuel System
If red cell is in worksheet "Schedule" H18:I22 then insert copied cells in worksheet "Tasks Due" one below cell = Lubrication System
[Code] .....
If my uploaded workbook doesn't work I've linked screenshots of my document.
Top of "Schedule" worksheet: [URL] .....
Middle of "Schedule" worksheet: [URL] .....
Bottom of "Schedule" worksheet: [URL] .....
"Tasks Due" worksheet: [URL] .....
Although Copy + Paste and Copy to New Row are fairly common questions I find, I am having trouble getting it to fit to what I want.
In the attached example, I have three sheets: 'TBC', 'Bland Anna' and 'Coates Beth'.
I would like to search in columns E and F (no dates will be present until row 5, but as rows 1 through 4 will not contain dates, it may be easier/involve less code to just search the columns) for a specific month, e.g. for this example search for any dates that are in January.
I would then like to copy the data in columns D through G that correlate to said date, and then paste onto sheet TBC on the next available row, not to overwrite.
The macro in question would need to go through every sheet and perform the above action, although there are only 2 sheets in this example, the live workbook will likely contain 100+ sheets containing employee sick leave.
Furthermore, and this is something I'm not sure would be feasible, everytime it copies data, I need it to pull across the name of the individual. This part is slightly more complicated as the names on the sick leave sheets are based at E2 and G2, whereas it needs to copy the names to B and C. Moving the name information from E2 and G2 would make sense to make copying easier, there are other sheets that I have excluded as they are not relevant here, but which are used elsewhere.
If this is not possible, I will have to look into reworking the layout of the information to make it feasible.
month copy test.xlsm
I want a macro which will copy all the data from sheet 1 in columns A:C ignoring any blanks
then paste it into the first free cell in column B of sheet 2