How can I have excel copy the entire row of data from worksheet Employee Inventory to another worksheet called EEs if column Q contains TERM. I've tried almost everything but nothing is working. Please help!
Assumptions for this code:
1. Employee Inventory sheet has heading in row one so it will start checking the data in row 2.
2. Column Q does not have any empty cells.
3. EEs sheet will have headings in row one so the data will start copying in row 2.
Set I = Sheets("Employee Inventory")
Set e = Sheets("EEs")
Dim d
Dim j
d = 1
j = 2
Do Until IsEmpty(i.Range("Q" & j))
If i.Range("Q" & j) = "TERM" Then
d = d + 1
e.Rows(d).Value = i.Rows(j).Value
End If
j = j + 1
Loop
Thanks! That is really helpful to me as well.
Just one more thing: How is it possible to copy data always on the fist empty row on the correspondant sheet (here EES sheet), if this sheet can contain altering number of rows time by time.
Does anybody know how to change the "TERM" query to a cell location on another sheet. Eg Sheet2!A1?
Sub CopyAlertDate()
Set i = Sheets("Sheet1")
Set e = Sheets("Sheet 2")
Dim d
Dim j
d = 1
j = 2
Do Until IsEmpty(i.Range("D" & j))
If i.Range("D" & j) = "TERM" Then
d = d + 1
e.Rows(d).Value = i.Rows(j).Value
End If
j = j + 1
Loop
End Sub
I'm having problems using it to copy and paste using more than one IF statement.
For example...
Set a = Sheets("Sheet1")
Set b = Sheets("Sheet2")
Set c = Sheets("Sheet3")
Dim x
Dim z
x = 1
z = 2
Do Until IsEmpty(a.Range("B" & z))
If a.Range("B" & z) = "TERM" Then
x = x + 1
b.Rows(x).Value = a.Rows(z).Value
Else
If a.Range("B" & z) = "ACTIVE" Then
x = x + 1
c.Rows(x).Value = a.Rows(z).Value
End If
End If
z = z + 1
Loop
End Sub
The code does work but doesn't remove the blank lines between the copied data - how do I remove these?
this is the quick of copying the values. if you want formatted text to be copies (not the format) then replace
e.Rows(d).Value = i.Rows(j).Value
by
e.Rows(d) = i.Rows(j).TEXT
If you want to copy both values and format, then you have to use copy and paste
I had the same problem as posted by Blossom83 on Oct. 14, 2008, that started this discussion. I solved it by applying the solution posted by Helper on Febr. 2, 2009 (thanks Helper!).
One problem remains for me, however: how can I, using Helper's solution, also copy the cell borders from the source file into the receiving file? Appreciate any suggestions.
What if I only need selected columns from a row to be copied next to each other, say columns B, D, E, G. What needs to change with the below?
Assumptions for this code:
1. Employee Inventory sheet has heading in row one so it will start checking the data in row 2.
2. Column Q does not have any empty cells.
3. EEs sheet will have headings in row one so the data will start copying in row 2.
Set I = Sheets("Employee Inventory")
Set e = Sheets("EEs")
Dim d
Dim j
d = 1
j = 2
Do Until IsEmpty(i.Range("Q" & j))
If i.Range("Q" & j) = "TERM" Then
d = d + 1
e.Rows(d).Value = i.Rows(j).Value
TERM is what the person asking the original question wanted to use as their specific condition.
Anything from worksheet 1 that had a cell with TERM written in it was to be transferred to worksheet 2
How to code macro on the basis of status. For Eg. in Sheet one there is status of Yes & No in Column B and students name in column A. Now i want that names of student should be automatically populated in sheet 2 for names on the basis of yes. If any changes is made in sheet one for status yes and name should should be populated in sheet 2.
I have moved a load of excel files from an old laptop to a new laptop and have lost the macro. on one spreadsheet each line comprised a company name with invoice breakdown for VAT, and then by left clicking on the cell containing the company name and then pressing control and L it would copy and paste the line into another spreadsheet which was a ledger card for that company. the company ledger would then add the totals of the invoices and show the total owed to that company.
path to open vat file :-
Libraries - Documents - vat files - vat file Acorn (business name)
Left clicking on the company invoice name being entered and Pressing Control + L
transferred ( presumably by copy and paste ) all the cells from A to K were transferred to the relevant company ledger card.
Path to open company ledger :-
Libraries - Documents - Excel - Ledger cards -company name ledger.
The original spreadsheets on the old computer were on excel 2003 and the new laptop is on excel 2010, but I believe files can be saved on the new laptop in excel 2003.
Can anybody write a Macro to do this transfer from one page of excel to another please.The old macro comprised about 6 or 7 lines.
I am after some help. A bit new to Excel and only have basic skills. I am currently setting a database up and need the data on my main worksheet (Data Tab) to automatically fill out the other works sheets. I Need it to copy across based on the week number the data was entered. The format of all the sheets are the same.
The Week Number is in column A. and I have got data relating to that entry in Columns B to I.
Sheet Names I want to copy to our Week 44 right through to Week 52
e.Rows(d).Value = i.Rows(j).Value
Sub CopyAlertDate() Set i = Sheets("Sheet1") Set e = Sheets("Sheet 2") Dim d Dim j d = 1 j = 2 Do Until IsEmpty(i.Range("D" & j)) If i.Range("D" & j) = "TERM" Then d = d + 1 e.Rows(d).Value = i.Rows(j).Value End If j = j + 1 Loop End SubThanks
My requirement is same. instead of 'TERM' i need to use a column value. could you please let me know how to do this?