Copy Data Range Blocks

Jan 8, 2008

I would like to copy specific cells (all in one block) to another worksheet, then do a bunch of other things with the data (these steps work already), then go back to the original worksheet and copy the next block of data, repeat about 50 times, then stop at the end of the list.

I can't attach a file as I can't get it under the file size limit but the rows to move (just to any worksheet in the workbook, from there I can manipulate and move as required) each time are row 1 plus all those pertaining to each subject group. So for group 1 it would be row 1 plus rows 6 to 28 - for group 2 row 1 plus rows 29 to 51 - group 3 row 1 plus 52 to 74 etc. If I can just get looping through chunks of data and moving them around under my belt

View 5 Replies

Picking Blocks Of Data To Copy From One Sheet To Another

Jul 22, 2014

I have a series of spreadsheets that represent the rosters of teams in a league. At the end of the league season, some teams qualify to advance to a final event, and some do not. I start with a file of all the teams for that level of competition. This list comes from a program that creates it's output in an excel '97-2003 format (and I can't change the way the data comes out of this program). I modify the data a bit by removing data from merged cells and rename that worksheet a 'Raw Data'. In the past I have manually copied and pasted the data for the teams that advance to a sheet called 'Roster Data'. I am hoping there is a way to automate this part of the chore.

There must be 6 blank lines between the team names on the 'Roster Data' tab, but some teams have more members than others, so they are not spaced the same on the 'raw data' tab. Is there a way to select teams from the 'Raw Data' tab and have only the selected teams show on the 'Roster data' tab, AND have the spacing become uniform?

View 13 Replies View Related

Sort Range Data Blocks

May 9, 2008

I have a list of data (as shown below under Input), which I would like to sort them according to their block number (as shown below under Output). The hurdle is the sorting should be able to cater for missing fields. Also, note that the sector can be recurring, that is "P,P,Q,Q", and then follow by "P,P,Q,Q" again.

INPUT
Block No.SectorData
11P19
22P
33P39
41P18
52P21
63P38
75P22
85P24..............................

View 2 Replies View Related

Sort Records Within Data/Range Blocks

Apr 19, 2008

I have around 50,000 records in a data file within which groups of records are contained in blocks delineated by date/time. e.g.;

A B C D E

10/03/2008 13:15 a 100.0
10/03/2008 13:15 b 2.8
10/03/2008 13:15 c 50.5
10/03/2008 13:15 d 8.6
10/03/2008 13:15 e 32.5
10/03/2008 13:25 75.2 <-----Time change
10/03/2008 13:25 5.5
10/03/2008 13:25 16.5
10/03/2008 13:25 3.5

etc

As you can see, while the file is ordered by date/time in Column A, the values in column E are randomly ordered.

My objective is to maintain date time order but sort the values in Column E into an ascending order within each block. So, for example, using the details above I would want it to look like;

A B C D E

10/03/2008 13:15 b 2.8
10/03/2008 13:15 d 8.6
10/03/2008 13:15 e 32.5
10/03/2008 13:15 c 50.5
10/03/2008 13:15 a 100.0
10/03/2008 13:25 3.5 <-----Time change
10/03/2008 13:25 5.5
10/03/2008 13:25 16.5
10/03/2008 13:25 75.2

Note that there is data in columns B to D which would need to be sorted in line with Column E. So, all records in the block, cols A to E are sorted to give the above order for each time block.

My problem is that I'm not sure whether a basic sort can do the job or whether a macro is needed. I'm using Excel 2003.

View 4 Replies View Related

How To Copy Blocks Of Formulas Or Conditional Formatting

Sep 11, 2007

Lets say I have a block of cells (lets say A1:E5) that all reference the value in the upper left hand cell (A1). Could be a formula by itself or one embedded within a conditional format. I reproduced the formula and formats in the A1:E5 block by using the absolute cell reference $A$1.

I now want to reproduce that entire block (including the referenced cell A1) multiple times on the sheet, but the catch is that each new block must reference the cell in IT'S OWN upper left hand corner. (e.g. a block located at F10:J15 must use as it's reference F10, not A1).

How can I quickly reproduce those blocks? I have done a search and replace to eliminate the $ symbols within the formulas to make them relative, but that doesn't seem to work within conditional formats.

The issue right now is I am trying to apply conditional formats to a sheet I already constructed. I don't want to overight the formulas in the UL corner, so I am trying to copy and paste the conditional formatting only. Can't seem to figure out how.

View 9 Replies View Related

VBA Macro To Copy Blocks/Areas & Append To Another Worksheet

Apr 21, 2009

i am making a spreadsheet for recording employee telesales activity each day.
administrators will update a shared workbook each day with the information

employee names are across the top and the activities are down the side (calls, CVs sent, interviews, meetings) each day has it's own block of data. on a friday I would like to run a macro to select these blocks and paste them into the first free row on a separate worksheet.

i have something similar working elsewhere which copies one column and pastes that into the last free column of a new sheet but i have no idea how to do it for rows going down a page. I have attached a file example to clarify.

View 6 Replies View Related

Numbering Blocks Of Data.

Nov 2, 2009

I have 250000 lines of data and at the moment they are in seperate blocks of different sizes, and seperated by 5 blank lines.

For Example

112
1523
523
1523

*5 BLANK LINES*

12
23

*5 BLANK LINES*

344
4563

etc.

What I would like to do is give each block a number.

1 112
1 1523
1 523
1 1523

*5 BLANK LINES*

2 12
2 23

*5 BLANK LINES*

3 344
3 4563

The lines in between will come out eventually I just need them there as they are difineing the blocks of data.

View 3 Replies View Related

Compare Blocks Matrix Of Data

Aug 29, 2006

I have the follwing data in A1:T17

3,4,7,9,10,11,21,32,33,35,37,41,47,57,60,64,69,72,74,75
4,6,9,10,12,13,15,21,22,31,35,47,49,52,56,63,64,72,74,75
4,6,9,10,15,21,31,33,34,41,42,45,46,47,57,60,68,72,74,78
3,4,6,10,11,13,18,21,30,32,33,35,46,53,60,67,69,74,77,78
6,9,13,16,21,22,31,46,48,49,52,61,63,64,69,70,71,75,78,79
3,4,7,10,14,17,18,21,28,31,33,36,37,43,47,57,65,69,75,80
4,7,13,15,17,25,29,32,37,42,45,47,50,57,60,64,68,71,72,74
3,7,10,11,16,18,28,34,35,43,47,51,52,55,56,57,60,64,71,72
8,9,10,12,16,21,22,28,38,47,49,51,52,53,54,55,64,66,71,72
4,5,6,9,12,15,19,20,30,34,35,38,45,47,54,56,63,65,72,78
5,6,9,12,15,21,26,31,32,43,44,47,64,66,67,68,69,74,75,80
..................

I want to compare the first set of 20 numbers (A1:T1) with the second set (A2:T2) and check how many numbers match. ** If the matched numbers
are >=10 ** then list them to the right of cell W1. In this example, 10 numbers matched are 4,9,10,21,35,47,64,72,74,75. List them in W1:AF1.

Now compare (A1:T1) with (A3:T3). In this example, 11 numbers matched 4,9,10,21,33,41,47,57,6,72,74 list them in W2:AG2.

Now compare (A1:T1) with (A4:T4). 10 numbers matched 3,4,10,11,21,32,33,35,60,69 list them in W3:AF3.

Compare (A1:T1) with (A5:T5). Here only 5 numbers matched 9,21,64,69,75 (Which is ** less than 10 ** - does not match the criteria) DO NOT LIST
this.

Compare (A1:T1) with (A6:T6). 11 numbers matched, 3,4,7,10,21,33,37,47,57,69,75 list them in cell W4:AG4.

.................

Sub Compare()

Dim r As Integer, rr As Integer, c As Integer, cc As Integer, opc As
Integer, opr As Long

opr = 1
For r = 1 To 16
For rr = r + 1 To 17
opc = 23
For c = 1 To 20
For cc = 1 To 20
If Cells(r, c) = Cells(rr, cc) Then
Cells(opr, opc) = Cells(r, c)
opc = opc + 1
End If
Next cc
...............................
Next r

End Sub
The above code runs fine but If I increase my rows from 17 to 1200 it takes 3 1/2 hours to complete. Can it be reduced?

View 9 Replies View Related

Move Blocks Of Data In 1 Column To Rows

Nov 15, 2006

Moving Data And Deleting Blank Rows. I am a complete newbie to excel VBA and require assistance (if at all possible). I have data in the following format in an excel sheet (I have a lot more rows but give an example for two rows)... in 1 column but I wish to separate it (i.e. move data along onto the first row in other columns) and delete blank rows.

For Example:

Col1
AAAA
BBBB
CCCC
DDDD
EEEE
AAAA
BBBB
CCCC
DDDD...............................

View 9 Replies View Related

Clubbing Blocks Of Data For Two Entities In A Column Chart

Mar 11, 2009

Clubbing blocks of Data for two entities in a Column Chart

I need to show graphically the comparison between two LOAN amounts for a Housing LOAn..

I am almost done with the MATH part part of it, however need to compare the results Graphically...

I have two Blocks of DATA primarily: ...

View 9 Replies View Related

National Insurance Number Validation On Blocks Of Data

Nov 1, 2007

I have a spreadsheet where data is pasted on from spreadsheet supplied by various external companies. One of the main problems we're having when validating the data is an incorrect NI Number and I'm hoping to find a way of getting this automated.

What I would like to do is, when a block of text is pasted into our template, a block of code to look through the selection and check column S for a valid format NI no (eg AB123456C). I have some code (below) that works when changing an individual cell, but nothing that works when pasting in blocks of text.

Private Sub Worksheet_Change(ByVal Target As Range)

stringvalue = Target.Value

statuschange = 0

Application.EnableEvents = False

'Check for text only cells
If Target.Column = 19 Then

View 9 Replies View Related

Fill Series That Combines Data From 2 Sheets In Blocks Of 10

Jun 5, 2006

I have a spreadsheet with two sheets... Sheet1, and Sheet2, that I use as sort of a staging area to format info before bulk uploading to my product database. The products I'm working with at the moment have 10 subcategories below each of them. Sheet1 is the full product database listing. I place new subcategories in blocks of 10 that I plan to add to the database on Sheet2. Sheet2 also holds the default partial category names in cells A2-A11.

Part 1:

I am currently using the following formula on Sheet2, in column C to combine the category name value on Sheet1, with the default partial category name values on Sheet2 (A2-A11). I'll paste two blocks so you have a clear picture of what I mean by blocks of ten.

=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$2)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$3)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$4)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$5)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$6)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$7)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$8)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$9)
=CONCATENATE(Sheet1!B3407;" ";Sheet2!$A$10)..................

View 6 Replies View Related

Dynamic Names Pointing To Blank-Delimited Blocks Of Data

Nov 10, 2012

I have a column of data that is blank-delimited into five blocks. The size of each block will vary, since the data comes from a web query. A blank (empty) cell tells me where a block ends and the next one begins.

I would like to set up five dynamic names (e.g. Block_1, Block_2 and so on), each of which will reference the corresponding segment of data.

What would be an elegant way of defining the dynamic names?

View 6 Replies View Related

Excel 2013 :: Averaging Rows Of Data Within Larger Blocks Of Datasets - Skipping Rows

Jul 16, 2014

I am trying to average different rows and columns within a larger block data set in a series. This data is from a 96-well absorbance microplate reader experiment. I only mention this to describe the raw data output I am dealing with.

Each set of data is in a 12 x 8 block with the next block below it with one blank row between. So I have a block of data contained between A1->L1->L8. The next block is contained between A10->L10->L17. This continues for a total of 28 blocks.

I want to take averages from rows or columns from each block and autofill them into a single column. So for example I'll need =Average(A1-C1) with =Average(A10-C10) below it and so on and so forth. My problem is that if I try to autofill from this already started column the third row will take the average of A2-C2 instead of A19-C19.

Is there a formula/script for me to skip the correct number of rows to the next data block?

I have attached my spreadsheet to this thread. I am using Excel 2013

FeS_Kinetics.xlsx

View 1 Replies View Related

Copy Data With Criteria For Worksheet And Data Range

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

Copy Data Into Specific Range?

Jul 4, 2014

I have a worksheet which contains daily results and want to copy the formulas within the Cell F2 but only copy into the cells where the data has recently been added.

EG: Within the attached worksheet i have just entered the data for the 03/06/2014, so i only want the formula to be copied into column F for this range.

Potential it could the vb code could look at Column B and if blank then paste.

View 8 Replies View Related

Copy Data From Start To End Range

May 2, 2009

can anyone help me how to create a macro which will copy range of data based on a criteria? I have a worksheet which contains dates on column C rows C30:C64, dates would start from march 29 to may 2. how can I copy the range of data from April 1 to April 30? the criteria which will be copied will always be the start of the of the month up to the end of the month...meaning if the dates placed is like April 26 to June 6, it would automatically extract the range for May 1 to May 31 and place this on another sheet.

View 6 Replies View Related

VBA To Find Last Row Of Data And Copy Range

Mar 20, 2013

What would the VBA code be to find the last row of data and have it copy range A2:E(last row)?

View 2 Replies View Related

Copy Data Range From 1 Workbook To Another

Sep 30, 2006

I need to copy the same data range from Sheet 1 of different workbooks in the same subdir to output.xls (also in the same subdir).

In workbook output.xls, I've defined the range of workbooks (source files) as name filenames. I need to copy the same data range (say B8:R8) from each source file to the cells adjacent to the filenames in output.xls.

Eg. I have defined the range of files to copy data from as filenames in Col A of Sheet 1 in output.xls.

Col A
sales_10-09-06
sales_10-10-06
sales_10-11-06

Workbook sales_10-09-06 contain sales data of various product to different firms as below:

Col A Col B Col C .................................. Col R
prod A prod B prod q
Firm 1 50 20 100
Firm 2 20 25 80
Firm 3 30 50 20

How do I copy B8:R8 of sales recorded for each day in sales_date.xls to Col B of Sheet 1 in output.xls? There are 200 files (specified by the name filenames in output.xls) that I need to copy B8:R8 from.

View 9 Replies View Related

Copy Only Cells Containing Data From Range

Feb 8, 2008

I need a macro that will select and copy only cells containing data in columns C to H from one worksheet and paste it to another worksheet in columns B to G.

View 4 Replies View Related

Copy Data From Textbox To Range Of Cells?

Jul 19, 2012

How do I copy a string from a userform1 textbox called 'Code' to cell range A1 to AX where X is a number that I enter into another textbox called 'lightcount' on userform2?

View 1 Replies View Related

Copy Data In Row And Paste To The Bottom Of Another Range?

Aug 2, 2012

The following code is doing what I want it to do in part, its copying the data in range k3 to q3 to the next empty row in columns a to f

It works when I change any cell in column K (11), I want to change to only on a change in K7 or K8? As an afterthought if I'm dealing with the same sheet do I need to make reference to sheet2?

VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim nextRow As Long
If Target.Column = 11 And Target.Cells.Count = 1 Then

[Code].....

View 2 Replies View Related

Copy Range And Paste Data After Certain Row With Condition?

Apr 25, 2014

I am using below code to copy data and paste after a certain row. I want in below code first copy data Range("C4:R46") and paste it automatically 50 times after 43 rows but with this condition.

[Code] ......

View 2 Replies View Related

Copy A Range Of Data And Paste To The Last Row Of Another Workbook

Dec 18, 2008

I need a macro that will copy a selected range from an existing worksheet in a workbook and paste the data to the last row in another workbook on a particular worksheet.

View 7 Replies View Related

Copy Data Range From Excel To Outlook

Oct 6, 2011

How I can email a data range from a worksheet and copy paste into a blank email as a picture (Windows metafile).

View 2 Replies View Related

Copy Partial Data To New Range In Spreadsheet

Feb 19, 2008

I have data in columns a - d; the number of records can vary from a few to a huge number which (to me) makes the problem more complex.

I need a macro that will copy every 3rd record into rows e - h; and every 5th record into columns i - l, etc.;

View 9 Replies View Related

Unable To Copy And Paste Range Data

Jul 14, 2006

I have tried using the below mentioned macro. However, it only work on one cell. I need to copy and paste a range from A1:Q200.

Sub retrievedata()
Dim wbResult As Workbook, wbSource As Workbook, CopyRng As Range, Dest As Range
Dim FileName As String, Filt As String
Set wbResult = ThisWorkbook
Set Dest = wbResult. Sheets("Data").Range("A1:Q200")
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Filt = "All Files (*.*),*.*"
FileName = Application. GetOpenFilename(Filt)
Set wbSource = Workbooks.Open(FileName)
Set CopyRng = wbSource.Sheets("Summary").Range("A1:Q200")
Dest = CopyRng
Application.ScreenUpdating = True
Application.DisplayAlerts = True
wbResult.Activate
wbSource.Close
End Sub

View 3 Replies View Related

When Data File Close Sheet To Sheet Range To Range Copy

Sep 28, 2008

i have a main.xls file and two data file dat1.xls and dat2.xls

mail named file have ar - br- cr- dr- er -fr sheets

dat1 named file have ar-br-cr sheets

dat2 named file have dr-er-fr sheets

and all this files data source is

colomn source a - fd
row source 29-4000

i want to make two commandbutton to main file first for dat1 second for dat2 file and i need a code to use at this buttons to make

when dat1 and dat2 close

main file user when click first button

copy dat1 file ar sheet colomn source a - fd row source 29-4000 cells to main file ar sheet colomn source a - fd row source 29-4000 cells

copy dat1 file br sheet colomn source a - fd row source 29-4000 cells to main file br sheet colomn source a - fd row source 29-4000 cells

copy dat1 file cr sheet colomn source a - fd row source 29-4000 cells to main file cr sheet colomn source a - fd row source 29-4000 cells

View 9 Replies View Related

Copy Data From One Sheet To Another With Dynamic Range Selection?

Feb 21, 2011

The CRM application that we use provides output in an excel sheet "Table View 1". I want to be able to copy the data that is dumped to a sheet to another sheet. The requirements:The data output range is different. Sometimes it is 4 rows and sometimes it is 25 rows. I want a mechanism where based on the output, the formula/macro automatically copies the information to the new sheet.The first 2 rows need to be excluded when the copy is being made.

View 6 Replies View Related

Copy / Paste Dynamic Range Of Cells With Data From One Day

May 28, 2014

I have a range of cells with data from one day. This range is "C36:K63"

Each day, the prior day's range needs to be copied and pasted into a new range that begins two rows below where the prior day's range ends.

Thus, day two should copy "C36:K63" into a new range: "C65:K92"

As you can tell, the rows will change each day, but the columns will always remain the same.

How can I create a macro that allows a user to copy data from the most recent range and paste it into a new range?

i.e. if there is no data in C65:K92, the macro should copy the data from C36:K63 and paste it into C65:K92. Then, the next day, the macro would copy the data from C65:K92 and paste into C94:K121.

View 2 Replies View Related