i have this code at minute to copy information from all my sheets to a summary sheet , is there a way i can modify this code to copy from row 6 to 35 if there is a value in col b on sheet then copy a:bq of that row to summary sheet carry on to end of data , then on next ws copy row 6 as this is machine heading and then same again and loop through all worksheets i have in my array ....
View 9 RepliesIn effect the code looks at sheet 'data' and creates new tabs depending on the contents of column f and then pastes data from the entire row into those tabs as appropriate - this is what I asked for and works wonderfully. What I would like it to do is to also copy the format of the row that it is copying. (most importantly the cell borders and colours)
I can (I think) identify the copy and paste elements of the code but have no idea what if anything I can change to make this work.
The code is as follows
HTML Code:
Sub CopyData2()
application.ScreenUpdating = False
Call Sample ' this inputs data into column f
Dim rCell As Range
Dim lastRow As Long
[Code]....
I have a chart with coloring for specific series sets. When I create a similar chart, can I copy the formatting from one range to the other? Why does it default to certain styles?
View 1 Replies View RelatedI am attempting to copy some numbers from one spreadsheet to another including the formats. The format I am using is a user-defined one which doesn't normally appear in the list of personalised formats.
My code seems to work fine within the same spreadsheet but fails when I do it using 2 spreadsheets.
The problem: I am getting values when doing a pastespecial for formulas and formats.
Manually copying and doing a pastespecial with the mouse gives me the correct results. Below is code I got from the forum, "here", and am running in a test workbook.
Option Explicit
Sub Test2()
'
Dim rSource As Excel. Range
Dim rDestination As Excel.Range
Set rSource = ActiveSheet.Range("A1:C1")
Set rDestination = ActiveSheet.Range("A1").End(xlDown).Offset(1, 0)
rSource.Copy
rDestination.Select
Selection.PasteSpecial Paste:=xlPasteFormats, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormulas, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
valKill:
Set rSource = Nothing
Set rDestination = Nothing
Exit Sub
End Sub
Does anyone know why I am getting values? What do I need to do to make this paste only the formats and formulas?
For some reason the following code, when executed, pulls a named range from the source workbook (sProj_Name). I feel like I have used this exact same code before and not encountered this issue. This named range is linked to the source workbook so if I save the file and re-open I get the wonderful 'would you like to update links' pop up. I cant figure out why, when I am forcing it to paste only values and formats, it is picking up this named range. In addition the source workbook has way more than one named range, so why it doesn't pull all instead of just one is a mystery to me as well.
VB:
With Workbooks(sProj_Name).Worksheets(2)
.Cells.Copy
Sheet2.Cells(1, 1).PasteSpecial Paste:=xlPasteValues
Sheet2.Cells(1, 1).PasteSpecial Paste:=xlFormats
Application.CutCopyMode = False
End With
Here's my problem..
if i input 75 in cell G2, the value in A7:E7 will paste or will display in A2:E2
or
if i input 76 in cell G2, the value in A8:E8 will paste or will display in A2:E2
or
if i input 90 in cell G2, the value in A9:E9 will paste or will display in A2:E2
or
if i input 78 in cell G2, the value in A10:E10 will copy or will display in A2:E2
See my attachment.. sample prob.xlsx‎
I have a sheet used to process sales orders - about 500 orders per month x ~1.5Mb each. I want to save them as smaller files.
The sheets to save are full of vlookups linked to external sheets and tabs, other formulas, and macros - most of which do not need saving when the sales order is filed. There are lots of merged cells too.
I must save:
1. Cell values
2. Cell formats incl merged cells, borders, colour, font etc.
3. The row and column sizes
4. Print set ups - print area, margins, page setup, header footer etc - (Everything needed to reprint to same as original)
I think I need a "File save as" style Macro which opens a dialogue box for the user to nominate the destination folder (& allows the user to browse for it), and a new file name.
The original file name is "Job Sheet - Omlaw.xls"
The tab/sheet to save is "Front Sheet"
(There are two other tabs - neither of which need saving.)
All cells to be changed to "locked".
The sheet must be password protected in Excel. User to enter it - or not.
The saved file should be "write protected" if possible?
my macro pulls download in on sheet1. On sheet2 it makes a pivottable of it with horizontally displayed the suppliers and vertically the codes of products. This is of course dynamic (one month it may contain 10 suppliers + 8 products, other month 15 suppliers + 20 products). On sheet3 is the (static) lay-out of all suppliers and all products. Now, what I want is that all fields <> empty (or zero) from the pivot table are to be copied and placed in sheet3, the 'report' I have to fill in. index and match won't do the job I think.
View 2 Replies View RelatedI would like to link a macro to a button on the active sheet, that saves the active sheet In a New Workbook, with Format And Values Only. Preferably saved in the same location as the origonal workbook. This is my main goal.
If possible I would like the new workbook to be named from a chosen cell (E19) on the active sheet with date added.
1. In whatever cell is selected when the macro is run, enter a new row.
2. Copy the information from the row directly above the new row and paste (values, formulas, formats, etc) into the new row.
3. Return to column P in the new row, i.e if the new row is row 11, then return to P11, for row 12 return to P12, etc.
I have tried recording the macro but because it is hard coded to specific rows, its not working. I have attached a sample copy of the sheet (had to zip due to the size of the file).
What I would like to do is copy the format of a range of cells (A2:O2) and paste that format into all the other rows of data beneath it. But my last row of data will change periodically, and I don't know how to tell Excel to find the last row each time. Here's what I was able to figure out on my own:
Code:
Sub CopyFormats()
Range("A2:O2").Select
Selection.Copy
[Code]....
I have a workbook that has a lot of formulas in it that reference material stored on my local computer.
I need a macro that will copy all 42 tabs in to a new workbook book without all formulas, but saving all the formats have made. This would save me from every other day coping and pasting manually each of the pages.
I have used the formatting option to make numbers that once appeared as 1, 2, 3, 13, etc into four digit numbers, to appear as 3001, 3002, etc.
The problem is, I have other data whose original values are the 3000 formats, and Excel won't combine the two or sort them correctly.
Is there a way I can format it to change the actual value?
I have these values in one column
706
706
706
512
512
12
12
12
12
In an another column I have same values and in the next column some more values that correspond to the previous values
12 red
512 blue
706 orange
I need a formula that can automatically copy those text values according to number values.
706 orange
706 orange
706 orange
512 blue
512 blue
12 red
12 red
12 red
12 red
I have tried =lookup and some variations of =if, but no good result so far.
I have a command button. When the user clicks it, it needs to dynamically refresh the data in Excel.
I have a SQL server running on the backend. I have been successful in establishing a connection between the Server and Excel and am able to query the data required for me.
FyweekProductRegionHandledAbandonTransferred
These are the columns in Excel. Now i need to start copying from SQL to these fields row by row till the end of the record set
I need VBA Code that will copy numerical values (including formulas) i.e excluding text in column J and to paste these as values in column F.
Basically what I need is to find a row (I'm doing it by matching the row title that is 'BALANCE'), then I need to find the max value in this row (the problem I'm having is that the row number is not constant), copy that value in another cell and copy the name, that is located in the same column, next to the previous copied value.
View 2 Replies View RelatedI'd like the following code to do is to go through each worksheet in the workbook and copy the value of the formula in cell S2 down the S column to the last row based off of a count of rows in column B. It's not working quite right and was hoping someone would be willing to correct it.
View 4 Replies View RelatedI have a file where I have few sheets (link to file below). Sheet zrzut is dropdown from other system - updated daily. Sheet Productivity is filled with data supporting to group data and add conditions (for example column "I" in "zrzut" sheet). Sheet "Vena" has data from "zrzut" based on condition in column "I". Sheet "VENA_PLAN" has the same data as "Vena" but only values - I need only values for other actions. The thing is my current code replaces old data with new, but what I would like to achieve is to have new data stored below old. For now code looks like
Code:
Sub PlanVENA()
Application.ScreenUpdating = False
Worksheets("VENA_PLAN").Range("A3:H300").Value =
[Code]....
I have a cell "A1" that changes values. Every time "A1" gets a new value I want to copy it the the cell below the last value in Column "B". Assume "B1" contains a column name.
I want to copy A1 to B2, then copy A2 to B3 when A1 gets a new value.
Then I want to copy A1 to B3 when A1 gets another new value and so on.
I have a "weekly"workbook that is link to 26 workbooks the weekly sheet updates percentages from the other workbooks. What i am tring to do is I have a range "S9:S35" that pulls the percentages from the other workbooks I need it to copy the value of that range to "T9:T35" only when promted too(msgbox) in both cases. So that way if a workbook gets opened it doesnt automaticlly update "s9:s35" and "t9:t35" does'nt update when weekly workbook gets opened.
View 9 Replies View RelatedI have a large list of data including subtotals. I want to copy and paste just the values of the subtotals to another worksheet. I have tried collapsing the list to Level 2 and copying/pasting values but this still carries over the whole list.
View 3 Replies View RelatedI have a Sheet in which the data is calculated by changing the Sheet reference and also by the period..
Now I need to transfer this data as values, however there are certail cells which are merged and therefore I get a prompt as I cannot paste as values using Paste Special-> Values..
I have a pair of workbooks in which one calculates a number of values and I want to store the values only in the other.
I would expect to be able to highlight the range of the calculated cells in the source workbook, Copy and then Paste Special | Values in the other.
Both the source and the target contain some merged cells. When I try to do the paste, it complains that "this operation requires the merged cells to be identically sized".
They are identically sized.
I have even done Paste Special | Column Widths to guarantee they are identically sized. It doesn't complain about the merged cells then; in fact, it doesn't complain about the merged cells with any of the other paste operations, only with Values.
I have been able to repeat the behaviour with a very simple example. See the two attached sheets. Try pasting Values only of the range $B$2:$D$4 from either one to the other.
When I first created the samples they worked. What seemed to triggered it is I changed one of the merged column widths by one pixel, tried expecting it to fail (and it did), changed that column width back again and it hasn't worked since. As I said, I even tried pasting the Column Width to the target, which it appeared to do successfully, but it hasn't helped.
Now, the attached examples are only to show the principle of the problem. They are very simple and there would be a myriad alternative work-arounds for them. In my original case though there are many formulae over a much larger range and I want other people to be able to paste values without having to follow complicated instructions, so I am not looking for work-arounds unless someone has a mind-numbingly simple one that my numb mind has overlooked.
The only other way of solving the problem that I can think of would be to write a macro that copies cell values individually, but I'd rather not do that because I know these people: they will get narky about having to enable macros. It will be "against organisation policy" or something.
Is there any way of resetting the target workbook so that it will receive the values from the source?
I am using Excel 2007.
In cells A1 and B1 I have numerical values, e.g. 50 and 12 respectively. Cell C1 calculates the product A1*B1 (= 600). In another cell, say D1, I want to display a statement like “50 x 12 = 600” with the 50, 12, and 600 taken directly from cells A1, B1 and C1, without re-entering them. If I change the 50 to another value, say 70, then I want D1 to read “70 x 12 = 840”
I'm working on a project requiring the matching of data from multiple worksheets and got some useful advice from someone on here a few months ago to get it up and running.
At the moment I'm using SUMPRODUCT with an IF argument to check rows on one sheet for matches on 5 others and return a "Yes" or "No" value, which is working fine. The next bit of analysis however requires copying the value from another column on rows that match, and pasting it into the first sheet.
I have a sheet with a lot of different data. In column M in workbook "Maximo report" there are 5 different values. I want to copy the rows that contain CDC_HVAC and that have the value "INPRG" in column G to the sheet HVAC_INPRG. I want to do this for all 5 values. Once the rows are in their respective sheets I want to filter them based on a formula and then sort from oldest to newest. I dont want the file to be too big and I think that VBA is the way to do it. I am new to VBA
View 6 Replies View RelatedI am trying to copy a set of data (lets say workbook 1) into a new workbook(workbook 2). Both files are saved in the same directory.
Workbook 1 will be continually updated with new row additions and some changes in text in the rows. I want to copy these new changes (from workbook 1) without having to copy and paste the new additions into workbook 2, as it is wasting a lot of my time.
Is there a trick in excel to automatically do this?
I have the following macro which works fine accept for the fact that it copies the formulas from other worksheets, where as i only want to copy the values.
View 3 Replies View Related