Free Microsoft Excel 2013 Quick Reference

Transfer Data to New Worksheet for Better Analysis

In WK1, I have sales history for a given rep since 2000. The months are displayed across the column headings and the years by row headings. A2 is Sales, A3 is profit and A4 is % of profit.

On WK2 I want to move and rearrange the data so it is more useful. I.e. it will display months on the row headings and years on the column headings. This way, I can see and track individual metrics across the month of January for x number of years.

I need a function, formula or macro to do this. I will create WK2 to show Sales in this fashion, WK3 to show profits and WK4 to show % of profit so I also need to know how to transfer the same function, formula or macro to the other worksheets.

Thanks!

******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA4B4C4A9B9C9A14B14C14=
ABCD1Jan*'00FebMar*222,00032,00097,465*35,6006,97431,256*425.45%21.79%32.07%*5****6Jan*'01FebMar**745,000124,00074,125*815,40028,76512,450*934.22%23.20%16.80%*10****11Jan*'02FebMar*1255,89077,400205,000*133,21415,42135,690*145.75%19.92%17.41%*WK1*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA3=
ABCDEFGHI3*200020012002200320042005200620074Jan********5Feb********6Mar********7April********8May********9June********10July********11Aug********12Sep********13Nov********14Dec********WK2*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


Hi everyone,

I'm still new with macro programming,, i have this code for transferring data to different worksheet,,but it seems that it does not do what i want,,can you help me edit this code
Supposed to be Name starts with letter B will be transfer on the sheet2 and name starts with letter C will be on sheet3 and so on up to letter Z,, with all the rows within it,, ex. Name, Age , ***

Code:

Sub test()
Dim r As Range, ws As Worksheet, rng As Range
With ThisWorkbook
With .Sheets("sheet1")
Set rng = .Range("a2", .Range("a" & Rows.Count).End(xlUp))
End With
For Each r In rng
If UCase(r.Value) Like "[B-Z]*" Then
On Error Resume Next
Set ws = .Sheets(Left$(r.Value, 1))
On Error GoTo 0
If ws Is Nothing Then
Set ws = ThisWorkbook.Sheets.Add
ws.Name = Left$(r.Value, 1)
End If
With r.EntireRow
ws.Rows(ws.Cells(Rows.Count, 1).End(xlUp)(2).Row).Value = .Value
.ClearContents
End With
End If
Next
With .Sheets("sheet1")
With .Range("a2", .Range("a" & Rows.Count).End(xlUp))
On Error Resume Next
.SpcialCells(4).EntireRow.Delete
End With
End With
End With
End Sub

Dear all

Kindly advise how to set Excel Macro and filter data to new worksheets.
I have worksheet DATA as attached.
I will like to set macro so that:
a) new worksheet contain data for ORG begins with DDD
b) new worksheet contain data for ORG not begins with DDD
Note: ORG in column J highlighted in yellow and letters in blue

I have tried to do so but encoutered error and ended up with no filtered data in the newly created worksheet

Kindly advise

Hello
I am trying to write a Makro which transfers data to another worksheet depending on whose name a line relates to e.g.

Master worksheet contains
Col A B C D
Line 1 Andy London 10 250
Line 2 Fred Glasgow 30 90
Line 3 John Bristol 45 78
Line 4 Pete Poole 5 500
Line 5 Andy Southend 50 120

I would then a makro to turn blank worksheet "Andy" into:
Col A B C D
Line 1 Andy London 10 250
Line 2 Andy Southend 50 12

And another makro to turn blank worksheet "Fred" into:

Col A B C D
Line 1 Fred Glasgow 30 90

etc etc

Is this doable, obviously with a lot more data than just this?!
Thanks
Nick

Rookie needs some help...

I have a SaveAs macro that saves the worksheet into another workbook at a specified location. I need to add to the code. Would like to transfer data from one worksheet into the next available row in another worksheet. I have searched and seen multiple variations of this and I know this is probably cake for experienced programmers but I can't get it right. Is it also possible to create an automatic hyperlink in a cell based off my new saved location.

To summarize what I am trying to do:

Take data from cells N10:W10 in worksheet 'OPL MASTER' and transfer them to the next available row in worksheet 'OPL DATABASE'. I am trying to get it to create a hyperlink in the next available column that this data is tranferred to.

Any help is appreciated!

Public Sub SaveAsA1()
    Dim wbName As String
    Dim ext As String
    ext = "S:PROJECTSBakery MaintONE POINT LESSONS"
    wbName = Sheets("OPL MASTER").Range("A1").Value
    ActiveSheet.Copy
    ActiveWorkbook.SaveAs Filename:=ext & wbName
    ActiveWorkbook.Close
End Sub


:o hey, so I forgot how could be the easiest way to transfer data from one worksheet to another worksheet.

For example:
>I"m making a spreadsheet that once is updated, I want to transfer everything that it says "entered" to another "worksheet" (instead of having to filter on what says entered or blank,)
So I want to transfer those lines to another worksheet that would be just for "entered worksheet"

Any advices would be a great help!!! I totally forgot some codes that I would be able to add in a Macro to make my life easier...HELPP !! ;o)

tnks

Hi everyone,

I would need help to create a macro which could implement the five steps below, however I am not too sure if that is possible at all. Attached is the spreadsheet which contain the 'Staging Jobsheet-Template' worksheet (original data) and the new worksheet (i.e. the outcome following macro).

Explanations of the 5 required steps:

- First step:

1) Add a new worksheet

- Second step:

2) In the 'Staging Jobsheet-Template' worksheet, search for the first non-empty cell in column D (this will be the first row of relevant data)
3) Starting from the row where the first non-empty cell was found (in this case row 16), copy columns A, c and D of the 'Staging Jobsheet-Template' worksheet respectively to columns D, F and E of the new worksheet when the following conditions are met:
a) Cell from column A is never emtpy
b) Cell in column C is equal to a number between 1 and 10

- Third step:

4) Search for content of cell E1 from the new worksheet in the 'Serial No.' row of the 'Staging Jobsheet-Template' worksheet (in this case FCZ150475AA)
5) Once the value has been found, copy the associated Temporary Device Name (i.e. the cell located 8 rows above the cell found in previous step), in this case VPN22457R1
6) Paste the value in the cell located 2 columns to the left of E1 in the new worksheet (in this case cell C1 of the new worksheet)
7) Carry on step 4 through 6 for all values present in column E of the new worksheet

- Fourth step:

8) In the 'Staging Jobsheet-Template' worksheet, search for cell entitled 'Site ID' in column A
9) Locate and copy the first cell located to the right of the 'Site ID' cell (in this case copy value 11111)
10) Paste the value in column B of the new worksheet for all rows having a value in column D

- Fifth step:

11) In the 'Staging Jobsheet-Template' worksheet, search for cell entitled 'Site Address' in column A
12) Locate the first cell located to the right of the 'Site Address' cell (in this case 201-203 MAN,BRADGATE,BP9 4RT,ENGLAND)
13) Paste the value in column J of the new worksheet for all rows having a value in column D

Thanks a lot for your help,
Antoine

In making new worksheets for 2003, how do I transfer the formulas from 2002 worksheets?

Hi there,
I am trying to automate a worksheet to able me to input data into a worksheet and then split out the data into different worksheets for ease of creating charts etc. I have used the autofilter and the macro recorder and have came up with the following:

Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="4C.MT2.P1.EN"
Selection.AutoFilter Field:=2, Criteria1:="=4C.MT2*", Operator:=xlAnd
Rows("1:222").Select
Selection.Copy
Sheets("M2 PCS EP").Select
ActiveSheet.Paste

There is 9 more data sets that i need to copy...is there an easier way to automate this as the way I am doing it seems to be very drawn out

I am trying to automatically display data from 1 worksheet (Master) to multiple worksheets. For example: Each week I run a report for each business unit in my organization (this is captured on the master). What I would like to do is have a tab designated for each business unit and once each report is run it will automatically separate the business units to each tab. I am a novice when it comes to VBA, but a fast learner. HELP!!!

Hi There,

I need your help.

I have designed a tracking system with a userform. In my userform I have a spot where I could identify the status of any given file.

When I go to set the files' status to "CLOSED" or "DEFERRED" It does not move over the entire row of data to the worksheet "Inactive_Data" However, if I were to load the tool in the "Active_Data" worksheet, set the files status to Closed or Deffered and click on the "Add/Update" button it sucessfully does this. How can I modiy the code in the Add/Update button to accomplish this when the Active_Data sheet is hidden or is not the active sheet?

Any help is greatly appreciated.

Thanks for all your help and support.

Ps. I've uploaded a copy of the Excel file.

Cheers and have a great day.

Jay

I want to transfer 5 cells of data to 3 worksheets, Justin gave me a VBA that fransfers data to 1 worksheet but I cant workout how to transfer to 3 worksheets, I have attached a sample that works in part.

Still being challenged, this place is a gold mine I just need to understand to language.

I Currently have some VBA sourced through here which adds to the end of the first instance of a value in column A the values in column's B and C and repeats adding values in new cells for B and C until the value in column A changes.

Now I need to transfer this sorted data to another worksheet (destination.xls) and add it by the reference number in column A to the end of the row with the same reference number.

I have experimented with vlookup with limited success and am looking for a more robust solution.

The data from the spreadsheet called source.xls appears starting in column EE. This will be the same starting position for all rows I have coloured the data for ease of recognition purposes only.

I have attached 2 sheets as examples of what I am trying to achieve.

Thanks for all your help so far.

I have written a worksheet that populates data from a closed workbook
and performs calculations on the data form the closed workbook. I am
not highly skilled with VB and I try to write workbooks with as little
macros as I can.

I populate the data from a closed workbook with a modified function of
INDIRECT called INDIRECT.EXT. That is how I can read from a closed
workbook.

The workbook pulls up data from my cash register for the entire month.
Because of this it takes several minutes (about 3 to 5) to calculate
the workbook. Not that big of a deal but the workbook also has cells
that I need to manually input data into. Therefore, every time I enter
data into one cell the entire workbook recalculate and subsequently,
every manual entry takes about 3-5 minuets. So I set calculation to
‘manual’ but that is not good because if I enter a mistake
I do not catch it until I recalculate again.

Now for the questions:
1- Would it be better to lookup the data from closed workbooks via VB?
Meaning would the recalculation time return to normal (a slit second.)

2- I don’t mind the 3-5 minute calculation as long as I only have
to do it once. So is there a way I can macro or preferably use a
FUNCTION call that copies all the cell data in the worksheet over to a
new workbook/worksheet by just pasting the data and not the formulas?
Also at the same time I want to create the worksheet name via data
located in the worksheet. This way I can manually enter the date I need
to against the data copied over from the closed workbook with the heavy
calculation load put on by the INDIRECT.EXT function.

3- I have run the marco recorder and selected all cells, created a new
workbook, special paste for values only, and named the file. How do I
program the macro to browse for the workbook and create a new worksheet
and asks fo the name?

I am hapyy to attach the workbook and marco is needed.

--
COntactJason
------------------------------------------------------------------------
COntactJason's Profile: http://www.excelforum.com/member.php...o&userid=26143
View this thread: http://www.excelforum.com/showthread...hreadid=394729

This is my macro here:

Sub SplitSheets() 
' 
' split Macro 
' Macro recorded 15/11/2007 by Administrator 
' 

'Code goes here instead of 
Application.DisplayAlerts = False 
Dim LMainSheet As String 
Dim LRow As Integer 
Dim LContinue As Boolean 

Dim LColAMaster As String 
Dim LColATest As String 

'Retrieve name of sheet that contains the data 
LMainSheet = ActiveSheet.Name 

'Initialize variables 
LContinue = True 
LRow = 2 

'Start comparing with cell A2 
LColAMaster = "A2" 

'Loop through all column A values until a blank cell is found 
While LContinue = True 

LRow = LRow + 1 
LColATest = "A" & CStr(LRow) 

'Found a blank cell, do not continue 
If Len(Range(LColATest).Value) = 0 Then 
LContinue = False 
End If 

'Found occurrence that did not match, copy data to new sheet 
If Range(LColAMaster).Value <> Range(LColATest).Value Then 

'Copy headings 
Range("A1:Z1").Select 
Selection.Copy 

'Add new sheet and paste headings into new sheet 
Sheets.Add.Name = Range(LColAMaster).Value 
ActiveSheet.Paste 
Range("A1").Select 

'Copy data from columns A - Z 
Sheets(LMainSheet).Select 
Range(LColAMaster & ":Z" & CStr(LRow - 1)).Select 
Selection.Copy 

'Paste results 
Sheets(Range(LColAMaster).Value).Select 
Range("A2").Select 
ActiveSheet.Paste 
Range("A1").Select 

'Align All Cells 
Cells.Select 
Cells.EntireColumn.AutoFit 
Range("A2").Select 

'delete column A from all sheets before going back to main. 
    Columns("A:A").Select 
    Selection.delete Shift:=xlToLeft 


'Go back to Main sheet and continue where left off 
Sheets(LMainSheet).Select 
LColAMaster = "A" & CStr(LRow) 


End If 

Wend 

Range("A1").Select 
Application.CutCopyMode = False 

MsgBox "Backups Complete." 
Application.DisplayAlerts = True 

Application.ScreenUpdating = True 

Columns("A:A").Select 
Selection.delete Shift:=xlToLeft 


End Sub

It basically splits a worksheet using recurring data via (column A) into new worksheets. For example (as simple as poss) A list of data called "01 backup", "02 backup" etc and puts the data into a new worksheet so it is split by column A, then it deletes all column A's in the new worksheet because they are the worksheet name. That was the easy part of the macro, and it does its job perfectly. What i need to add is the ability to add a new worksheet left of the one that was made, using data from a different spreadsheet in the same column as before.

Ask me questions and stuff to get a better view.

=============================

Some examples as follows:

http://www.freewebs.com/davie1982/Book1.xls
^
The original spreadsheet

=================

http://www.freewebs.com/davie1982/Book2.xml
^
The spreadsheet after the macro is run

=================

http://www.freewebs.com/davie1982/to...added%20up.xml
^
The spreadsheet with data i want the original spreadsheet to contain as workbook names from the Invoice Number column

=================
http://www.freewebs.com/davie1982/Book2%20final.xml
^
The finalised spreadsheet (which i want in after or during the macro)

If this is any extra help i hope you can help me find a solution.

Also posted this problem at http://www.mrexcel.com/board2/viewtopic.php?t=302268

It's Excel 2003

I have written a worksheet that populates data from a closed workbook and performs calculations on the data form the closed workbook. I am not highly skilled with VB and I try to write workbooks with as little macros as I can.

I populate the data from a closed workbook with a modified function of INDIRECT called INDIRECT.EXT. That is how I can read from a closed workbook.

The workbook pulls up data from my cash register for the entire month. Because of this it takes several minutes (about 3 to 5) to calculate the workbook. Not that big of a deal but the workbook also has cells that I need to manually input data into. Therefore, every time I enter data into one cell the entire workbook recalculate and subsequently, every manual entry takes about 3-5 minuets. So I set calculation to ‘manual’ but that is not good because if I enter a mistake I do not catch it until I recalculate again.

Now for the questions:
1- Would it be better to lookup the data from closed workbooks via VB? Meaning would the recalculation time return to normal (a slit second.)

2- I don’t mind the 3-5 minute calculation as long as I only have to do it once. So is there a way I can macro or preferably use a FUNCTION call that copies all the cell data in the worksheet over to a new workbook/worksheet by just pasting the data and not the formulas? Also at the same time I want to create the worksheet name via data located in the worksheet. This way I can manually enter the date I need to against the data copied over from the closed workbook with the heavy calculation load put on by the INDIRECT.EXT function.

3- I have run the marco recorder and selected all cells, created a new workbook, special paste for values only, and named the file. How do I program the macro to browse for the workbook and create a new worksheet and asks fo the name?

I am hapyy to attach the workbook and marco is needed.

Hello everyone, I have a problem with my current macro that uses a basic autofilter to auto filter from the parent database to extract the correct rows and then copies the query and pastes it into a new worksheet to further proceed with the macro.

I have run into a problem because my database has become very big and now when I try to autofilter the query and click on copy, an error regarding the data range reference is too complex - use data that can be selected in one contiguous rectangle

I tried a few things such as to autofilter out everything I dont need and hit delete - this does not work either, same result

I got help here previously in which the code deletes All Hidden Rows and this is very time consuming, I have not tested all my methods but it took 15 minutes to delete hidden files for one method and theres roughly 5 in total

I have to end up running this code on the parent worksheet multiple times because I use the parent worksheet to extract different parameters into different worksheets!

I have noticed that if I manually copy the data in smaller blocks, by halving the data seems to work,but I do not know how large of a partition I am limited to copy because my database is very large and the size varies month-to-month so I cannot put a number on the max range. I think if I could get a macro to do it by thirds or preferably quarter range should be safe.

So just to summarize, I am trying to devise a method in which I would auto filter on the active parent sheet "sheet 1" and I would copy the auto-filtered query to "sheet2" instead of copying the whole worksheet in one instance I would like to split the autofitlered query into four equal parts with respect to the range of the worksheet and then to copy the first quarter of the query and paste in sheet 2 and then the second quarter to sheet 2 and so on untill all four quarters are done one after the other, so sheet 2 should be a series of all four parts combined into one series on sheet2

Any help is appreciated!

Hi guys,I have a database in which a record is recognised by a number.I want to copy the information of a record to a new worksheet. The record to copy is a variable (recordnumber).You press a button and a userform comes up in which you fill in the recordnumber you want to copy.Then a script should determine which rows in column 1 have the same recordnumber and copy these cells.For some reason I cannot get it to work and I do not know where it goes wrong..I use the following code:

	VB:
	

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I also attached a dummy of my worksheet so you get a better idea of what I mean...

I have data in a master sheet in rows. i want to copy each row to A1 of a new worksheet. But when I copy and paste the the function from one new sheet to another it simply copies the same row, I would like the next row in the new/next sheet. So Row 2 of my master would go to row 1 of Sheet 1, row 3 of my master would go to row 1 of Sheet 2 and so on....I know this has to be realively easy ....I am just being stupid. Thanks in advance for any assistance you can offer

Hi everybody.

i have a userform with 5 textbox, in the last 2 textboxes, i have date and time consecutively.. i have already insert a vb code which will enable me to transfer data to a worksheet. it works fine, i want to have a vb code so that when for e.g, i insert 1248(employee no.) in the first textbox, all which follows the next 4 textboxes will enter in spreadsheet 1248 when i click on post button, and the form will be cleared.same for the other employee number. i have already did it, but all enters in the same worksheet.I already rename 5 spreadsheet 1248,1243..etc. how to transfer the employee number(1248...) and the 4 textboxes to its specific worksheet i.e 1248,1243..., as i really dont know how to do it, or if its possible to do it

Please help me, its important , im trying to do a time clocking.

Thanks all

Hello,
I need to copy selected data into a new worksheet. For example I have
the following data :

Shp pt So Doc type Shp Num
U003

Evening all

I have a table of data that has been split by cost centres and by debit and credit entries. I.E there are two columns under each cost centre (cost centre 000 covers columns C and D). Is there a way to export the information contained in each cost centre to a new worksheet in the same workbook?

Cost centres cover two columns. The cost centre's heading is included in the first column relating to the data ,on my spreadsheet C7, but information relating to this code is also included in the column to the immediate right of this column, column D.

Therefore the first set of data covers column C and D. The second covers E and F, and so on.

In addition is there a way to make sure only rows containing values are exported from the origional workbook?

Thanks again for your time guys

I am using Excel 2003. I have created a workbook with multiple sheets for tracking data on a weekly basis. Each sheet is the same, same rows, same headings, just different data coming in (tracking sales, hours, etc). The workbook is being used by a manager that is not familiar with Excel. I have created it so all she needs to do is copy/paste a new worksheet for each new weeks data.

What I want to figure out is how to create a summary sheet at the beginning of the workbook that will automatically update the data that is currently populated, but will also update new data that is added when she creates a new worksheet.

I am not an expert with excel, and have yet to use any VBA solution (in fact, I just today learned that term, if, indeed I am using it correctly). If there is a better way to do what I am doing, I'm very open to suggestions.

The reason I used this set up for my manager is that it also includes graphs for each week, so she can "see" the data better. The copy/move worksheet works great for creating a brand new worksheet with the graphs I put into them, so when she populates the data, she can see the results immediately.

I have a workbook to collect and display data, on a monthly basis. The data for each item appears in blocks of 8 consecutive rows. Some of this data needs to be transferred to a new workbook for the following month, whilst some needs to be ignored. Does anyone know how this can be done without manually copying and pasting each data block into the new workbook?

Hey Guys,

I am pretty much stuffed in this one.

I have a spreadsheet with 9 columns & about 13K rows worth of data in it on sheet1. Task is to Match the combination of columns A,B,C with that of D,E,F and G,H,I and the transfer it to sheet2.

So I guess it will be like:

If A,B,C = D,E,F = G,H,I then transfer it to sheet2.

I am not too versed with coding knowledge but can work my way through research. I did looked up all the forums & couldn't find anything like this.

Any help or may be better way of doing it will be highly appreciated.

Thanks Guys
DJ