Copy A/F Data To New Sheet & Retain Format

Jan 8, 2010

Using xl 2007 - The following code is working fine. except on completion my new sheet does not have the same nice formatting of the Original Sheet.. So I need to Incorporate the formatting of the new sheet to be the same as the Original Sheet.


Sub DumpAutoFilterToNewSheet1()
Dim wss As Worksheet
Dim wsd As Worksheet
Set wss = ActiveSheet
If ActiveSheet.FilterMode Then
Set wsd = Worksheets.Add
wss.AutoFilter.Range.Copy _
Destination:=wsd.Cells(1, 1)
Else
MsgBox "Nothing is currently Filtered" & vbNewLine _
& "Filter a Column and Try Again."
End If
End Sub

View 9 Replies


ADVERTISEMENT

Copy Chart Without Source Data & Retain Formatting

Feb 21, 2008

I have 2 sheets in one workbook; one sheet has a chart with a data table, the other contains the source data for the chart. I want to copy and move the sheet with the chart to a new workbook, but without the source data. I break all links, but then the chart's data table takes the default values and formats in the new workbook after I break the links. For examples, the source data has formats such as currency and no decimal places. However, after copying the sheet, the data table reverts to no dollar sign and decimal places. In addition, the months on the x-axis turn to numbers when the links are broken.

View 3 Replies View Related

Join Cell Text & Retain Format Of Each

Oct 16, 2006

I have two cells. In cell A1 there is a text "(fi) = " and in A2 a number, for example 30. (fi) means a symbol for diameter (letter "f", using Symbol font). Is it possible to joint those two cells in third cell using formula "=A1&A2", maintaining original fonts? What i get is "f = 30", not "(fi) = 30".

View 4 Replies View Related

Retain Format Importing Text File

Jun 6, 2007

I am having problems choosing a format for the column in my spreadsheet that contains my account codes. This is especially a problem when the code starts with an '=' sign and is followed by a number or has a '£' sign followed by a number.

I receive the file in a .txt format which I guess if I get it that way I would not have a problem but as I need to work on the file in excel before uploading it I experience problems. For instance when I open the file the codes that begin with '=' + a letter for instance =Y just comes up as '# name?' and only when I click in the cell can I see that it is =Y. Also the code '=1' just comes up as '1' as does '£1' - now this causes problems as for a start I need to do Vlookups on the account code but now instead of three distinct codes I have 3 1's.

The only way that I have discovered how to solve this is to click into each cell and add a ' before the text. (this changes the format to text - right??) But obviously this is very time consuming - is their a quicker way to change these cells. I can't just right click and format cells - text. This does not work?

View 5 Replies View Related

Join 2 Times In Cell & Retain Time Format

Aug 23, 2007

I'm usually able to find my answer but I couldn't find anything to match what I need. Anyway... this is either really simple or impossible to do. I have two cells which have times in each. Say A1 has 5:00 PM and A2 has 10:00 PM. I'm just trying to make it so one cell will say:

From 5:00 PM to 10:00 PM... So I tried this ="From " & A1 & " to " & A2

But as I'm sure you already know that yields this result:

From 0.708333333333335 to 0.916666666666668

View 3 Replies View Related

How To Copy Linked Excel And Word Files And Retain Links

May 30, 2013

I have links between Excel files and Word files. I use these files for multiple projects. When I copy them and paste them to a new folder the new files would be linked together. For some reason this has stopped. Now when I copy, the Word file has links to the original document, not to the copied document.

View 7 Replies View Related

Can I Copy Format From Conditional Formatting To Other Sheet Or Cells?

Sep 9, 2003

Can i copy format from conditional formatting to other sheet or cells?

View 9 Replies View Related

How To Automatic Merge Data From Sheet To Another Sheet With Same Format

Mar 28, 2014

how to merge data automatically in one sheet (master sheet) from specific sheet with different name sheet but same format?

Adding, deleting or editing should be automatic update to master sheet.

For example:

Sheet name:

Sheet 1: Salesman 1 - with data range A10:F310 (as his data file)
Sheet 2: Salesman 2 - with data range A10:F310 (as his data file)
Sheet 3: Salesman 3 - with data range A10:F310 (as his data file)
Sheet 4: Salesman 4 - with data range A10:F310 (as his data file)
Sheet 5: Salesman 5 - with data range A10:F310 (as his data file)
Sheet 6: Salesman 6 - with data range A10:F310 (as his data file)
Sheet 7: All Salesman - - with prepared data range A11:F2000 (as master data file/record for 6 salesman)

Column Name as a Header:

A10: Customer Number
B10: Customer Name
C10: Salesman ID
D10: Promotion Type
E10: Promo Item
F10: Free Item

View 2 Replies View Related

Retain Date Format For Input Entered As Date?

Mar 24, 2014

I'm using nested SUBSTITUTE formula to make some changes to the some of the data. I'm also using IFERROR to return the input if it does not find the criteria I have specified. I have lot of variations in my input data.

The formula works fine for all except for date format input.

Sample:

N90232Y09--->SUBSTITUTE(A1,"N","")-->90232Y09
12335--->12335
12/3/1923---->58936 (I want the date to be retained)

why the date format is changed even if don't specify any changes for it. I expect my IFERROR to just retain the input as it is.

View 4 Replies View Related

Combine Text & Date. Retain Date Format

Nov 8, 2006

Date:31-Oct-06 (the date is in cell A3)

Formula: "as at"& " "&A3

Result: as at 39021

I need the results to be "as at 31-Oct-06". Is there a formula for this??

View 2 Replies View Related

Join Text & Number & Retain Number Format

Jan 11, 2007

I used to get data from a database (CorVu & MIMS) in this format "0122458/001". Due to changes in those Databases I now get the data as 2 columns " 0122458" and "1" .What I need to do is somehow get this back to the old format including the leading zeros.

View 8 Replies View Related

Retain Data From A Formula

Oct 30, 2009

I need a solution so that each day I can get a value from a single cell but it appears in a new column each day.

eg, A1 contains a numerical value (based on a formula)
Each day there is a new column, say, Monday = B1, Tuesday = C1 etc.
On Monday B1 takes the value of A1 and 'freezes' the value in B1
On Tuesday C1 takes the value of A1 and 'freezes' the value in C1, but the value in B1 remains as it was when it was originally frozen.

View 9 Replies View Related

Retain Data On Userform

Jun 30, 2006

i am using the following code to put data into a worksheet which it does perfectly. once the data is put into the sheet it is saved. what i want to do next is to retain the data in the userform and possibly change one or two entries and put data back into the sheet to save again

Option Explicit
Dim ans As Variant
Dim MyArray(100, 4)
Public MyData As Range, c, d As Range

Private Sub cmbAmend_Click()

Application. ScreenUpdating = False
Set c = Range("b4").End(xlUp).Offset(3, 0) ' c selected by Find
c.Value = Me.DTPicker1.Value ' write amendments to database
c.Offset(1, 0).Value = Me.TextBox1.Value
c.Offset(2, 0).Value = Me.TextBox2.Value
c.Offset(3, 0).Value = Me.TextBox3.Value
c.Offset(12, 0).Value = Me.ComboBox1.Value
Set d = Range("b16").End(xlUp).Offset(1, 0)

View 6 Replies View Related

Copy Data From One Sheet (Fixed Cells And Sheet) To Another Sheet

Apr 18, 2009

I want to be able to copy a name from one sheet (Available Players), paste it to a cell in another sheet (Round 1 through Round 20). The cell that will be copied is fixed but the place where it will be pasted will be different and may be on a different sheet.

also i would like to change the color of the copied cell to "greyed" out or cut if it can not be greyed out. I have created a button and put in a macro that i created but have been having problems with it, generic 1004 errors that i can not figure out. i am attaching the document.

View 8 Replies View Related

Retain UserForm Data After Close / Save

Jan 31, 2008

I am trying to accomplish keeping userform data in the userform after saving & closing the workbook. I am not referring to saving the data in a worksheet. Is this possible?

View 3 Replies View Related

Retain INDEX MATCH Data From External File?

Jun 11, 2014

I've 1 master file with event info (dates, times, categories, prices etc). I have another file to track sales which uses index match to pull data from the master file (stored on a server) instead of double entering.

Upon opening the sales file, all the index match cells are blank until opening the master file, even after saving.

Is there a way to make the file retain all the data it pulls without setting manual calculation (manual spreads like a virus to other user's files - finance manager almost killed me last time)

I currently have a macro button set up to open/close the master file, but if I try to filter/sort the data without the master file open it all disappears again.

View 1 Replies View Related

Copy Data To Another Worksheet But With Different Format

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

Format Data To Another Sheet Macro

Feb 23, 2009

I import a raw data file from our phone system into sheet 'imported', I then want to display the data on sheet 'formatted' how I have displayed it for the purposes of this example. I had a solution to this but the formatting out of the phone system has changed.So I need it to list the agents names, and then the corresponding time for each activity. The activities are listed on B3:I3.

View 2 Replies View Related

Move Data And Format On Another Sheet?

Jul 30, 2014

I've got a workbook with a sheet named Data that has the information laid out in a single row. The data can't be fit onto a single sheet for printing / viewing so I've got a second sheet named Presentation that the data get's laid out to 2 rows so that it can be viewed / printed on a single page.

I'm handing this off to non-technical people so I need an easy way to populate the presentation sheet with the data. I've layed the data out using =Data!A1 however, the drag auto-fill doesn't properly fill it down since it's single row to double row.

Example of Data Sheet
Patient
Chart

[Code]....

View 3 Replies View Related

Macro To Copy Data From Sheet 1 To Multiple Pages On Sheet 2 In Correct Cells

Jan 15, 2009

I have attached a 97-2003 .xls file with data for multiple store locations on sheet 1, and the desired result on sheet 2. I am actually using excel 2007, but I dont think I need any special features that it provides.

I will try to explain the issue here without opening the attachment.

Here is an example of the Data on Sheet1

View 13 Replies View Related

Copy/paste Worksheet Into A New Sheet Generated By New Data In Master Sheet?

May 26, 2013

I am trying to put togther a VBA form button click to do the following: I have several customer names all in master sheet A1 - A300. I want the code to notice that there is a new customer and generate a new sheet, naming the sheet the customers name and copying and paste the entire sheet named 'worksheet' to this newly generated sheet.

View 2 Replies View Related

Transfer Data From One Sheet To Another With Different Format And Formatting

Sep 12, 2013

create a fixture list from data i have in 2 other worksheets,Fixture Generator and Results.I was thinking the results page would be better to get the info from as it is already filtered (if 16 teams, 8 rows + 2 headers, if 9 teams, 5 rows + 2 headers etc.)The thing is, the filter has it listed in a column.

How would i get the info, conditionally, to enter into the fixtures sheet so that week 1 would be to the left, then week 2 to the right of that, up until week 4, then start a new row of fixtures until it is complete. Possibly having a gap between the first half and the second half ?

View 2 Replies View Related

Copy Data And Sheet Name To New Sheet And Delete The Old Sheet?

Mar 30, 2014

Example, I have a sheet named DATA1, I want to add a new worksheet, copy a certain range from the DATA1 sheet to the new sheet and rename it the same name DATA1 and delete the old DATA1 sheet.

Also, the sheetname will be unknown, the macro must get the sheet name first.

View 2 Replies View Related

Copy & Transpose Table Data Into Database Format

Sep 14, 2009

I have a sheet with products down the side, and dates across the top. Numbers will be entered at an intersection between dates and products to represent quantity. Whenever there is a non-blank value for the quantity, I need to copy the products, dates, and quantity into a traditional database format. I have attached the original data format I get from suppliers on when they will deliver, and then show a separate tab for "Desired Result"

View 2 Replies View Related

Retain Formulas In A Few Cells In A One Worksheet So They Are Not Cleared By "clear Contents" Without Using Protect Sheet

Apr 8, 2008

i have a worksheet which is a form which feeds into another worksheet which acts as a database. i have a button which clears the form based on code which is essentially "clear contents".

the problem is i have a few cells where i would like to retain the formulas. i tried to do this with custom in validation but this did not work. i also tried to enter the code directly into the worksheet but this didnt work either (my skills are limited..). i am avoiding using protect sheet bc that in turn will affect many of my other buttons. is there something i am overlooking?

View 9 Replies View Related

If Data Is In Cell Copy To New Sheet And Print New Sheet?

Jun 13, 2014

Ok so i have 2 sheets. Sheet 2 is a form that needs to be printed.sheet one will have data pasted into it by the user. The data will be placed in column a and b. If a has data in it then so will b. Now I need the macro to identify if data is in a then the macro needs to then copy a and paste special into A18 on sheet2 then copy b and paste into A6 on sheet2 then print sheet2. Repeat this process to every row as long as A has data in it.

View 3 Replies View Related

Drop CSV File Into One Sheet And Copy Data To Another Sheet?

Jul 8, 2014

update code based on a dynamic range of cells, which worked! I want to drop a csv file into one sheet, and copy the data to another sheet. However, if I drop a new csv file in, and there are fewer rows, the old rows aren't deleted. For example, if my first set of data had 10 rows, and the new has 8, the extra two rows are still there.

[code]
With Sheets("Raw Data")
.Range("A10", .Range("A" & Rows.Count).End(xlUp)).Copy ActiveSheet.Range("A12")
.Range("B11", .Range("B" & Rows.Count).End(xlUp)).Copy ActiveSheet.Range("B12")
.Range("E11", .Range("E" & Rows.Count).End(xlUp)).Copy ActiveSheet.Range("C12")
.Range("F11", .Range("F" & Rows.Count).End(xlUp)).Copy ActiveSheet.Range("D12")
.Range("H11", .Range("H" & Rows.Count).End(xlUp)).Copy ActiveSheet.Range("E12")
End With
[code]

View 2 Replies View Related

Macro To Copy Data From One Sheet - Paste In Another Sheet

Jul 29, 2014

How to create macros. I need to copy a certain group of cells from one sheet to another, and then do it for x number of times. I'm just using the record function and now i'm lost. Here is the code i currently have:

Sheets("Mapping QTR2").Select
Range("B137:D230").Select
Selection.Copy
Sheets("ExpDB_Q1").Select

[Code] ....

Also, column C is blank. i would like to get the values from another list in the same workbook, say "branch list". I would like to populate Column C with one specific branch for each "batch", if that makes any sense...

View 3 Replies View Related

Copy Row From Sheet 1 To Sheet 2 When Data Entered In Cell F

Jul 9, 2009

I have Worksheet 1, with columns A to E. I would like a row to be copied to Worksheet 2, as soon as cell F in Worksheet 1 is populated. Also the row to be deleted from Worksheet 1.

So, as soon as F1 in worksheet 1 is populated and enter button pressed, row A1:F1 will be copied to the next empty row in worksheet 2, while being deleted from worksheet 1. So eventually all rows in worksheet 1 will be deleted and rows in worksheet 2 will be populated.

View 2 Replies View Related

Arrange Some Set Of Data In Fixed Format And Create New Sheet For Each Set?

Oct 28, 2012

I was wondering if i could arrange some sets of data (on one sheet) in fixed format and create a new sheet for each set of data using VBA

I have some sets of data in sheet (similar to the sheet "Raw data" of the attached file)
first four rows contain costumer-1 details,
next few rows contain bill details of costumer-1,
again,
next four rows contain costumer-2 details,
next few rows contain bill details of costumer-2,
and
table goes on for around 10-20 costumers

Now, I want to arrange the given set of data of each costumer into a fixed format (as sheet "reminder pad" of the attached file) and create a new sheet for each costumer

Also I want to add reference no to each sheet (with automatic increment for each sheet)

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved