If I select lets say cell "A46", expand the selected range from A46 to Q46 and paste that range to another sheet. I know how to do the copy and paste features, but I can't figure out how to expand the selected range. I will always be selecting a cell in column "A" and going to out to "Q".
View 9 RepliesI'm using 2003.
1. Copy cells B5 to V-First blank row in Strength Tests worksheet
2. Paste cells into Racks worksheet in cell C5
3. Change font size to 6
4. Sort by Column T descending then by Column C ascending
5. Copy one row (A5-W5 (1Rx23C)) from Racks worksheet
6. Paste row into M1 worksheet in cell D4
7. Print M1
8. Drop down one row on the Racks worksheet
9. Repeat steps 5-8 until there's a blank row.
This macro works fine on my machine but not with other users:
This should copy/paste certain cells then paste 3 sheets into a new work book.
ON other computers it seems to paste in a picture? works OK for me?
Sub ValidationTests()
'
' ValidationTests Macro
' Macro recorded 21/12/2011 by '
'
Sheets("Score Sheet").Select
Range("A8:M18").Select
Range("H18").Activate
Selection.Copy
[Code] ..........
I would like to implement specific cell ranges from two specific worksheets each within 33 workbooks (which all have several tabs) into a summary page in a separate workbook.
The cell ranges are going across my spreadsheet in rows and I would like for them to transpose into a columns depending on the data which I have separated by catergory on the summary page. They are all on the same location in each workbook which is separated by country. The cell ranges are E26:P37 and I would like to transpose them and have them put below eachother without overwriting for my format on the summary page, how I can put this together in a macro?
Attached is my code, pay attention to the bold part. I want the sourceSheet to be copied as a sheet and pasted in the targetSheet (the Sheet2 of "NewBook") but I want it pasted asvalues. Here is the specific part which needs to be looked at...and below is the full code.
VB:
Set sourceBook = Application.Workbooks.Open(sourceFilename)
Set sourceSheet = sourceBook.Sheets("Current")
Set targetSheet = NewBook.Sheets("Sheet2")
[Code].....
copy/paste Every Sheet Single ( P Column ) and Paste to Notepad and take P1 As file name for note pad.
View 1 Replies View RelatedI have one workbook that needs two macros.
On the "Complete Backlog" tab of my workbook, I want users to enter in the requested information based on the column header. Then I would like a Macro attached to a button that says "Refresh" that the user would click after they have entered in all of the information. This macro should look in Column M (WIP Status) and if any of the cells say "Close", it should Cut the entire row from the spreadsheet(Ex. A2:M2) and Paste it into the speadsheet titled "Closed Jobs".
This is so that as jobs are closed/finished, they are removed and stored on a separate sheet. The items would have to be pasted so that it pastes into the next available row - not just on top of each other.
I also need another macro that i can put into a button that doesn't "delete" a row from the sheet, but just copies over to another sheet - so that there are two instances in the workbook.
If would look something like: If a cell in "Column G / Director" of the "Complete Backlog" speadsheet is equal to "Snodgress" then copy columns A-L of the same row to the spreadsheet titled "Snodgress" - of course skipping down the rows to the next blank row.
.....is equal to "Herr" copy row to "Herr" spreadsheet.
....is equal to "McCormick" copy row to "McCormick" spreadsheet.
and so on.
HTML Code:
Range Apple
A B C D E
1 2 2 4 3
2 1 3 5
3 4 6 9
4 5 3 1 3
5 7 7 7 6
Range Pear
A B C D E
4 1 3 5
5 1 1 1
6 2
7 2 2
8 5 7
Range Apple
A B C D E
1 2 2 4 3
2 1 3 5
3 4 6 9
4 5 3 1 3
5 7 7 7 6
How would you prevent the copy/paste of cells that have comments?
Also, how would you allow cells with comments to be copied and pasted without pasting the comments?
I also have an aside question about the forum advanced search. When searching for multiple search words, how would you type the search to include all words, for example, "prevent" & "paste" & "comments".
I want to copy and paste from one sheet to another based on column a using a macro copy button.
E.g. if column a value = apple then copy that row into the apple sheet.
I want this macro to find in this case "406" in column A which is at the very end of the last block of 160 rows of information.
I then want it to move up 159 rows and copy 160 rows of information underneath the last block of information.
i.e.go to A5280 , then go to a5121, copy rows 5121:5280 to 5281.
It falls over on the very last line of code I can see A5281 selected but it won't paste....
The following sub will look in the file ("FY09 SOF"), in column "A", search for the strings that begin with "2109", "3009", or ends in "-1", and copy the entire row. It will then paste these in the file ("FY09 PR Log Blank").
I have found that in the file ("FY09 SOF"), if things are filtered in any row, it will not copy those necessary items.
The data filter is on row 13 of each sheet. Is there a way of fixing this? (i.e. having the macros select "all" on the filter before copying the sheet? There are 60 sheets so a macros will be necessary.
Sub get_data()
Dim wb As Workbook, wbDest As Workbook
Dim ws As Worksheet, wsDest As Worksheet
Dim lngCalc As Long
Dim FoundCells As Range
Dim FoundCell As Range
Set wb = Workbooks("FY09 SOF")
Set wbDest = Workbooks("FY09 PR Log Blank")
Set wsDest = wbDest.Worksheets("Paste all here, then sort")
With Application
.ScreenUpdating = False
lngCalc = .Calculation
.Calculation = xlCalculationManual
End With
For Each ws In wb.Worksheets.............................
I've received an Excel file with the below macro. However, I've made changes in the original file and therefore, the macro isn't working anymore except if I copy paste special the value instead of simply copying it. I would need to change the macro accordingly.
The macro is the following :
Sub CCtosheetwithoutformat()
'
' CCtosheetwithoutformat Macro
[Code]....
I have a data validation list that has approx 40 items.When I select the cell with the drop down list - only 10 or so items appear . I then have to use the scroll bar on the side to look further in the list. Is there a way that more than 10 or so ( possibly all ) items appear in the drop down box when the cell is selected - or is there a way that one can scroll down using the mouse wheel. ( I have to use the scroll bar at the moment ).
View 2 Replies View RelatedI need help please. Using a macro, I have selected the range C7:C12. Now I need to keep this selection and expand it 18 columns to the right. This would give me a selection of C7:U12.
The original range changes at various times. Some times it might be C7: C452 etc.
I know it's easy, but I can get the code to keep the selection.
I have two ranges. I need to add a row at the bottom of the first range and autofill formulas in the second range using the new added data
View 2 Replies View RelatedI am trying to dynamically manage a named range. As a new column is inserted to the right of the named range, the named range is then expanded/extended by one column to the right.
View 4 Replies View RelatedI have a named range. The range includes a block of cells plus one extra row and one extra column. That is so I can insert a new row or a new column and still have them within the range. The last row and column are simply blank. I am trying to figure out how to select the part of the range that excludes the extra row and column, so I can then do a fill-right and fill-left. It seems like this should not be hard but I am getting all gummed up in the syntax. The last line of the following code excerpt produces a run-time error 13 Type mismatch. It compiles, however. (I have omitted some extraneous lines that have no effect on r or rf.)
Dim r As Range
Dim rf As Range
Set rf = Range("TotalFTEByStaff")
Set r = Range(rf.Cells(1, 1), rf.Cells(rf.Rows.Count - 1, rf.Columns - 1))
Looking to expand grid in Excel 2003,
View 4 Replies View RelatedI've created a spreadsheet in which I want a blank row added when a previous row is filled in any of the boxes. I've used VBA code to insert a row but it just inserts a row in which all the cells are unmerged. I want a blank row the same as the ones we're filling so the document auto expands and there's always a blank row available to fill.
View 5 Replies View RelatedStill finding the SUMPRODUCT function a bit challenging to fully grasp.
This formula works great to sum the miles (Q16:Q38) the team (A16:A38) drove in a particular month (B16:B38)
=SUMPRODUCT(--ISNUMBER(MATCH(A16:A38,INDIRECT(E3),0)),--(MONTH(B16:B38)=E7),Q16:Q38)
How would I expand it to check yet another column say (S16:S38) for the term " Charge Call" ?
So I can see the miles the team drove in March for all the charge calls.
Where can I go to study and learn more about SUMPRODUCT. Seems like just what I need to build interactive type dashboard reports.
I have a cell set up with a formula to copy the content from another cell. However, the cell won't expand to fit the text. If you delete the formula and just type in the cell, it does expand. See attachment.
View 5 Replies View RelatedI am trying to make a spreadsheet for a customer and would like to include a feature where the list of parts can be expanded or compressed with buttons.
Each SO# has a set of PO#'s associated with it, and it would be nice if the viewer can choose to see the PO#'s or not see them at will (this will make a lot more sense if you see the spreadsheet example in my link.)
Ideally, there would be a button next to each "SO#" which would expand/compress accordingly. I don't want just a universal button that would expand/compress all of the SO# at once (although having this option on the side would be nice).
here's link to my spreadsheet: [URL]....
I have about 300 cells filled with different formula's like the ones written below.
=SUMPRODUCT(--('Book 1'!$B$1:$B$1000="H"),--('Book 1'!$C$1:$C$1000=D21),('Book 1'!$W$1:$W$1000))
=SUMPRODUCT(--('Book 1'!$B$1:$B$1000="H"),--('Book 1'!$D$1:$D$1000=I21),('Book 1'!$S$1:$S$1000))
Spreadsheet works great, but I've realized that I should have expanded beyond the 1st 1000 cells (example: $1000). One book 1, my data entry will extend beyond row 1000.
Is there a way I can replace all $1000's on the spreadsheet with say $3000 without having to correct each of the 300 cells separately?
I have a sheet that copies text info (merged and centered cell)from a cell in another sheet (text alt+enter to create more line), the text wrap is on, however i have to manually expand the cell to see all of the text
View 2 Replies View RelatedI have an excel file where I have a grouping of cells. I don't trust some people to expand and contract the grouping via the tiny buttons on the left of the screen as some people using this program will have no prior experience with excel so it needs to be very simple and intuitive.
On clicking a specific cell I would like the grouping to expand if it is already contracted, and contract if it is already expanded. I would also like for this to happen outside of using VB because I hate those enable/disable macro options when you first open excel but I am willing to use VB if necessary.
I suspect I need a class module for this...
How do I trap the event when a user chooses to expand / collapse outlines (columns in this instance) on a worksheet?
Basicallly, when attempting to expand I want to prompt the user to enter a password and then unprotect the sheet. And if collapsing to prompt again and then protect the sheet. I have this bit covered, just not sure how to trap the event.
When i click commandbutton1:
Copy Active cell (columnB), next cell (ColumnC), next cell (ColumnI), next cell (ColumnO) all of which exist in the same row.
I will then paste the values into another sheet in the next available row which i think i have mastered. the cells will be pasted next to each other i.e. instead of column B,C,I,O they will be pasted as column A,B,C,D.
My dilema is i am not sure if you can use multiple cell offsets to perform this or if it is better to use a loop.
Need to expand the crteria on this array?
{=SUM(IF(LYIND1=1,IF(PROFILE!$G$405:$G$435="EOM",(PROFILE!$AU$405:$AU$435),SUM(IF(LYIND1=2,IF(PROFILE!$G$406:$G$436="EOM",(PROFILE!$AU$406:$AU$436)))))))-SUM($K$16:N16)}
I need to add
{=SUM(IF(LYIND2=1,IF(PROFILE!$G$406:$G$436="EOM",(PROFILE!$AU$406:$AU$436),SUM(IF(LYIND2=2,IF(PROFILE!$G$407:$G$437="EOM",(PROFILE!$AU$407:$AU$437)))))))-SUM($K$16:N16)}
I have some working code that makes a number of changes to an excel report using VBA. Each time I execute the code I am taken to a different point in the excel report (depending on user inputs). What I now need to do is to change the cell selection from a single cell to three cells.
For example, my code may result in cell B100 being the active cell. What I now want to do is change the selection from B100 to the range B100:B103. I would have thought I could write something simple like:
Sheets("Group").Range("ActiveCell:Offset(0, 2)").Select
I have tried every variation of the above but I can't seem to make it run. Where am I going wrong?