I have data like in excel, which I uploaded and I need "simple" thing, If in column G is text Australia then copy from D column each cell which has "AUS1*" + copy the same number cell from C column and copy it into cell A and B. I hope I wrote it understandable
View 2 RepliesHere's what we need to do:
We are compiling one master spreedsheet from many smaller ones and I need to figure out how to copy them quickly and efficiently. For example:
Small Spreadsheet:
Column B is marked as P, M, C or S. This represents the type of the account.
Column C is the name of the account.
Master Spreadsheet:
We have a column for each account type (P,M,C,S)
Can we use a formula/macro to automatically copy columns B and C from the small sheet and organize them accordingly in the Master Spreadsheet?
For example:
Small spreadsheet:
P Account Name1
M Account Name 2
M Account Name3
C Account Name4
M Account Name5
would appear in the Master Spreadsheet as:
P M C
Account Name1 Account Name2
Account Name1 Account Name3 Account Name4
Account Name1 Account Name5
I'm not sure why this doesn't work on the dummy data?
Problems:It doesn't recognize the September 2011 datesIt is supposed to pull dates between July and August (i.e. 2 dates) and pull everything except those dates.
The date strings have to be variable based since it can't be hard coded.
Code:
Sub copyrow()
Dim rc As Integer, row As Integer, i As Integer
Dim mm As String, fdt As String, pdt As String, mo As String, yr As String
Dim Date1 As String, Date2 As String
mm = Month(Date) - 1
mo = Format(Now(), "mm") 'ex. "08"
yr = Format(Now(), "yyyy")
Date1 = mm & "/01/" & yr
Date2 = mo & "/01/" & yr
[code].....
I want to copy cells A&B where there is a value in G to a new workbook. I need to loop through the workbook as there are 25 sheets in the workbook, and there 8 workbooks to go through.
There are 31 rows on each sheet, and there are only a few values in column G on each sheet.
A is customer name, B is Account No, G is Insurance premium paid, columns C to F, and H to M have other accounting data in them.
The idea is to create a global list of all customers who have already paid their insurance premiums.
I am very new to Macro programming. I want to set up a macro which will copy data from few cells in a sheet and paste them as "Values" to certain specific locations in another sheet based on criteria. This copy paste needs to be done for about 40 different cells. I want to fix this with a button on the source sheet, so that this process happens when the button is clicked. I am attaching a sample file showing what I need.
View 2 Replies View RelatedI have an personnel file with employee info, and I want to create a macro that will look in the "Master" worksheet at the Department column ("I") for anyone in Benefits, and then copy their name from the Name column ("D") into the "Benefits" worksheet. The names should begin pasting in cell "D3" but will recognize if a cell already has a name in it and then paste in the cell below that.
Here is what I have:
Option Explicit
Public Sub RatingbyDept()
Dim Dept As Range
With Sheets("Master")
For Each Dept In .Range("I2:I1000")
With Dept
I keep getting error 92 - "For loop not initialized".
I am copying from cell A2 to C2, then A8 to C8, etc. if the cell in column "A" contains the text "Item" until the end of data in column A. Here is what I am attempting:
Sub MoveItem()
sText = "Item"
sText = LCase(sText)
iMax = Cells.SpecialCells(xlCellTypeLastCell).Row
For i1 = iMax To 1 Step -1
If InStr(1, LCase(Cells(i1, 1)), sText) <> 0 Then
Cells(i1, 1).Copy Destination:=Cells(i1, 3)
End If
Next i1
End Sub
The problem is the destination location. If I define it as a Range eg: Cells(i1, 1).Copy Destination:=Range("C2") The macro works, but does not iterate the destination cell.
I need to assign to variable the number of cells in axle "Y", I explain them, I have assigned to the variable "Celda" the number "4" that, celda = 4 where 4 is the number of cells where begin the information that i need to copy,at firts they were separated sheets, and the macro it was working, assigning a letter a the processed row in the column of the left, but now is a one sheet, and I need, this macro find the last cells that the contain a letter in the left and assign the number of cells to the variable "celda" dont the range for example "A33" else "33" and begin toward down, I have this
Workbooks(origen).Activate
Application.ScreenUpdating = False
celda = 4 'where the number 4 will be changed for the number of cells in axle "Y" in this case "33"
celda2 = 4
cdestino = 1
While Workbooks(origen).Worksheets(1).Cells(celda, 2).Value <> ""
dato = Cells(celda, 2).Value
Select Case dato
Case "Aplicación de material"............
how I can copy formulaes in cells B2:S2 down the row if the corresponding A-cell is not empty (contain a string)?
View 3 Replies View RelatedI have a worksheet that has data in columns a through d. Acct#, Name, Usage Code, and Other Code. Acct# and Name are self explanatory, usage code is a two letter code, and other code is a field I added to use with my macro. A specific account number could be repeated many times, but it is the usage code I am needing to work with here. My acct numbers are sorted in numerical order and column d has a 1 each time a new account number appears and a 2 if it is a repeat. I am wanting to copy the account number of any account that does not have a specific usage code listed at least once. That specific code is "ta". I am wanting it to only list the account number once in column f starting at row 2. Here is my code that is not working.
Sub test()
Dim CopyNum As Integer
Set CopyNum = 2
Dim Switch1 As Integer
Set Switch1 = 0
I am trying to copy a range of cells, based on a set of criteria that changes constantly.
In Column A, I have numerical values that are present every 10th cell. In columns B through D, I have various text data that corresponds to the number in Column A. So for example, in A50, I have the number 46975. In the range B50-D59, I have text data that goes with the number in A50.
Each day I run a few calculations and based on the number I get, I want to find the exact match in column A, then copy all the data in columns B through D that are related. So if my calculations result in 46975, I want to automatically copy over to another worksheet the range B50-D59.
I have been struggling to setup these two workbooks for a bit now, and I can't for the life of me figure out a formula to do what I need to do. Essentially, I have one workbook that contains a list of purchase records for my company, sortable by Date, Vendor, Price, etc. and one workbook that has a sheet for every vendor. What I need is a formula that will search column B for a vendor, Allied Waste for example, and transfer all the information within the rows for every instance that vendor is found to the new workbook.
This is basically just a way where I can input information once in one workbook, where the sheets are divided by month, and the info will automatically transfer to another workbook, where the sheets are divided by vendor.
a VBA code to do the following:
I have a worksheet consist of some 17,000 records - from ColA:ColDZ. In Datasheet, I have all values. From this, based on some values in ColA all records should be copied into another sheet(s).
I have attached a sample workbook in which I have explained the requirement.
6 worksheets ("Monday" ,"Tuesday" etc)
1 Worksheet (call it Main)
In the Main sheet let's say there are 3 column headings - Date, Inv No, Amount
I want to copy from the Main sheet to the appropriate day e.g if the dates in the main are13/01/10 and 14/01/10 then all the info pertaining to 13/01/10 should go to the Wednesday worksheet.
which I have modified from one that I use before for sheets 1 & 3 (instead of 4&5)
But it isn't working! I have defined a dynamic range in sheet 4, which is the range I want to search (its named "search") should i be using this range in the formula somewhere?
Private Sub CommandButton1_Click()
Dim rngFind As Range
Dim strFirstAddress As String
With Sheet4.UsedRange
Set rngFind = . Find(ComboBox1.Text, LookIn:=xlValues)
If Not rngFind Is Nothing Then
strFirstAddress = rngFind.Address
Do
rngFind.EntireRow.Copy Sheet5.Range("A" & Sheet5.Rows.Count).End(xlUp).Offset(0, 0)
Set rngFind = .FindNext(rngFind)
Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress
End If ..................
Data exists in a workbook's sheet name "0293" in columns A, B, C, & D. If a row's value in column B exceeds zero, the entire row & formatting needs to be added to the bottom of data in a sheet called "Comprehensive" of the same workbook and put 0293 in column E of the same row. What would be the simplest VBA code to copy sheet 0293's data & add it to the "Comprehensive" sheet with the sheet name in column E of the same row?
View 2 Replies View RelatedI am trying to loop through a column and cut and copy cells containing one of ten or more criteria (text strings) to the next worksheet, which already exists.
The basic loop is set up, and I understand that an array for the multiple criteria is needed but am falling at this hurdle, as well as struggling with the routine to copy the row to the next available row on the next worksheet.
I have omitted to post my effort so far, as it's embarrassingly basic, but can anyone help with the code?
I have a workbook that is used to track job estimates.
What I would like to have is a macro that would search through column N on the original file and if any of the cells have "No RFE" in them - generate a new workbook with only this list that would include any data from columns C-U copied from the original file.
I've been seaching the forum, but so far have had little luck finding something that suits my needs.
I am working on a spreadsheet for a shoe company. I have separate columns for the size, model, color, and item number of a shoe. I get everything except for the item number from a written document; I then have to find the item number for the shoe from another excell document called the Master List.
I was hoping there would be a way to have Excell auto-fill the item number for me. For example, if a shoe is a Red, Athens (the shoe model),size 12, its item number (which can be a pain to find) listed in the row of the Master List is aaabbb. So I want to just enter in the size, color and model number, and have Excell find the item number for me, and fill it in.
I have enclosed an example. Sheet 1 is the sheet I would be working on. Sheet 2 is a portion of the Item master list, which is actually 50k lines.
Sheet 2 contains a drop down list containing names, would like to assign a Macro to a button on same sheet that would refer to sheet 3 based on certain criteria.
Sheet 3 contains columns A - K of data. 2 criteria, data in column D and column K. Column D would be the same as names in the drop down list, whereas column K refers to a constant, either y or n.
Example: If D contains "Cheddar" and K contains "Y", copy and paste A:K to Sheet1, preferably starting at row 7.
I have data on a worksheet (“Interface”) that I need to copy over 7 other tabs for analysis.
("Module_1", "Module_2", "Module_3", "Module_4", "Module_5", "Module_6", "Module_7").
The linking Field on the Interface tab defines what row I need to paste the data to on Module 1-7. The column headers are the same on both the interface and other tabs (“YES, NO, N/A, BLANKS, STANDARDS MET, STANDARDS NOT MET, NOT APPLICABLE”)
The tab is selected based on the row
Module 1Medication Management
Module 2Privacy
Module 3Process
Module 4Patient Safety
Module 5Medical Records
Module 6Infection Control
Module 7Environment of Care
I’m trying to modify code written for a similar purpose with no luck. This is the code I'm trying to modify. I know I'm not on the right track yet.
I need a macro that will search data from a range of cells in one column for multiple criterias and them copy the entire rows to a new worksheet.
Example I have a list of group names
Network
Telcom
Help Desk
BA
Network
I only want to choose all Network and Telcom rows copy to another worksheet.
We're working with a large workbook with data from A5:P20000 on sheet1.
I need to be able to pull data from sheet1 to sheet2 based on the invoice number in column A on sheet2.
Sheet2 will have different invoice numbers in column A, some of these invoices numbers are in column H on sheet1. I need excel to find these invoice numbers and copy the data from that row on sheet2 to the row with that invoice number on column2.
Example:
Sheet2 A5 has invoice number 1234457
This same invoice number is located on sheet1 H3400
Need to copy to sheet2 (starting in cell B5) the following cells from sheet1 (B3400, C3400, E3400, G3400, H3400, I3400, J3400, K3400, M3400, N3400).
I have seen all kinds of copying routines, but haven't been able to get what I want.
1. Copy Row contents from activeworksheet
2. The activeworksheet will have various row counts
3. The activeworksheet has row 1 as header.
4. The criteria to use is in the current worksheet columns P through Z
5. The criteria to look for is the word inspect. only the word inspect, not words that contain inspect (i.e. "inspection") should not be found.
6. The word could be in column P and in S, but needs to be only copied once, becuase it has meet the criteria, but it also could only be in c
7. Copying the row into a Sheet named "Inspection"
As a bonus would be cool to be able to copy certain cells or the entire row.
Attach is a sample of my project. I have to loop through B9:B80 in sheet("Raw Data") and capture the value of each cell. EG:
* if Value captured is 120A --> check with sheet("SP14F") from the row which contains the same value.
* i have to check that the aperture, outer and inner values of Layer 120A and so on..of the Raw Data sheet is the same as that of in the SP14F sheet.
* if the values are not the same, change the font to red
i understand that this task requires looping and arrays.
I have a spreadsheet that is used to store statistical information on a monthly basis, some of this information may be incomplete and so will need to be acted upon month by month, What I need to do using VBA is: At the close of the spreadsheet check sheet1 and if there is any data (text) in cells N – Q on any line If there is move to sheet3 check for the next blank line then paste the data from only certain fields i.e. A,B, N-Q,R,S
Here is another problem
As the sheet will be used on and off during the month I don’t want data that is already been moved across to sheet3 to be copied again so there needs to be someway of checking if the info is already there? I have code now for the find last cell part of the problem.
Sub FindLastCell()
Dim LastCell As Range
With ActiveSheet
Set LastCell = .Cells(.Rows.Count, "A").End(xlUp)
If IsEmpty(LastCell) Then
'do nothing
Else
Set LastCell = LastCell.Offset(1, 0)
End If
End With
LastCell.Select
End Sub
I have 2 ranges: 1."Task info" (4x4 range)
2." Dates" (24x2 range)
I have one variable cell ref: "Date1" (a date)
Based on "Date1" I want to copy the value in the "task info" and Paste special value that is next to the corresponding date in "dates".
I have on sheet1 of my workbook a number of rows of data, from column A through Column G. The Data in column G either contains the text "Yes", or "No".
I need a macro that will look through each row in this sheet, and upon finding a "Yes" in the cell in the column G, will copy that entire row (columns A through G) into a new row in sheet2. Sheet2 currently contains data, so I'd like to move everything down by one row and paste this data at the top (row 6 is the first line of data on this sheet).
I'd then like to go back to sheet 1, and delete the row I've just copied. Then continue though the loop. The loop needs to stop when a blank row is discovered.
I'm not sure that I'll be able to explain this clearly, so I've attached an example. There are 3 columns - Issue, Action Group and Status. Many Issues belong to the same Action Group and others do not belong to any Action Group. The is also a Status summary section that has the total Issues and Actions associated with each status. For example the Status called Resolved could have 10 issues and 5 Actions (including blanks). The problem is that I can't figure out a way to get the total for the Actions. As I said this explanantion isn't great, so the attached sheet
View 3 Replies View RelatedI have multiple sheets with the same format.(all columns have same format) in column "A" i have a value lets say "B1-1".
What I need to do is to copy from all sheets the rows containing in column "A" the same criteria("B1-1") to a new sheet and skip the rows that have a blank cell in Column "A".