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
I am copying a range of rows (which include some hidden rows). When I paste them I see all the rows. Is there a way to retain the hidden attribute?
View 2 Replies View RelatedI am working on a file with different users entering information on different sheets (tracking the status of individual projects and activities). Using the macro below (which I found in the forums), the individual data will be combined on a master sheet. My problem is that some of the users will be using autofilters to filter their own data (for example--filtering to show only the "Open" projects). When I do the copy of the individual data, I want ALL of the data, not just the data that is being shown by the individual filter settings.
At first, I just turned the individual filters off before copying the data. The other users have asked if there is a way around this or alternatively, restore the filter values after the copy.
A key point, there are currently 8 user sheets. Each user is allowed to set their own filters and change them at will-e.g. one day, a user may filter on projects due in Q3, another day she may be looking at all projects closed in 2013.
VB:
Sheets("Combined").Select
ActiveWindow.SelectedSheets.Delete
Dim J As Integer
On Error Resume Next
[Code] .....
So i have sheet 1 with some cells locked and protected (but open to copying) as a template and sheet two will be where people paste one or more of the template formula on an ongoing basis. How do i mantain the locked and protected cells after they have been pasted? I have unlocked sheet two pasted the first template and had to choose protect cells after pasting, and further pasting of locked cells are not protected.
View 1 Replies View Relatedim looking for a basic macro for workbook copying, all workbook(all hidden, unhidden, and very hidden) sheets.
i know how to do unhidden sheets of course however the hidden and very hidden are giving me some trouble...i need to also copy over all the macro's, buttons, and drop down box's as well..not just values.
I have the following code (which works fine when the sheet isn't hidden)
Code:
FinalRow2 = TtlRev.Cells(Rows.Count, 1).End(xlUp).Row
TtlRev.Range("AM6:AO6").Copy
TtlRev.Range(Cells(6, 39), Cells(FinalRow2, 41)).Select
ActiveSheet.Paste
However, I need to hide the sheet, but it doesn't work because you can't use .select on a hidden sheet I believe. Any way of doing this to a hidden sheet?
How do you crack a protected spreadsheet other than copying the tab and pasting? Someone protected a lease abstract by mistake and now we cannot make changes.
View 1 Replies View RelatedOk I recently ran into something in Excel that was completely unexpected. When I filter data, and select multiple rows of data, and copy and paste it.... it pastes the unfiltered rows as hidden rows! Is there a way around this without copying row by row?
View 1 Replies View RelatedI want to copy the current region on sheet 3 and paste that into sheet2 starting with cell E4. But I don't want to actually use the copy method. I believe there is a way to do this. I was thinking the following:
[Code] ......
The idea I had was to simply state that cell E4 would be assigned the value of CurrentRegion on sheet 3. Is there a way to bypass the copy method?
If not, how to copy, then paste?
I have a problem with a spreadsheet I am working on, and I'm not sure if its something I've done, or whether its corrupted.
Basically, Column A is Hidden and not Locked in the protection tab of formatting. The other columns are Locked and not Hidden.
I used to be able to use the arrow keys to scroll through Column A, but this now doesn't seem to work. I can select the cells within column A with the mouse and I can scroll with the mouse and the scroll bars, but I can't move between the cells with the arrow keys, enter button or tab button.
Does anyone know what might be causing this, or is the workbook corrupt?
I have a userform which clones the latest sheet and produces copy of it on the next sheet. The first sheet they will be cloning is the sheet called 'template', I however want this to be hidden since I do not want anybody to modify a sheet which basically serves purpose of a template. And once cloned, it is no longer needed.
On the click of the command button, the macro will create sheet1 taking the info from sheet named 'Template' in the same workbook . And now on the next click of the command button, Sheet 2 is created taking the info from sheet1 and Sheet 3 is created taking the info from Sheet 2 and so on. Here is what I currently have, so how can i modify it in order for my scnerio to work?
To select the last sheet in the workbook
Code:
Sheets(Sheets.Count).Select
To create new sheet
Code:
Sheets(Sheets.Count).Copy After:=Sheets(Sheets.Count)Sheets(Sheets.Count).Name = MyEvent & " " & MySCN & "(" & ThisWorkbook.Sheets.Count - 2 & ")"
i have created a table. when i m editing new row then formulas is copying bu value is not copying ?
View 3 Replies View RelatedI have a worksheet with columns as follows:
col A Name
col B Category
Col C Subcategory
In column A every row has a name, but in col b and c only the first row of a category or sub are popluated, for example:
Name CAT Sub
a 1 1
b
c 2 2
d 3
e
f 3 4
g
h
As I work with many of these spreadsheets, they are all different in respect to number of products(name) and number of CAT and SUBCAT.
I have a list of names in column a, and in column b either a "0" or a "1"
as part of a longer piece of code I need to be able to copy the names that have a "1" next to them over to column c
I need help with copying some data from one sheet to the other, but I am really bad at explaining this. Can I send the excel sheet to someone and then explain it? I think that will be easier, cuz Ive tried explaining it on another forum and nobody understood what i was saying without being able to see the sheet.
View 9 Replies View RelatedI'm using Excel 2003, Windows XP, and Microsoft Outlook. I'm trying to copy some cells, which include merged cells, from Excel into a new e-mail using Outlook (the output of some calculations), but every time I do this, Outlook seems to unmerge the cells.
When I paste into Word, the cells remain merged; I can then copy/paste from Word to Outlook with no unmerging occurring. what's going on and/or how to resolve this issue so I can copy things into Outlook straight from Excel?
I want to be able to have a range selected and copy the cells offset it it at (0,-2),(0, -4) and (0,-7) all at the same the to reduce the macro time.
View 1 Replies View RelatedI need to start from its most basic so i can see what the macro is doing & then adapt.
See attachment.
This will take the form of a button to be clicked on in sheet1.
Sheet1 is my input sheet, sheet2 is my history sheet.
I want to copy the cell range B4:F9 in sheet1 into sheet2 - starting from cell C3.
Next time i click the button, copy the cell range B4:F9 sheet1 into sheet2 again, but underneath the data already there in sheet2
Each time i click the button it just keeps adding the data in sheet1 into sheet2, to form a history.
I'm trying to copy cells from one sheet to another. I know that if I know what cells I want then I can use something like this:
View 9 Replies View RelatedI am creating a macro to copy data from one workbook that is emailed to me to another workbook that contains the actual macro, I will be using the second workbook to import the data into an application.
What I need is to be able to copy text in cells say D1, D3, D6 etc located in workbook1 to cells say A1, A2, A3 etc in workbook2 without having the macro go back and forth each time to copy the cells one at a time.
Also is there a macro that will recognize an already open workbook that I can use to copy the data
I just need to know how to reference another workbook in order to copy values from cells on it to my own version of it. It's 18 pages, and I need to pull certain columns cell values from certain sheets (these columns are all fixed, no fancy statements required). I'm unfamiliar with the structure required to refer to another workbook. All I need to do is say "This workbook-Sheet1!D2:D20 = Workbook"Feb"-Sheet1!D2:D20", and so on, throughout the sheets.
I did look for solutions to this, but all of the answers are mixed in with complex statements for other simultaneous calculations... I find myself unable to pick out the small piece which I need from the mash of code I'm unfamiliar with.
I would like to be able to copy a group of cells from sheet 1 to sheet 2 or 3 based on a value in another cell on sheet 1 eg
sheet 1
If Cell A1 = 24, A2=London Road (this make up an address), A5= omit
If A5 = omit I would like the address copied to sheet 2
If A5 = Complete I would like it copied to sheet 3
I have 1600 address to go through.
I have a spreadsheet like that attached where there are titles down Column A, and every second column from B on there are data entries I wish to copy to the other alternate columns. For example B1:B2 into C1:C2, D1:D2 into E1:E2. I have an example of how it is and how I wish it to be in my attached spreadsheet. This is my penultimate dilemma in the spreadsheet I've been working on for a few days, excited to nearly be there!
View 4 Replies View RelatedI am using a macro to copy a variable column of data from one file column A:4to another but what I want to do is copy the column apart from the last 2 cells. I am using:
View 2 Replies View RelatedI would like to copy a range of cells with a marcro, for example copying range C3:C15 over to D3:D15 and when I run the macro again I want range D3:D15 to copy to E3:E15 and from there to copy E3:E15 to F3:F15. I want to keep that going to the next and the next with the same macro. But it just keeps copying the the original range which I recorded in the macro, but I want it to keep going. If somebody can help me with that, I would sure appreciate hearing from you.
View 8 Replies View Relatedi am trying to copy specific cells for mutiple worksheets and past them into a summary page. i can get that to work but not all the cells on all the sheets are populated and i cant get the blank cells to be pasted to the summary sheet so that each sheet has 31 entries.
View 6 Replies View Related1) The following code works well except for 1 wierd circumstance. This UDF code resides in a Cell in workbook 'A'. If I have another workbook open (call it workbook 'B'), and I do ANYTHING in workbook 'B' (like copy a cell to another cell within workbook 'B', or perform a calculation within workbook 'B', or even copy a cell from 'A' to 'B'), then the cell that this UDF resides in (workboook 'A') will change from a good number to a #VALUE error. If I work ONLY within 'A', then this UDF functions properly (giving a good result).
2) If I hit the F9 button (i.e. re-calc), with workbook 'A' active, then the #VALUE goes away, and the good number comes back. To clarify, all workbooks have AUTO CALC enabled. I guess the 'Application.Volatile' line causes this code to run when I hit the F9 button (thereby clearing the #VALUE error). If I hit F9 with 'B' active, then the #VALUE error (back in 'A') goes NOT clear.
3) NOTE #1: If I put a BREAKPOINT inside this code, and then repeat a "copy from cell to cell" within workbook 'B', I find that the following code DOES NOT execute, as it does NOT "stop" the code at the BREAKPOINT line. But, when I hit F9, then it DOES stop at the BREAKPOINT.
4) NOTE #2: If I "comment out" the Application.Volatile line, it still does the SAME thing. Except worse, then when I hit F9, it doesn't correct the #VALUE error. I really need the Application.Volatile step with this code, as these functions will not automatically update if their referenced cells are changed.
5) NOTE #3: The variables Row_Num & Column_Num are declared at the Module Level. If I use unique variables and declare these at the Procedure Level, it makes NO difference. I also tried putting a "DoEvents" line after Application.Volatile, but NO difference.
6) It seems I need this code to execute whenever there is a "change" on ANY workbook. Or else, do something to keep these cell values from changing anytime there is "action" on another workbook. Or, is there better code to do the samething? ... Do you have any CLUE how to fix this SNAG?
I've got a macro with copies certain data from 1 sheet to the next. The problem I have is that some times there is an autofillter on the data.
I think I need to add .SpecialCells(xlCellTypeVisible) in to the code some where but im not sure where.
my current code is as follows. This is only a part of the code. It also copies other columns across to the other sheet
Code:
Sub CopyPasteMacro()
'
With Sheets("Imprint Data")
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
Set rngToCopy = .Range(.Range("B6:B" & LastRow), .Range("B6:B" & LastRow))
End With
rngToCopy.Copy
Sheets("To Key @ TE Imprint").Range("D4").PasteSpecial Paste:=xlPasteValues
End Sub
i am trying to copy few cells with data, and i have something like this:
Range("A3:AO" & Format(intRowCounter, "###")).Select
So this will copy all data withing those cells range, however, i want to copy only data from specific columns, ie, from column A3, and from K3:J
I am trying to do a loop to go through all the sheets in a workbook (11 ina all) that will copy all the cells and paste special value. below is the code i am trying to use and it is giving an error.
For Each sheet In ActiveWorkbook.Sheets
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next sheet
