Copy Data From One Worksheet To The Alternate Line In Another Worksheet
Jun 19, 2013
I would like to copy the data from one worksheet to the alternate row in another worksheet as follows. I've attached a file showing the source file and the desired output.
1. Copy column B's data in worksheet "Working" to worksheet "Upload file" Column V. B2's data goes to V1, B3's data goes to V3, B3's data goes to V5, so on and so forth.
2. Copy column H's data in worksheet "Working" to worksheet "Upload file" Column F. H2's data goes to F1, H3's data goes to F3, H3's data goes to F5, so on and so forth.
The number of rows with data in worksheet "Working" varies. It could be 30 lines one time and over 100 lines another time.
View 2 Replies
ADVERTISEMENT
Sep 11, 2013
When the worksheet is changed;
-For each row between A3 and A5000
-If cell in column L is empty
-Do Nothing and move on to next row
-Otherwise if cell in column L is not empty
-Copy entire row to alternate sheet, and delete row from original sheet.
-After all rows between A3 and A5000 have been checked, sort alternate sheet in ascending order based on the contents of column A.
The issue is that the code keeps skipping some rows that should be copied, possibly due to the 'for each' command not liking how I'm deleting rows (maybe?)
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
Dim lRow As Long
lRow = Range("A3:A5000").Rows.Count
[Code] .....
View 2 Replies
View Related
Apr 13, 2009
look for a certain value in worksheet A and copy that row of data to Worksheet B.
However, it seems to be only copying the row in worksheet A and pasting it. Is there something that a noob VBA scripter has missed out?
PHP Private Sub GetInfo_Click()
Dim r As Long, LastRow As Long, Status As Integer
Dim Message As String, Title As String, Default As String, MyValue As String
Application.ScreenUpdating = False
MyValue = Range("A4").Value
Workbooks("invoice.xls").Worksheets("A").Activate
LastRow = Range("C65536").End(xlUp).Row
For r = LastRow To 1 Step -1
If Cells(r, 1).Value = MyValue Then
Rows(r).EntireRow.Copy
Workbooks("invoice.xls").Worksheets("B").Activate
Rows("8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Status = 1
Workbooks("invoice.xls").Worksheets("A").Activate
Rows(r).EntireRow.Delete
Exit For
End If
Next r
Application.ScreenUpdating = True
View 2 Replies
View Related
Feb 13, 2014
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?"
View 2 Replies
View Related
May 18, 2009
I have a workbook with ~80 sheets. One sheet is a summary sheet containing data from each of the other sheets. The formula to display the data is pretty easy, but I need a function to copy the formula but increment the worksheet reference in each cell.
For example:
the formula in one cell is ='17'!$AI$6
The next cell should be ='18'!$AI$6
and so on...
I have about 12 columns like this, so I really don't want to edit each of them individually.
View 5 Replies
View Related
Apr 21, 2012
I have a workbook that contains 50 worksheets named 1-50. I need to add more worksheets. all the formulas in the worksheets always refers to the previous worksheet.
How can i make a copy of the worksheet named 50, name it 51 and have all the formulas in worksheet 51 refer back to worksheet 50?
View 1 Replies
View Related
Oct 24, 2011
I have embedded a worksheet called Rawdata" into a userform. Is has a number of text boxes on the userform, all the text boxes copy correctly to a worksheet called Rawdata, however i cant get the spreadsheet data to copy from the Userform to the sheet called Datapad.
The worksheet in the userform is from A1:G600, this data needs to be copied back to the Datapad worksheet B2:F601.
code so far:
Private Sub cmdadd_Click()
Dim iRow As Long
Dim ws As Worksheet
[Code]....
View 2 Replies
View Related
Jan 16, 2008
As I am managing a few projects at once, I would like to create a To Dos List for each project (seperated by different worksheets). To make my life a bit easier, I hope to show all the To Dos which is due on a particular day on a separate worksheet. In other words, I am hoping to write a macro which allows me to copy all the relevant To Dos (of that day) from different worksheets and compiled it into a single list on a new worksheet.
Note:
1) Each row of To Do contains 3 columns, Date, Priority and Descriptions.
2) The To Dos for each project are NOT arrange in sequence by dates.
3) Number of project will increase over time, therefore the number of worksheets will also increase.
View 3 Replies
View Related
Jan 23, 2007
I need to add a line on the top of a new worksheet with the data copied from another worksheets cells. Using a macro. The line has to be created everytime on the top of the new excel sheet with the previous data moving one down.
View 6 Replies
View Related
Apr 24, 2014
I'm trying to create a macro that will look at each worksheet in a workbook and then delete the last line of data on each worksheet. The last row can vary on each worksheet. This is what I have come up with but it is not working. I am on Excel 2010 and Windows 7.
Sub Macro1()
Macro1 Macro
Dim ws As Worksheet
[Code]....
View 2 Replies
View Related
Nov 22, 2006
I have written code that allows a user to copy, via a button, a certain sheet any number of times. They can choose to copy that sheet 5 times and then 10 times, etc.
When the sheet tries to be copied for the 17th time, it fails with the following message: "Run-time error '1004':
Method 'Copy' of object '_Worksheet' failed"
Here is the code that I have. The second line is the line that is failing.
Worksheets("RoedForm").Select
Worksheets("RoedForm").Copy After:=Worksheets(iCount)
This always fails on the 17th copy regardless of how many different combinations of copy sheets the user tries. There are several sheets before the sheet that is to be copied and I have deleted several of those to see what happens and it still fails on the 17th copy. I also have 1GB of memory, so I don't believe that memory is an issue.
View 3 Replies
View Related
Sep 24, 2012
I'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.
View 2 Replies
View Related
Dec 15, 2009
I'm a novice Excel 2007 user and appreciate all the help I can get. I have a workbook with monthly worksheets in it. When a certain data Type is selected from a drop down menu in that monthly worksheet than I would like to have it automatically enter specific data (Name, Date, Eval, Type) copied to another worksheet (CC) in the same workbook. I have been manually entering the data so far. Another thing, some of the data will be entered into the Monthly worksheets and some will only be manually entered into the CC worksheet so it would need to accomodate both methods of data entry. Please let me know if I need to clarify. I have attached the workbood, too.
View 11 Replies
View Related
Dec 11, 2008
I would like to copy and paste two columns from a worksheet to another worksheet simultaneously. I would like to have a macro to do this function.
View 5 Replies
View Related
Feb 26, 2010
I have an Excel WorkBook with 100 WorkSheets.
Each Worksheet has a unique identifying label - "Requirement Number"
Within each worksheet is free form text data of the following categories:
Requirement: 10358
Title: Customer requirement 1
Text: This describes the requirements for a product for the customer[code].....
The text of the categories may begin in column A or B
What I need is a macro that will search each worksheet for a category, e.g., "Configuration:", copy the row where the keyword "Configuration" is found, and then have that row transposed and pasted to another WorkSheet (e.g., "Extracted Data") cell.
Data extracted from the next Worksheet would begin a new row in "Extracted Data"
Example:
Requirement1 Title Text Verification Method-Level ...
Requirement2 Title Text Verification Method-Level ...
Requirement3 Title Text Verification Method-Level ...
The Requirement# is best extracted from the WorkSheet tab since some of the worksheets are missing this information.
I can provide an example spreadsheet, however, I was not able to figure out how do that in this post.
I found a thread similar to this problem:
find and copy row
However, it only finds, copies and pastes for one keyword.
View 9 Replies
View Related
May 12, 2008
What I am trying to do is to write a macro that will automatically copy six columns from worksheet (Sheet 1) to another worksheet (Sheet 2). i.e. ‘Description of Project’, ‘WBS Code’, ‘Rate’, ‘Employee Name’, ‘Premium’, ‘Invoice’, ‘Status’, ‘Total Cumulative Hours’, ‘Total Cumulative Amount’ from Worksheet (from Sheet 1 to Sheet 2)
The problem arises as I know the names of the columns to be copied in Sheet 1 (as details above) but they can be in any order in sheet 1.
In additional the columns ‘Total Cumulative Hours’, ‘Total Cumulative Amount’ are total columns so when they are copied from ‘Sheet 1’ to ‘Sheet 2’ their values should be copied as opposed to the formulas
View 9 Replies
View Related
Feb 26, 2010
I am looking for a solution to an otherwise very tedious problem.
I have an Excel WorkBook with 100 WorkSheets.
Each Worksheet has a unique identifying label - "Requirement Number"
Within each worksheet is free form text data of the following categories: ...
View 9 Replies
View Related
Jun 22, 2006
I would like to be able to select several non-sequential rows in a worksheet called "Data" (using a check box or just entering a value in Column A) and then be able to press a Command button to copy the selected rows to another worksheet called "Estimate" at the bottom of a table, and delete the designators in Column A (i.e. deletes the value, or unchecks the boxes) so I can repeat the process again if needed.
View 7 Replies
View Related
Jul 26, 2009
I have a worksheet that utilizes a Worksheet Change Event. I created a macro to copy the sheet and that works fine except for the fact that it doesn't contain the Worksheet Change event.
View 5 Replies
View Related
Jan 31, 2014
I need to loop through worksheets in a workbook and copy every first cell value(A1) and then paste into a new worksheet.
I have tried various loops. some have copied first value for the first sheet and then pasted in the new sheet. while others have been not so good.
This is the code I have so far and this does not work at all.
Code:
Sub Check()
Dim ws As Worksheet
Dim lr As Long
Dim treg As Worksheet
[Code]......
View 1 Replies
View Related
Sep 11, 2013
I have a worksheet(named A) where in some cases an additional WS needs to be filled out with some of the same data. I have a button that opens this new Worksheet (named B) and in the fields that are the same I use the formula,=IF(ISBLANK(A!D10),"",A!D10), to copy the data from WS A to WS B.
The problem is that these worksheets are copies of originals so that the orignal never gets altered. So when you go to import the data the WS that is opened is A (2) and B (2) and as a result nothing imports because the formula is looking at WS A not A (2).
Is there a way to make this work so that worksheet A (2) will go to B (2), A (3) will go to B (3),...
View 9 Replies
View Related
Aug 27, 2008
I have a worksheet with column a-e. Column e has data that says it is either closed or open. I was wanting to run a macro that would move the entire row that has the work closed in column e# into another worksheet also named closed. Please see the below for example;
A B C D E
1 # # # # closed
2 # # # # open
So basically I would like to move the data from row 1 into a the worksheet name closed.
View 9 Replies
View Related
Feb 15, 2007
What i've got is a spreadsheet that has a number of two worksheets one called premiership and another called stats, what i do is get data from 2 different websites and put that on stats worksheet by importing it.
Now this where it gets complicated, in stats when the data is download i wont to copy three cells to a specific location in the premiership worksheet. So in the stats worksheet, whats in column A and E needs to match whats in B and C in premiership and then copy information from cells in B,C,D in stats to cells in premiership G, H, I. AT this moment in time i do it manually, can this be done?
View 6 Replies
View Related
Nov 9, 2013
What I'm trying to do is to copy my data to another work sheet but with the different format like column to row..
item CODE
item DESCRIPTION
Cost
store 1
store2
store3
store4
store5
store6
store7
store8
[Code] .....
It includes formatting of column into row (store name,qty)
Store Name
Item Code
Item Description
Quantity
Price
Total Amount
Automatic it has total amount
store1
0003
item1
3
46.80
140.4
It will skip the column that has no value(empty cell) and will not transfer it into row
store5
0003
item1
2
46.80
93.6
[Code] .....
View 9 Replies
View Related
May 30, 2009
in my workbook i have 13 sheets, in one of the rows at the top of each sheet is a spot to put a name.
i would like to only have to put in the row on the first sheet and have it add the input to all sheets in the same row. how do i do that?
View 3 Replies
View Related
Aug 14, 2013
This is what I have now. YTD_annual_interest earned_3.xlsm
When you click the "update Workbook" button, it creates new tabs using the names in Column A5 through A29. If you look at the tabs named John Jones and Phil Williams, you see what I want to happen once the tabs are named. If you look at Mary Smythe, and the rest of the sheets, this is what actually happens.
My question is two fold.
1. Can the sane button be used to grab and copy the data in the corresponding rows as the named tabs? If no, how can that be done?
2. I want to populate the new sheets with the data as in the John Jones, and Phil Williams sheets automatically.
View 13 Replies
View Related
Jan 26, 2008
I have two workbooks: Workbook1 has two sheets – Cash Register and Suppliers; and Workbook2 has one sheet – Clients. The users of Workbook2 do not have access to Workbook1, and information entered in Workbook2 is required (and has to be manually re-entered) in Workbook1.
I tried IFs and VLOOKUPs to pull the data, but due to the large number of cells, the workbook is awfully slow – taking a very long time to save (even with Automatic Calculation/Recalculate before save turned off).
I am using Excel 2007.
This is what I am trying to achieve:
When a user enters an ID number, which begins with either “S” (e.g., SB-00010) or “P” (e.g., PADA-012-034-0567), in column E of Cash Register:
(1)
If that ID number begins with “S”, I want the code to look up that ID number in column A of Suppliers and copy the corresponding cell from each of the following columns:
B
C
D
E
F
G...........
View 14 Replies
View Related
Feb 23, 2008
I have a macro that copies selected data from various cells in WS1 to WS2's next open row, using offset to step to each new open cell in that row.
View 14 Replies
View Related
Jul 12, 2009
I have a listbox within a userform for example Listbox1. Together with other items from that userform I am trying to copy the data to a worksheet.
View 2 Replies
View Related
Dec 8, 2009
First of all I think I know a way how to do this, I just don't know whether it is the smartest way.
I've got data in cell A5 to cell ALastRow (for instance A40). Same for B5 to BLastRow, C5 to CLastRow. Well you get the picture.
This data must be copied to another worksheet if cell E or F contain data. If cells E and F are empty the data must not be copied.
My plan:
In the worksheet where the data must be copied to, I will pass a variable which indicates the LastRow. That way I know when I have to stop checking cells E and F.
I will write a loop which activates the source worksheet, checks cells E and F and, if neccesary, stores the content in variables. Then I will active the target worksheet and write the content to the appropriate cells.
The loop will keep going until it has reached the last row of the source worksheet.
Is this a good approach or is there a more efficient way to go about this?
View 6 Replies
View Related
