how you would write a macro to move a selection of sheets to a workbook called Digi's in the D drive. Also if there isn't a Workbook called Digi's already it needs to add one. I've found some code to loop through sheets but nothing to show what sheets the user has selected
View 4 Replies
I am using this code to copy columns C,D, and P from a user selected workbook into position C,D,E of workbook "Checklist.xlsx" (the one from were I run the VBA code). I don't have a clue about VBA, just have been gathering info, but the code works pretty fine. The only issue is that it opens twice the workbook "wbExt" hence the system prompts me if i want to reopen the already open workbook.
Some questions:
1) Is there a way to copy the 3 columns at once? something like "Columns("C:D, P").Select".
2) Is there a way to select the workbook from were to copy the column without needing it to actually open?
3) is there a way to paste the columns starting from row 3 instead of row 1 (Range("C1").Select)?
I use the command ActiveWindow.SelectedSheets.Copy to copy selected sheets into a new workbook. As a result of this line is a new workbook created and a the selected sheets are pasted into the new workbook. Is it possible to paste only values by using a single line like this?
View 2 Replies View RelatedI have a workbook with about 25 sheets. All the sheets are named. I'd like either a Macro or some VBA code whcih, when the user clicks a button will Unhide selected sheets, say "Equip Labor", "Equipment", Proj. Summary" and "Implementation Guide" and then copy these sheets into a new workbook created on the fly (Book1.xls) and then in the original workbook re-hide all the selected sheets. This way the user can save the new workbook as whatever name they want.
Is this possible? The closest post I could find was this:
http://www.mrexcel.com/forum/showthr...opy+Worksheets
I tried to modify this, but I'm not that good yet with VBA.
How do I copy the selected cell to another worksheet instead of sheets("Interior") ?
Code:
Private Sub OptionButton1_Click()
With Range("B19")
.Font.ColorIndex = 3
.Copy Destination:=Range("P19")
Application.Goto Sheets("Interior").Range("C20")
End With
End Sub
I had a macro on my previous laptop that worked, but didn't have the macro backed up so it was lost.
I need a vba macro that will copy all active/selected worksheets into a new workbook. Included requirements:
* Maintain tab names
* Only bring in contents/data with all formats (including logo image, but NOT formulas or hidden rows/columns)
* Keep the file name the same except adding "- FINAL" to the end
What i want to do is copy all records from whatever date i enter, onto sheet test. The full excel file has over 80 worksheets for each individual rep, the example i attached has 8 sheets..
View 9 Replies View RelatedI currently have a macro that takes a user selected name and date and creates a time sheet for the selected name. This works just fine, however i have to select each individual person and run the macro for each person, how to automate that part of the macro, so it will automatically create the said sheet for all person at once, rather than me doing it manually.
[Code] .....
The 'CurrentMonth' variable is user selected from a list, and will remain that way.
I have a form that asks at what row the user wants to start with a selected range then how many rows to select. There will always be 21 columns selected with this range. I am still learning but it has to do with something on how I am setting the Start object. It is not being recognized.
Dim Row As Long
Dim Selection As Long
Dim Start As Range
Row = txtStartRow
Selection = txtHowMany
Set Start = Range("A" & txtStartRow).Select
Range(Start & ActiveCell.Offset(Selection, 20)).Select
I would like the user to select two files, the macro then inputs a formula that includes vlookup to the other sheet. However i am not sure how to reference each workbook.
Sub InsertLocationContents()
Dim rng As Range
Dim LastRow As Long
'OPEN CSV FILE WITH LOCATION CONTENTS
csvFN = Application. GetOpenFilename(Title:="Select Location Contents csv file")
If csvFN = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
Workbooks.Open Filename:=csvFN
Workbooks.OpenText Filename:= _
csvFN, Origin:=437 _ .............................
Within a worksheet, I have several radio buttons, allowing the user to select only one of them. Using VBA, how do I recognize which radio button the user selected?
View 1 Replies View RelatedOn the first row of a spreadsheet template is a <hidden> contiguous series of conditionally formatted cells (range name "stdRow" =production!$1:$1), with formulas, that I want a user to be able to easily insert at whatever row they might be in a worksheet.
If, for example, the user is at D24, then clicks the [InsertRow] button that I have positioned at the top of the worksheet (in a fixed pane), I want the attached macro to insert an instance of "stdRow" directly underneath the user position (at row 25, in this case). The use's position should still be at D24 when the macro finishes. If the user clicks the button multiple times, multiple rows should be inserted (again, without changing the user's position).
This is what I have tried so far:
The User has made multiple selections with the mouse. The spreadsheet is filtered. The user will usually make different multiple selections on the following columns: A and X through to AR (inclusive).
I just need some code to capture these various multiple selected ranges so that I can copy the selected range as shown below:
I want to do a concatenate for multiple fields, but want the user to specify which cells should be concatenated by entering this in a few fields. What does this formula (or VBA) look like?
Let's assume there is the possibility to concatenate a maximum of 4 cells. Call the 3 cells that will be concatenated cell1, cell2, cell3 and cell4.
On the first sheet of the workbook the user enters the following:
cell1 = Sheet2!A4
cell2 = Sheet2!B4
cell3 = Sheet2!C4
cell4 = Sheet2!D4
Here are some cells and their values:
Sheet2!A4 = Hel
Sheet2!B4 = lo
Sheet2!C4 = World
Sheet2!D4 =
Upon enterring the information after the = sign for the cell1, cell2 and cell3, the formula for the concatenate becomes: =CONCATENATE(Sheet2!A4, Sheet2!B4,Sheet2!C4 ).
The result of the formula is: HelloWorld
If I now change the values of cell1, cell2, cell3, and cell4 the following should happen:
cell1 = Sheet2!B4
cell2 = Sheet2!C4
cell3 =
cell4 =
The formula for the concatenate becomes: =CONCATENATE(Sheet2!B4, Sheet2!C4).
The result of the formula is: loWorld
I'm trying to create a macro that allows users to hide or unhide selected rows, either via toggle or userform. The macro I have looks like this, but for some reason it doesn't hide the rows:
Sub Hide_Range()
Dim UserRange As Range
DefaultRange = Selection.Address
Set UserRange = Application.InputBox _
(Prompt:="Select Range to Hide:", _
Title:="Hide Range", _
Default:=DefaultRange, _
Type:=8)
Rows.Select
Selection.EntireRow.Hidden = True
End Sub
what's wrong with my code and how I can further develop it to let the user decide whether to hide or unhide the selected rows?
I am trying to display number of lines which depends on value pass by user. Actually user is passing some value. on which some group of lines has to be display.
eg
for 1 value 9 lines
for 2 value first 9 lines + another 9 lines
for 3 value first 18lines + 9 lines
and so on till 52.(this 9 lines are set of some column and rows)
thus cant use macros..
I am trying to write this macro so that I can optimize 181 outputs based on 2 inputs (columns A and B are inputs). Each row has a single output at the end of the row that I am trying to minimize. I want to be able to select as many of the rows that I want and have the code optimize each individual row. So far I can't get it to let me use variable cell references for the "ByChange:="$A$3,$B$3"." This is the hang up that is not letting me increment the macro to the next row. The macro works currently for the single selected cell, but until I can vary the ByChange portion, I can't automate it.
View 1 Replies View RelatedHave a person Input an Assembly Number and,Have Excel return a list of Part Numbers, their Descriptions, and a Quantity for each Part Number that will need to be packaged with the Assembly Number that was Input.
The output needs to be in the form of a check sheet (I can play with the formatting if I can get the information out) for the one Assembly Number input.The output can either be in a separate spreadsheet or a User Form. The spreadsheet is easier for me to work with but the User Form looks cooler.
I can use a VBA Function to Input the Assembly Number but then I need some way of searching a small table (about 20x20 cells) to find the matching Assembly Number and then return the Part Numbers, etc. to a check sheet so the parts can be packaged. Each Assembly Number can have several Part Numbers associated with it. Either unique to that Assembly Number or common to other Assembly Numbers. The Quantities can also be unique or common. A couple of simple examples might be:
Assembly Number - ANA0134
P/N Description Qty.
ANA0118 Widget thing 2
ANB0554 Thing Widget 4
Assembly Number - ANA0431
P/N Description Qty.
ANA0118 Widget thing 3
ANB0554 Thing Widget 4
ANC0023 Plain Widget 1
I have a macro that works perfectly to import a text file and parse it. Now i need it to do multiple ones. I import the txt file to a new workbook as i filter for certain data only and if found i copy that to the current workbook. I want to do the same just for many txt files:
[Code] .......
how can I provide a chart that display as a line chart twelve months of data for the item selected starting or ending on the data that I have selected
View 4 Replies View RelatedI have a workbook that has three worksheets. The ASU Database sheet contains part numbers and descriptions including specific details that are selected from pull down data validation lists that are named on the third worksheet. I want to have a front end on the first worksheet that has similar data validation lists for the user to select from to create a search criteria that will create a worksheet with only the rows that meet this criteria. I am not sure what method to use to accomplish this.
View 2 Replies View RelatedI am creating a userform in Excel 2007 which requires a user to pick their name from a drop down box then press Ok, what i want to do is disable the Ok button until the user field has been selected.
View 1 Replies View RelatedI have a worksheet that has data for separate sheets for each date of the month.
Therefore, the month of January has sheets for 31 days.
I need to sum a particular cell in each sheets only for Monday.
Therefore, I need the formula to sum cell B4 only for the sheet for Jan 7, Jan 4,Jan 21 and Jan 28.
I'm probably asking something very complicated in coding but I
was hoping someone could give me a macro code that would do a search
for a cell's input, but only search selected sheets.
example: "Please enter the word you are searching for here, and click the enter button:"
'here' would be cell C20. the value of cell C20 would be searched for on various
selected sheets written in the macro only.
how I can change this code to apply to only the sheets that are selected (grouped) as opposed to every sheet in the workbook?
For Each shName In ActiveWorkbook.Sheets
The following code inserts a row below the selected row, and copies the formula of the row above into it.
Dim Rw As Integer
Rw = ActiveCell.Row
Selection.Insert Shift:=xlDown
Rows("" & Rw - 1 & ":" & Rw - 1 & "").Copy
Rows("" & Rw & ":" & Rw & "").Paste
However, I need to alter this to work for inserting more than one row at a time. ie. the user selects 'x' number of rows and 'x' rows are inserted below (in the same way 'Insert Row' works in Excel) and the row above the selection is copied down.
in creating a macro so that I can print from sheet 1 to sheet name "XYZ" as an array. In other words I want to print selected sheets as one command so that page number in the footer will automatically change.
View 14 Replies View RelatedI'm looking for a string of code that will prompt the user to check-off boxes specifying the sheets they'd like to print, then have it save into a SINGLE pdf file. I'm using the below code right now and it will prompt check-boxes, and print using PDF but it does it one sheet/one PDF at a time. Any way to mod this so it will combine and save into a single PDF?
Sub SelectSheets()
Dim i As Integer
Dim TopPos As Integer
Dim SheetCount As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As CheckBox
Application.ScreenUpdating = False
[Code] ........
I am trying to set up something similar to the old "print dialogue" box whereupon the user presses a command button on the userform and all sheets within the work book are listed (eg:sheet1, sheet2 etc) The user can select any amount of sheets and and using "OK" outlook is fired up- pretty similar to Ron de Bruins code for email.
I have tried taking the print dialogue vba and stooping it at copy, then trying to gets Ron's code to continue- without success.
There are great bits of code I've seen for select tabs and selct sheets from listbox, but I have found none to email once selected.
I was wondering if there was a way to Refresh Queries on a sheet by sheet basis. I have several sheets of queries, and I would like to refresh all of them except one or two.
The only way I know how to do this is to stick a line of code for each query into the script while leaving out the queries I don't want. I was hoping there was a more elegant way to do it. If not, no big deal. I'll just do it the old fashioned way.
