Copying Cells From One Worksheet To Another And Maintaining Format

Aug 7, 2008

I have a worksheet that gets autofiltered by the user. I need take the unhidden data and copy it to a new worksheet.

Range("H18").Select
Dim sh As Worksheet
Dim Cell As Range
Dim Txt As String
For Each Cell In Sheets("Panel Check List").Range("H18:H5000")
If Cell.EntireRow.Offset(1, 0).Hidden = False Then
Cell.Copy
Sheets("Query Results").Range("A6").Select
If IsEmpty(ActiveCell.Offset(1, 0)) = True Then
ActiveCell.Offset(1, 0).PasteSpecial
End If
End If
Next Cell

View 9 Replies


ADVERTISEMENT

Copying A Value Whilst Maintaining Format

Mar 2, 2009

Cell A1 could contain either % value (eg 50.0%) or could contain a numeric value (eg 50.0). (It's input is written by a macro that performs a sorting function, hence the mix of % and absolutes).

I need to copy the contents of A1 to cell B1 with a simple formula "=A1"

The problem is that Cell B1 will either show 0.5 or 50.0 depending on whether A1 is a % or number, respectively.

Doesn anyone know how I can output in B1 with the format from A1?

View 9 Replies View Related

Copying And Pasting Cells From A Worksheet Into A List Format In Another Worksheet

May 18, 2006

I need help with a macro for copying and pasting of cells. I believe this should not be a problem for the Excel VBA experts, but for someone who can only record macro, I'm really at a loss.

Attached is a sample file, where sheet 'Source' is an example of the sheet from which data are to be copied. The other sheet, sheet 'Final' is an example of the final format that I need. The reason I'm doing this is I'm planning to upload my data into Access and so I need to convert them into a list format.

List of target columns in sheet 'Final' and source cells in sheet 'Source':

Column A: Biz ID - not sure if I really need this, by right it should be listed automatically once I paste the data
Column B: B2 of 'Source'
Column C: B2 of 'Source'
Column D: B1 of 'Source'
Column E: row 6, relevant column
column F: column K
column G: row 5, relevant column
column H: the specific amount

So basically I'm creating an entry for every amount in the table.

View 9 Replies View Related

Copying Vlookup And Maintaining Original Table Array

May 5, 2008

I'm trying to copy a vlookup to an entire column, I want to look up a value in the cell to the left and compare it to a given table; but when I copy it down it up dates the cell value, but it moves the table down by one row at every row so it's not finding most of the values the further down I go.... what am I doing wrong. I doubt I'd have to re write the formulas in all the 2500 cells I need to look up.

View 3 Replies View Related

Maintaining Row Format

Feb 28, 2007

I have a table where alternate rows are formatted with interiror colorindex 15. Macros will result in 1 or more of the rows in the table being hidden (this can be consequtive rows).

Obviously, when a row is hidden (unless that results in 2 consequtive rows hidden) the alternating row format no longer works (ie either 2 grey rows or 2 nofill rows together)

I am struggling with the code to set the format when a row is hidden. I loop through the table to set format, fine if no row hidden. But when row is hidden everything I try to do to take account of the hidden row results in an endless loop when the hidden row is encountered.

View 7 Replies View Related

Sort Maintaining Format

Jan 1, 2007

I have a spreadsheet that has tons of entries for revenues and expenses. Some cells on the rows with revenues have a different format than these same cells on the rows with expenses.

The problem is that, when I sort the rows to organize them by date, Excel seems to only organize the values in the cells, screwing up all the formats, mixing the revenues format with the expenses format.

Is there any way to organize the information maintaining each cell's formats?

View 9 Replies View Related

Excel 2010 :: Maintaining Format In A Cell When Using Formula

Jan 10, 2014

I just upgraded to Excel2010 and noticed that if I calculate percentages resulting from other formulas, the result displays as a percentage with 15 decimal places and it ignores the format of the cell. My guess is that since formulas are being used in cells A1,B1, and C1, Excel is ignoring the format. When I hard code the numbers, I obtain the desired result.

Is there a way to change the default of 15 decimal places for formula results involving inputs that use formulas in their own creation? (outside of the Round function in each cell? Within Options?)

The data looks like this:

A1 = formula resulting in 1.76%
B1 = formula resulting in 1.90%
C1 = formula resulting in 1.69%

D1 = C1*B1/A1 resulting in 1.8218147382920100%

The desired result is 1.82%.

View 4 Replies View Related

Adding And Deleting Rows In Worksheet While Maintaining Formulas?

Mar 26, 2014

I have a Productivity Report that contains very basic formulas that provide totals for 4 columns (B6:E6) and an average for one column (F). I have included two command buttons, one to add a new row and the other to delete a row.

I need to be able to add or delete rows depending on how many employees' productivity I will be tracking on any given week; each row represents a separate employee. I need the following functionality out of my form:

1) formula in column F needs to copy and paste with each new line
2) when a new line is copied and pasted I need the contents to be cleared
3) I need the user to be blocked from deleting the first row (3 on this form) in the table

The code I'm using for my "Add" button is:

[Code].....

The code I'm using for my "Delete" button is:

[Code] .....
The buttons add and delete rows as I'd like them to but content is not clearing, with each row added the contents provide a sub total. I've tried various lines of code (some more complicated and some less) before I recorded my own macro (see above).

Attached File : Productivity Report (HH).xlsm‎

View 8 Replies View Related

Excel 2007 :: Copying Conditional Format To Multiple Cells

Nov 5, 2013

I'm a 2007 user. I am trying to conditional format a range of F1:J10, where F1 will be conditionally formatted relationally to A1. G1 is relational to B1. H2 relational to C2, etc., all the way down to J10 relational to E10. My conditional format formula in cell F1 is >A1*2 (will format F1 as orange). I want to be able to copy the conditional formatting over so the formula in J10 for example would be: >E10*2 (will format it orange).

I omit the "$" in the conditional format formula in an attempt to prevent it from being absolute. But every time I copy the conditional formatting over to the other cells, the formula within those cells remains identical to the formula in the original cell.

View 6 Replies View Related

Insert Row While Maintaining Merged Cells - Locked Sheet

Oct 28, 2011

I have a protected sheet with merged cells. I would like the user to be able to insert a row and have the formatting (including merged cells) duplicated on the new row. I've searched and seen some different options using VB but I'm clueless as to how to use those. Is there a simple way to do this?

Copying the row and using "insert copied cells" won't work because they get an error because of the sheet protection.

View 1 Replies View Related

Copying Row Into Worksheet After Populating Cells

Mar 26, 2009

I have the following simplified example:
Table with three columns with a growing number of rows. The user enters data into each row, once user enters the last value and hits "Enter" on the final cell (column c) I want a script to evaluate the row and copy the entire contents of the row onto an existing different worksheet. The first column (A) contains the attribute that will be the condition that determines which sheet to paste in

For example, in my attached file, the first row contains the value "Square".
I want that row be copied and pasted into the 2nd tab of the worksheet (reserved for "Square" data) on the next free line. Simiarly, Triangles should be pasted on the thrid tab. Note however that i think the data should be copied line-by-line as the user enters it, which obviously isnt the case for the example dummy data, its just there to simulate my setup

View 4 Replies View Related

Copying A Series Of Cells To Another Worksheet

Sep 5, 2006

is it possible to copy various cells from one worksheet to another. I have a cover sheet holding various jobs for different individuals, data validation has been used to set up various drop downs for the peoples name etc. On selection of an individuals name i was wondering could various cells on that row be copied into a seperate worksheet with that individuals name being the tab name? I have tried using the " Lookup" function but im not getting too far.

View 8 Replies View Related

Copying Adjacent Cells From Matches In Other Worksheet

May 8, 2014

I have a spreadsheet which has a source worksheet, containing 2 columns with thousands of client numbers and account numbers. I have a destination worksheet which contains certain account numbers which are featured in the source sheet.

What I need to do is pull the matching client numbers across from the source sheet into the destination sheet. An example is attached.

View 5 Replies View Related

Copying Rows Of Data Into Different Cells In A New Worksheet

Jul 31, 2008

http://www.excelforum.com/excel-programming/651452-copy-rows-of-data-into-a-different-format.html

Here is the problem:

I have a list of activities (each activity is one row of data). These will be input manually into Sheet2. (see attached file)

The activities then need to be automatically copied and pasted into Sheet1. However, the data is displayed in a different order than in sheet1. So I need some code which will run through a loop for each row of data and then copy and paste it into sheet1 until it reaches an empty row.

The cell positioning of each data set in sheet1 is equally spaced. VLOOKUP will not work for me here as I do not want any formulae or VBA script in sheet1. I cannot change the format of sheet1 as it is a company form.

View 9 Replies View Related

Copying Specific Cells From Worksheet To Activesheet

Oct 30, 2009

I'm trying to "export" data from a static ws "order" (Sheet2) to a selected (active) worksheet. This will happen with 15 different (random) cells.

I got this far but the copying isn't happening from the correct sheet or going to the active sheet. The data in "G5" on "order" should go to the first unused row on the active sheet. What I got was backwards.

Once I get the first one to work I can finish the code for the other 14 cells. I appreciate getting pointed in the right direction

View 6 Replies View Related

Autofill Cells - Copying Worksheet Tab Names

Nov 15, 2007

In file named (Book 1) I have a lots of tabs named e.g. pd1 pd2 pd3 etc.

In another file (Book 2) I need to link cell A1 (on Sheet 1) to cell A1 on pd1 (in Book1)

Which I can do fine.

In Book 1 I want to autofill the formula i have that refers to A1 on Pd1 but when I drag it I want the formula to go up in a series to refer to Pd2, Pd3.

So the final formula looks at $A$1 on Pd1 then on Pd2 Pd3.

View 9 Replies View Related

Copying Data From Array To Some Specific Cells In Worksheet

Mar 7, 2007

I have created one-dimensional array and now I try to copy the data from array to some specific Cells in worksheet. But it seems impossible all the time!

Sheet2.Cells(56, 3 + m) = LossLocationInt(m)

NB: LossLocationInt(m) is an array containing value in it. But, this doesn't copy to the Sheet2.Cells(56,3+m). For clearness: the array has type variant

View 4 Replies View Related

Sort Data And Auto Copy Sorted Data To New Worksheet While Maintaining WS1

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

VBA Worksheet Change Insert Line And Format Particular Cells?

Nov 8, 2013

what I am looking to do is when in colum AA a cell changes to "Drawdown" I'd like the cells in column A:D,F,H:L,N:Q,S:Z on the same row to grey out and a line to be insert below the cell that was changed, if it selects "Fee" , cells in A:D,F,H:L,N:Q,S:Z on the same row.

I have writen the following butit seems rather cumbersome and I can not get the insert line to work.

Code:
Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim IntersectRange As Range
Set WatchRange = Range("AA1:AA500")
Set IntersectRange = Intersect(Target, WatchRange)

[code].....

View 6 Replies View Related

Macro To Find Cells With Perticulat Format In A Worksheet

Sep 6, 2007

I have a work sheet with around 50 odd sheets. What i want is to list out all the cells that are in perticular format

e.g.....

View 9 Replies View Related

Filtering Column Cells And Maintaining Other Column Values

Mar 11, 2014

I have a spreadsheet with multiple columns:

Column B - Host Contact
Column C - Company Name
Column D - Attendees
Column E - Contractor Position

Column B will have anywhere from 1 to 10 names.
Column C will have 1 -2 names.
Column D will have anywhere from 1 to 10 names.
Column E will correlate with column D.

This list will go to multiple people who will want to filter column B for their name. When they filter their name, they should see all values in columns C, D and E.

When I try to filter this spreadsheet by a persons name in Column B, it only shows me the first value in columns C, D and E.

One of the attached pictures shows a mock-up of the unsorted list, the other shows the sorted list.

Sorted Format.PNGUnsorted Format.PNG

View 1 Replies View Related

Copying An Autofiltered Query From Worksheet A To Worksheet B In The Same Book

Jan 6, 2009

There is an autofilter applied to worksheet A, I would like to transfer only the autofiltered range from worksheet A to worksheet B, a clear worksheet B statement would also help.

View 9 Replies View Related

Copying From Hidden Cells Are Not Copying Protected

Dec 12, 2007

I am trying to make the cells Iam am copying paste hidden cells with all formats - seems to work fine other than the security part of them, Iam makeing a sheet for work and just trying to make it were it will not get destroyed by other users-

View 12 Replies View Related

Copying The Format Of A Cell

Jan 20, 2009

I am using to copy info from a cell to ta text box that I created using the tool box.

View 2 Replies View Related

Preventing Copying Of Format

Jan 22, 2009

I have an Excel 2007 file that is a work of art. Users manually copy several rows and columns from a table on a web page and paste it into my Excel file. This turns my beautiful Rembrandt into a Jackson Pollack mess. The formating of my cells is replaced with the formating from the web page. Is it possible to make the Excel file ignore the formatting of the data being copied? As a default?

View 9 Replies View Related

Copying Few Columns From One Worksheet To New Worksheet

Jun 9, 2008

I have one worksheet from which i need to copy only few columns (Ex column A,B, D,G...need not be in sequence.....) to a new sheet.can you please help me to write a macro for this.In addition: Do we need to specify the number of rows in column or is there any way to get data till the end of column automatically.

View 9 Replies View Related

Convert Data Into A List Format By Copying And Pasting

Jun 6, 2006

I have a consolidation workbook and source files.I would like to convert the data from the source files into a list format in the consolidation workbook.

I have attached a sample of the sheet format of the source files in the attached file, called 'Page 5'. The other 2 sheets are 'Template' and 'Instructions'. The 'Template' sheet is what I imagined would be the list format of the data copied from the 'Page 5' sheet. Instructions is where the lookup table for currency is.

So basically starting from row 8 in 'Template' sheet, I would like to copy and paste from 'Page 5' sheet to 'Template' sheet:
- H2 to A8 & B8
- B2 to C8
- According to the list of currency in 'Instructions' sheet, lookup the currency according to operating unit in C8 and paste to D8
- D5-I5 to E8
- row A8-A23 to column F-N

I actually have had a similar problem before, which Derk has helped me here - link: Use Access or XL? (I've decided to start a new thread because this is a more relevant forum).

I have tried to modify the code but I am rather lost as to which part I am supposed to modify.. Note that in this code, the source data is in separate file instead.

Sub add()

Dim wb As Workbook, f As Worksheet, t As Worksheet, j As Integer, k As Integer, n As Integer
Dim mty As String, yr As Integer, d As Date, bu As String, cur As String, sTodo As Variant

sTodo = Array("Page 5") 'finish adding the names
Application. ScreenUpdating = False
Set t = Workbooks("Example1.xls").Worksheets("Template")
i = t.Cells(65536, 2).End(xlUp).Row

View 9 Replies View Related

Maintaining The Color Of A Cell

Feb 2, 2007

if i am copying cell A and pasting it into cell B, and cell B is shaded gray and cell A was plain white, how do I make sure the color of cell B wont change?

View 2 Replies View Related

Excel 2007 :: Date Format While Copying Data From Another Source?

Jul 14, 2013

I have some data coming in from another source system which has the date format as "DD-MM-YYYY" but while pasting it to Excel (2007) sheet some date fields has just DD-MM-YY format which disrupts my macro. I did changing the language settings to "English(UK)" ,which i wanted, and changed the same in Excel options as well. But unfortunately it doesn't work.

View 3 Replies View Related

Maintaining A Sorted List In 2003

Jan 27, 2009

I have an Excel sheet with a list of data, which is likely to change frequently and has to remain in sorted order.

Requiring that users of the spreadsheet maintain the data in this way (i.e. sort it every time it changes) is a really bad solution and I'd rather not use a macro if I can avoid it (too much stuff can go wrong).

Is there a good way of doing this? At the moment, I've implemented merge-sort within a sheet, which works, but is a heavyweight solution, taking 6 columns per merge-sort iteration (so 60 columns to sort 1024 rows, 96 columns to sort 65536 rows). I could try to compact it, but my head was starting to implode programming it even with as few as 6 columns.

There must be a better way of getting Excel to maintain an automatically sorted list.

View 11 Replies View Related







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