Copy Selective Data From One Worksheet To Another
Dec 9, 2011
I am trying to copy selective data from one worksheet to another. My intent is to copy data in column F below to another worksheet in Column H. I only need to copy numeric data in Column F. Below is the screen shot of my data set. There are 244 Batches and each batch contain 4 members. In another words, I want to copy data for 4 rows (F2 to F5) then skip three rows (F6 to F8), Then again copy 4 rows (F9 to F12) the skip 3 rows and so on.
Here is the sample data -
ABC DEFGHIJ
1
2JohnAccounts841.5
3JackReceivable842.5
4JimPayable842.7
5JustinClaims843.9
6Next lines of Data contain expenditure for batch 2
[Code] ..........
View 2 Replies
ADVERTISEMENT
Jun 8, 2014
I would need only the first row data to be copied into a master file in column A (row A from each file to column A, column B.... etc - basically transpose value into master file)
View 1 Replies
View Related
Jul 25, 2012
I have a spreadsheet which contains many rows (>3000) and a few columns.
I would like to make a macro that will copy specific cells depending on the number from another cell (from the first column) and then transpose the corresponding values. My current problem is that the values I'd like to copy (countries) are on a row (horizontal) and I need it on a column (below "Countries") next to the car models (in black: the current sheet, in red: my target):
Number of Countries
Cars
Countries
A
FJ
A
UK
[Code] .......
Is it be possible to create a macro that transposes automatically the countries from the right into the third column (like 90 degrees against the clockwise direction)?
View 5 Replies
View Related
Feb 11, 2013
I need the 9th to the 15th character in my cell to be my Sheet name.
This is what I have got but it is coming up an error. It will always be Cell A4 and will always be characters 9 to 15 (These are Numerical the rest of the cell is Letters)
ActiveSheet.Name = Mid(A4, 9, 6)
View 1 Replies
View Related
Nov 15, 2012
I'd like to grant access to selective columns on a shared worksheet.
For example:
Columns A to D should be edited by only me
Columns E to G can be edited by anyone
Column H should only be edited by someone else
The worksheet has to be shared and has been shared by me. I know how to protect & hide locked cells etc but the problem I'm running into is this...
After locking & password protecting say columns A to D and then sharing the worksheet, I am unable to edit my own columns (columns A to D) without first entering the password.
However in order to enter the password I need to unprotect the sheet. And in order to unprotect the sheet, I need to unshare it!
Is there any way around this or perhaps a completely different way of approaching it?
View 3 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
Jan 5, 2007
We want to transfer row data based on criteria in that row to another sheet. We only want to transfer certain cells in the row. We have a column that is named status that we want to trigger and identify the row for transfer and then its location in the new sheet. We tried vlookup but it would only take the first instance of a specific status in the column and not take subsequent instances. We then tried the index function but could not get it to work correctly. I hope this makes sense. There will be a high volume of data being inputted continuously so I don't think that copy and pasting is a viable solution....
View 9 Replies
View Related
Jan 17, 2013
lately I have promised to program a little macro which looked easy for me to do but on which I have now spend a considerable amount of time without finding the problem why it is not running. I am working under Windows XP with Excel 2010, here is what i was trying to do:
In a worksheet there are two columns G and H. In both columns it shall be possible to select a number of possible entries from a list. For column G this is done via data validation, no problem. Now, the choices which are there in column H shall depend on the entries made in the appropriate row of column G. Column G thus functions as an overarching category. If I choose "Tree" in G, H would give me branches, leaves, apples. If I choose "car" the choices would be wheel, engine, gasoline etc. This shall be done by a Macro:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cat1 As Range
Dim cat2 As Range
Dim x As Integer
Set cat1 = Range("G:G") 'Task Group
Set cat2 = Range("H:H") 'Task Category
[code].....
When running, it gives me Run time error 1004: Method "Range" of object '_Worksheet' failed. Debug highlights the row
Code:
With Range(Cells(ActiveCell.Row, 8))
View 2 Replies
View Related
Jul 30, 2007
I'm using Excel 2003 with WinXP SP2.
I'm trying to make a spreadsheet to calculate my GPA for my studies. I can get everything to work fine except I want to be able to calculate an adjusted GPA based on a certain value of classes to drop.
The way it would work ideally is that I would be able to search/sort a data set and if it contained "X" number of entries then the "Y" number of lowest marks would be omitted from the AGPA calculation.
I'm having trouble designing a function/formula or scheme to sort the data range, and identify the lowest "Y" number of entries and recalculate the GPA without using them.
View 9 Replies
View Related
Jul 21, 2013
How to selectively transpose a row of dates to columns. I'm not sure exactly how to explain this, so below is an example of what the data look like entered into the spreadsheet:
study ID
provider
visit 1
visit 2
visit 3
visit 4
visit 5
[Code]....
I'd like to extract the data into a new table on another worksheet that looks like this:
Date
provider
study id
visit #
7/21/13
Test Name
10001
[Code]...
This is just a quick example, but basically it would continue through all possible visit dates for the first study ID, then move to the next row of data (i.e. the next study ID) and extract the data from the row and transpose it in the appropriate columns moving down...
View 14 Replies
View Related
Oct 8, 2013
I have a database where I would like to plot graphs from data of different periods and different series.
My current method is to go to the data set formulas and changing data range. The tricky part is that I might skip data series because I am selecting the series required for each chart.
How to do plot the different graphs efficiently and quickly.
View 2 Replies
View Related
Aug 23, 2013
I have a spreadsheet listing various pieces of computer hardware, software and mobile accessories, etc with costs attached.
I have created a dropdown list of staff profiles eg a sales representative, finance assistant, IT administrator.
What I want to do is get Excel to pull the data through from the data sheet, to a printable summary output form (something you could also cut and paste from). For example, a sale rep would need to be set up with a laptop, laptop case, mobile phone, etc, so when you select Sales rep from droplist, I want that action to pull all the associated data through to the summary form. Similarly, a Finance assistant would require different kit , perhaps a desktop PC , monitor , etc.
Next to my data I have created columns with "Y" for yes in cells containing kit that match the staff role requirements, but I am not sure of the best way to pull this together.
View 3 Replies
View Related
Apr 1, 2009
I have a spreadsheet of courses required to reach a certification. On this spreadsheet I have listed the number of hours required for each course in one column, and how many hours I have accrued in an adjoining column. Not all the hours will occur at once, so I tend to bound from cell to cell adding hours in small amounts. What I am trying to do is create a macro that will allow me to add to the existing number of hours to the newly accrued hours, without typing over what is already there.
For example…Class 1 requires five hours total, and I have two hours accrued. If I accrue two more hours (for a total of four hours) I want to update cell E2 without going in to this cell manually and changing this number. I would like to enter the additional two hours in a text box or similar function, and have that function update E2. To add to the level of difficulty, there are four levels of class. This means not only do I need to be able to select which class hours need updated, but which level of class. I have attached the spreadsheet I am working with to try to make things a little clearer.
View 2 Replies
View Related
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
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
Oct 7, 2013
I have an excel WS1 set up as DB; I want to keep this sheet for data revision. WS2,3, 4, & 5 will be data that is filtered and sorted, using WS1 as source so I want to auto copy the WS1 data. Can I just auto copy WS1 (how do I do that?) then filter and sort in each WS?
View 3 Replies
View Related
Oct 18, 2013
I have been working on a macro that compares a existing list of data to an updated list of data and then either moves any data not on the new list over to a completed tab (followed by deleting the record on the existing sheet), and then adds any items not on the existing sheet, but which appear on the new list, to the existing list.
I have come across a stumbling block, i have managed to identify on the existing list the rows of data that have been removed from the new list and therefore need to be moved over to the completed tab, but when i select the data it selects the header row aswell (which will always remain the same row). Obviously this then pastes the header row aswell, and also i can't seem to get it to paste in the new sheet to the next available row (i.e this will be used daily and i don't won't to overwrite the infor already in the completed tab). the next issue i have is then when i go back to existing sheet to delete the data i just copied across, as the header was initially select this also gets deleted.
The code below, is the complete code, including filtering, copying some forumals etc. The area i am getting stuck on is highlighted in red:
Sub Update()
Dim bottomrow As Long
Dim My_Range As Range
bottomrow = Cells(Rows.Count, "C").End(xlUp).Row
Set My_Range = Range("A1:Y" & bottomrow)
[Code] .....
View 6 Replies
View Related
Oct 17, 2012
I have a workbook with 3 sheets
Sheet1 - TeamList - Column A is a list of all the team leaders which is used to create a new worksheet for every team. Col B is blank. Column C, D, E are the team lists where teams can consist of anywhere from 10-25 employees.
For example
Column A
Column B
Column C
Column D
Column E
Team Leaders
Team Leader
Employee
Pin
TL 1
TL 1
Emp 1
10001
[Code] ......
Sheet2 "RawHours" A raw data sheet that contains employee hours information
For Example
Employee #
PIN
NAME
Reg
OT
TRN
Stat
Sick
Vac
Berv
2000100
10001
Emp 1
24.00
0.00
[Code] .......
Sheet3 - Template
Basically what I want to be able to do is create a sheet for every team leader and copy and paste there team into the appropriate sheet.
I have the sheet create done by simply copying the template and renaming each sheet using the Team Leaders list from column A from the TeamList sheet. And then I copy the page setup information using the template as well but when it comes to pulling over the appropriate team members in to the appropriate sheet I can not seem to wrap my head around it.
Below is the current code I am using:
Code:
Option Explicit
Sub CreateSheet()
Dim LR, i As Long
Dim shCount As Integer
Dim Sh As Worksheet
Dim Cell As Range
Dim CpySht As PageSetup
[Code] ........
View 3 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
Dec 18, 2009
I have one worksheet called WEEKS.xls which lists weeks 1 to 8, week1 being columns A,B,C, week2 being D,E,F, and so on up to week 8
I have another work sheet called supplier.xls which is a worksheet imported from a database. In this worksheet I have columns A to Q. I am only interested in the data in columns C, P & Q.
I have buttons along the top in the WEEKS.xls sheet, one for eack week. On week one when the button is clicked I would like a macro that would look in folder “Supplier Stock” for the worksheet suppliers.xls and then take the data from columns C,P,Q and paste them into the WEEKS.xls in columns A,B,C. On week 2 the data will be pasted into D,E,F and so on. On the supplier.xls there is a few gaps down the sheet , like row 15 & 16 and alo 19 & 20 if possible I don’t want the gaps in the WEEKS.xls. Also on column P it is a date format I would like that pasted into the WEEKS.XLS in a dd/mm/yy format
I have added both sheets to show you what I mean
View 14 Replies
View Related
