Computer Support Forum

Excel Macro to create new worksheet in Excel 2010

Question: Excel Macro to create new worksheet in Excel 2010

With due respect and credit to DerbyDad03 whose earlier post gave me the code below; is there any way to have the new worksheets created from a variable length field e.g. a persons name in a list or a field of fixed length other than a date as the script below uses?Sub CreateMonthlySheets()Dim lastRow, mMonth, tstDate1, tstDate2, shtName, nxtRowOn Error Resume Next'Turn off ScreenUpdatingApplication.ScreenUpdating = False'Make a copy of the data sheet and sort by date Sheets("Sheet1").Copy After:=Sheets(1) Sheets(2).Name = "SortTemp" With Sheets("SortTemp") lastRow = .Cells(Rows.Count, 1).End(xlUp).Row Rows("2:" & lastRow).Sort Key1:=Range("A2"), Order1:=xlAscending 'Using SortTemp Sheet, create monthly sheets by'testing Month and Year values in Column A'Loop through dates For Each mMonth In .Range("A2:A" & lastRow) tstDate1 = Month(mMonth) & Year(mMonth) tstDate2 = Month(mMonth.Offset(-1, 0)) & Year(mMonth.Offset(-1, 0)) 'If Month and Year are different than cell above, create new sheet If tstDate1 <> tstDate2 Then ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count) 'Name the sheet based on the Month and Year ActiveSheet.Name = MonthName(Month(mMonth)) & " " & Year(mMonth)'Copy Column Widths and Header Row .Rows(1).Copy ActiveSheet.Rows(1).PasteSpecial Paste:=8 'ColumnWidth ActiveSheet.Rows(1).PasteSpecial 'Data and Formats End If Next On Error GoTo 0 'Loop through dates, copying row to the correct sheet For Each mMonth In .Range("A2:A" & lastRow)'Create sheetname variable shtName = MonthName(Month(mMonth)) & " " & Year(mMonth)'Determine next empty row in sheet nxtRow = Sheets(shtName).Cells(Rows.Count, 1).End(xlUp).Row + 1'Copy Data .Range(mMonth.Address).EntireRow.Copy Destination:=Sheets(shtName).Cells(nxtRow, 1) Next End With'Delete SortTemp sheet Application.DisplayAlerts = False Sheets("SortTemp").Delete Application.DisplayAlerts = True'Turn on ScreenUpdating Application.ScreenUpdating = TrueEnd Sub

Relevance 100%
Preferred Solution: Excel Macro to create new worksheet in Excel 2010

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

Answer: Excel Macro to create new worksheet in Excel 2010

I created a quick macro to help you out. This macro creates a new sheet and names it using the contents of cell "A1".Sub CreateNewSheet()
Dim NewSheet
Dim SheetName As String

SheetName = Worksheets("Sheet1").Cells(1, "A").Value
Set NewSheet = Sheets.Add(After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet)
NewSheet.Name = SheetName

End Sub
Law if Logical Argument: Anything is possible if you don't know what you're talking about.

4 more replies
Relevance 89.9%

I am new to VBA macro programming, so i have been doing trial and error from code that was placed on here earlier that was very similar to what i am trying to do (almost exactly the same)...I have a project list i am tracking but want to isolate each project's information to its own sheet. (project list is produced via a report.i.e.IntakeID ProjectName ProjectDescription PriorityLevel2687 Electronically add documents test1 TBD2776 Docview Retention test2 High2888 Tracking and Enhancements (Stand Alone) test3 TBD2889 SBB Hi Watt - Laserpro - LS test4 HighI have the following code, but it is not working past copying in the information into its separate sheet. Ideally i would like to structure the code to be able to paste the individual information to its own sheet, but place the values in certain cells in the new sheet.i.e. record has project ID in A2 cell, description in B2 from report list, but in the new separate sheet, the ID would be in A4, and the description in C3.Here is what i have (This code was taken from a previous post in which i tried to modify for my purposes, credit goes to whoever started):Sub CreateProjectSheets()Dim lastRow, mProjID, tstProjID1, tstProjID2, shtName, nxtRowOn Error Resume Next'Turn off ScreenUpdatingApplication.ScreenUpdating = False'Make a copy of the data sheet and sort by date Sheets("Sheet1").Copy After:=Sheets(1) Sheets(2).Name = "SortTemp" With Sheets("SortTemp") lastRow = .Cells(Rows.Count, 1).End(xlUp).Row Rows("2:" & ... Read more

Answer:Excel Macro to create new individual worksheet on ID

Please click on the blue line at the end of this post and, after reading the instructions found via that link, repost your example data and code.Thanks!Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 80.33%

I'm using a radio button to make a selection on a spreadsheet but when I use the cell link feature the 1.00 or 2.00 turns white in a light grey cell. I'm trying to make the 1.00 / 2.00 the same color as the cell.Also, I want to run this macro in the loop from row number 6 to 128. Any ideas?

Answer:How do I create an Excel 2010 Macro to change font color?

Do you have a macro already or are you wanting us to create one for you? if you need us to help make one then please provide more detail, what is the purpose of this macro exactly? what is the process you want to follow before the macro is triggered?a lot more information is needed.

3 more replies
Relevance 77.14%

Hi,

I have one worksheet with 3 colums and rows.
I need to create a new dinamic worksheet from the data on the 1st sheet.
Columns in the 1st sheet are:

Material Plant Quantity
------------------------------------------
AAA 1 100
AAA 2 50
AAA 3 20
BBB 1 30
BBB 3 10

I know descriptions for the Plants: 1 - "East", 2 - "West", 3 - "South".
I have to create macros or formulas to build a new worksheet from information on this one. It is basically take a Material group by, then Plant and sum of Quantity for that specific Material and specific Plant , and build a column for that specific Plant, with provided description. So if its Plant =1 , then column is "East", and so on.
So, essentially the new worksheet shooud look like this.
Quantity for Materials based on Plant.

Material East West South
-----------------------------------------------------------
AAA 100 50 20
BBB 30
BBB 10

Can someone give me tips on how to build that new worksheet and how should i build macro?

Thank you,
Vadim.
 

More replies
Relevance 104.96%

writing a program in Excel... would like to have a "information page" where user info is collected then used to name tabs of worksheets in the program...
anyone have a solution?
 

Answer:Macro to name worksheet Tab in Excel?

http://www.ozgrid.com/Excel/TipsAndTricks.htm

http://www.mrexcel.com/
 

1 more replies
Relevance 104.14%

I am having a problem with my macro code.
First i will describe what i am doing then i will post my code for help.

I have created an open items list for work. I have created 2 identical headers, each in a different worksheet. One of the columns in both work sheets, F to be specific, is a status field. There are 3 to chose from pending, waiting on approval and completed. I have created a button at the top of the header in the open items worksheet so the when it is pushed it runs the macro and moves all of the items that have a status of complete to worksheet 2, the closed action items.
Item # Type Item Description Priority Due Date Status Owner Support Team Compl Date Updates

This is the header that i have in both worksheets.

Here is the code that i have written to move the completed actions over to the closed worksheet.

Sub ClosedItems()
Dim wsOpen As Worksheet, wsClosed As Worksheet
Dim rFilter As Range
Dim lRow As Long

Set wsOpen = Sheets("Open Action Items")
Set wsClosed = Sheets("Closed Items")

For lRow = 6 To wsOpen.Cells(Rows.Count, 1).End(xlUp).Row Step 1

If wsOpen.Cells(lRow, 5) = "90-Completed" Or wsOpen.Cells(lRow, 5) = "99-Cancelled" Then
wsOpen.Range("A" & lRow).EntireRow.Copy
wsClosed.Range("A" & wsClosed.Cells(Rows.Count, 1).End(xlUp).Row + 1).PasteSpecial
wsOpen.Range("A" & lRow).EntireRow.Delete
lRow = lRow - 1
End If
Next lRow

End Sub
PLEASE HELP!!!!!!!!
 

Answer:Solved: Excel worksheet macro

16 more replies
Relevance 104.14%

Hi, I'm really hoping someone can help with this problem- I've Googled it non-stop. I have an excel sheet where I would like a row to be copied from the 'open cases' worksheet into the 'closed' worksheet when the value 'closed' entered in column K. I have the following macro:Private Sub Worksheet_Change(ByVal Target As Range) Dim C As Range If Intersect(Target, Me.Range("K:K")) Is Nothing Then Exit Sub For Each C In Intersect(Target, Me.Range("K:K")).Cells If C.Text = "Closed" Then Dim otherSheet As WorksheetSet otherSheet = Sheets("Closed")C.EntireRow.Cut Worksheets("Closed").Cells(Rows.Count, "D").End(xlUp).Offset(1).EntireRow End If Next End SubThis pastes the row but deletes any information which was is in the 'Closed' worksheet. Any help would be brilliant. Thank you,

Answer:How to use to macro insert row into new worksheet in Excel.

My knowledge of VBA is just above nil, but wouldn't it better to Copy then Cut?You might try changing the line that reads:C.EntireRow.Cut Worksheets("Closed").Cells(Rows.Count, To something like:C.EntireRow.Copy Worksheets("Closed").Cells(Rows.Count, See if that works for you.MIKEhttp://www.skeptic.com/

3 more replies
Relevance 104.14%

I have a worksheet that I want to concurrently print a hard copy and save to a .pdf file in a folder on my computer. The worksheet that I want to print and save to .pdf is not the active worksheet.I would like for the worksheet to be saved to .pdf with a unique file name. The worksheets saved as .pdfs will be archived, and it's important that they are not written over.Could someone help me with the VBA code to do so? Thank you for any help you can be.Sincerely,Mark1011

Answer:I want to use a macro to save an Excel worksheet to a .pdf.

Try here:http://www.rondebruin.nl/pdf.htmMIKEhttp://www.skeptic.com/

5 more replies
Relevance 104.14%

I need help to create a macro that will do the following:

I have several rows in a worksheet, sorted by a column titled "Status". "Status" can either be open or closed. For each row, if "Status" is closed, I need to have the entire row cut and moved (pasted) to a separate worksheet starting on the next available row.

So, first the macro needs to recognize the rows that that have Status = closed. Then it needs to cut the entire row, find the next available or empty row in the next worksheet, and pasted the row there.

Can somebody help me? Thank you so much.
 

Answer:Excel MACRO Help: Copying row into a different worksheet

7 more replies
Relevance 102.91%

This is my 1st time. I am trying to obtain the macro code for my spreadsheet. I want data to be copied from worksheet TASKS to worksheet COMPLETED based on data entered in a cell. Both worksheets have the exact same layout.Utilizing Rows A-H. I would like for all data in row to be deleted from TASKS tab and moved to the COMPLETED tab (under the same section) if Column F equals 100%. See example of layout below (have to use .... to seperate).Tab = TASKS (to be copied & deleted from)Task..................................Start Date.........Proj Compl.........Actual Compl..........%........Resource Req.............Notes Section = MEETINGS Name of Meeting..............02/08/10...............02/09/10.............02/10/10.............100%.......None.......................NoneTab = Completed (data to be moved to - under the same section) Section = MEETINGSAlso...If I need to insert rows in the future for new tasks, will the macro apply to these?Hopefully this makes sense.

More replies
Relevance 102.91%

I have created several spreadsheets representing a dog's pedigree and statistics. What I would like to do now is be able to choose 2 dog's spreadsheets, and then copy and paste both of their pedigrees/data into a new spreadsheet to evaluate a test breeding between the 2 dogs. I No calculations are needed, I just need a way to create an organized and temporary way to present the data for both dogs.All the worksheets are identical in structure. The data is arranged in blocks. I know where I would like each block of data to be positioned on the new 'Test Breeding' worksheet. Unfortunately, I have no clue how to do this. Any assistance would be greatly appreciated!

Answer:Need Excel Macro: Copy/Paste to new Worksheet

Keep in mind that we can't see your spreadsheets from where we're sitting, so it's kind of hard to give a specific answer.It sounds as if VLOOKUP would work, except the VLOOKUP doesn't work across multiple sheets. However, there is User Defined Function at the Ozgrid site that will do VLOOKUP's across all sheets in a workbook.Are you ready for a interesting coincidence? The example they use is:=VLOOKAllSheets("Dog", C1:E20, 2, FALSE) As I said, not knowing the layout of your sheets, but using the fact that you said that all sheets are laid out the same, I'm thinking that you might be able to use this UDF to accomplish your task.Let us know if that helps.

3 more replies
Relevance 102.91%

Microsoft Excel MS Excel 2003

I would like to pull information from worksheet cells (specifically file path and name) to use in an auto open macro in order to provide a workaround on the limitation of the INDIRECT function's inability to access data in closed worksheets.

How do I create a reference in the Visual Basic macro to pul the data from the worksheet?
 

Answer:MS Excel Worksheet Cell info to be used in Macro

10 more replies
Relevance 102.91%

I'm really hoping someone can help me here.I've recorded a macro (code below). The macro basically copies a formatted range and pastes it into the current cell where I'm located on the 'Wireframes' sheet. I want the marco to work on any sheet even if i create a new sheet. I'm guessing I want to take the destination out of the code but my limited knowledge can't work it out in order for the macro to continue working.Thanks KieranSub Header_One_Col()'' Header_One_Col Macro' Macro recorded 02/08/2010 by WILLIAKI'' Sheets("Headers & Banners").Select Range("B3:P10").Select Selection.Copy Sheets("Wireframe").Select ActiveSheet.PasteEnd Sub

Answer:Excel Macro to paste range to any worksheet

Hi,This code will copy the range in Headers & Banners to wherever your active cell is and whatever worksheet it is on:Sub Header_One_Col()
Worksheets("Headers & Banners").Range("B3:P10").Copy _
Destination:=ActiveCell
End SubThis is all one line - the underscore with a space before it is a line continuation character.Note that in Visual Basic it is normally not necessary to Select a cell or other object before acting on it. The macro recorder records these 'selections', but they are not required.As the only 'fixed' worksheet is Banners & Headings you probably want to store the code with that sheet. Right-click the tab name "Banners & Headings" and select 'View Code'Paste the code into the Visual Basic window.You could then attach the macro to a button on a toolbar to make it easily accessible:Right-click a toolbar and select 'Customize'From the 'Commands' tab select 'Macros' in the left pane, and then drag the Custom button icon in the right pane to the toolbar.Right click the new icon, select 'Text only in Menus' and then select 'Assign Macro' and select your macro from the list. Click OK and then close.Now wherever you are, clicking the button will run the macro and copy and paste that range of cells, starting at the selected (active) cell.Regards

4 more replies
Relevance 101.68%

What is the code to send the user back to the previous worksheet? For example, the user is currently in sheet 1, he clicks a commandbutton which executes an if/then statement - if true, then he gets sent to sheet 2, if false, he is sent back to sheet 1. Thanks.
 

Answer:Excel Macro - Previous Worksheet - Easy Question

13 more replies
Relevance 101.68%

I am trying to create a macro that searches for a range of dates by prompting the user for a start and end date and then searches a specific column by asking the user to enter the column header title. I then want to copy the data from that row for 4 specific columns called LOB, Profile, FirstName, and LastName and paste it in a new workbook. The remaing data in that row should not be copied. I have limited VB knowledge and looking for somebody to help. Thanks!

Answer:Excel Macro to copy user specified data to new worksheet

re: "I have limited VB knowledge and looking for somebody to help."Does "limited knowledge" mean that you can take some generic code and modify it to fit your specific needs (change search ranges, sheets names, etc.) or are you looking for a complete and working macro?If it's the former, I can throw something together. If it's that latter, I would need some more specific information, like what columns the dates are in, exactly where should the data should be pasted, etc.As it stands now, I'm a little confused by a few of things.1 - Your subject line says "copy user specified data to new worksheet", the text of your post says "paste it in a new workbook" As I'm sure you know, those are very different things and the code required will be very different.2 - You said "by prompting the user for a start and end date and then searches a specific column by asking the user to enter the column header title."Does that mean that you have dates in more than one column and the code will need to know which column to use each time a search is done?3 - Will multiple rows need to be copied for each search or will it be a single row based on a single date that will be found based on the start and end dates. In other words, will there typically be more than one date found between the start and end dates?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

4 more replies
Relevance 100.86%

Please can someone write me a macro to create a new worksheet in Excel based on a trigger.Basically I have a master worksheet which is the first tab in the series containing some data. Column F has a drop down list containing two options "Completed" and "Pending". I want excel to create a new worksheet whenever a user selects "Completed" from the drop down list. The trigger will always be "Completed" and in case the selection is "Pending" then nothing should happen.I don't know if its possible or not but I would also like the new worksheet created to be renamed automatically to data input in other cells. E.g. - If I user "Completed" in Cell F1 then the new worksheet created should be renamed to the data in Cell A1, B1 and C1.Values and result for e.g :Cell A1 = Jan 2011Cell B1 = BACSCell C1 = Pre FlightResult required = A new worksheet named "Jan 2011-BACS-Pre Flight"

Answer:Excel formula to create new worksheet

Right click the sheet tab for the "master" worksheet, choose View Code and paste this code into the pane that opens:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 6 And Target = "Completed" Then
ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)
Sheets(Sheets.Count).Name = _
Range("A" & Target.Row) & "- " & _
Range("B" & Target.Row) & "- " & _
Range("C" & Target.Row)
End If
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 100.86%

It's been a while since I have done this and I can kind of picture in my head what I need...

I have 50 surveys (source) that I need to extract certain data points and copy/paste them into another spreadsheet. I can get to the point of opening each survey with an input box and copying the cells in the source but it is the paste that is failing me...the way I have it set up right now the next survey I open will overwrite the previous survey - I need to make this dynamic so that the next survey I open it will go to the summary file, locate the next empty Row and start importing data. Moving cell to cell - to the right until complete.

Please help

See Code:
Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim wb1 As Excel.Workbook
Dim ws As Excel.Worksheet
Dim ws1 As Excel.Worksheet
Dim sSurveyPath As String
'Dim sMainPath As String
Dim iSheet As Integer

'input box
Dim sMyInput As String
sMyInput = InputBox("Enter the Store Number", _
"Survey Store Number", "Enter Store Number HERE")
If sMyInput = "Enter Store Number HERE" Or _
sMyInput = "" Then
Exit Sub
End If

'Summary
Set wb = ThisWorkbook

'Loop through Each Sheet in this workbook
For iSheet = 1 To 3
Select Case iSheet
Case 1
sSheet = "Vendor Info"
Case 2
sSheet = "Newspaper Info"
Case 3
sSheet = "Rack Info"
End Select

'source
sSurveyPath = "E:\Myfiles\Client\Surveys\Survey_" & sMyInput &... Read more

Answer:Oh Excel Macro Gurus - pelase help with extracting data into new worksheet

Are you talking about Copy Destination:=wb.Worksheets(sSheet).Range("A3")
and then B3, C3, etc. ??? Is this what you need to be made dynamic using the next available row?

Regards,
Rollin
 

2 more replies
Relevance 99.22%

I set up a spread sheet for our finances - It took me quite a long time to make this spreadsheetso I would like to protect the spredsheet and all formulas.Thanks

Answer:Lock a formula in excel 2010 on a worksheet

It's pretty simple...On the Review Ribbon click Protect SheetChoose the items you want to let your users performSelect a PasswordDon't forget itClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 99.22%

I'm looking to create a form that sits on a worksheet, where I can populate fields on the form, that populates a worksheet within the same workbook.
I have 3 sets of invoices and once scanned would like to enter invoice data, that will record the data. After a certain period of time 'field on userform' the 'hard copy' invoices will be disposed of. Hopefully the form I create can record this data for historic purposes and reduce storage. Once I have the data, would also like to run a basic report if possible.
I'm using excel 2010 and don't have access, nor can I get access to it, so excel is my only option.
I would like the form to consist of 11 fields and some of them to do particular things if possible.
Field 1 (ID No.): Would like this to generate an auto number, each time document is opened.
Field 2 (Scandate): user would enter a date DD/MM/YYYY
Field3 (Maildate): as above
Field4 (Invoicetype): *see below
Field5 (Batchname): user would enter a date DD/MM/YYYY hh:mm:ss
Field6 (Number): user would enter a number
Field7 (Anticipated disposal date): would like this field to automatically be calculated. This is 3 months from the "Scandate" re Field 2
Field8 (Actual disposal date); user would enter a date DD/MM/YYYY
Field9 (Disposed By): user would enter their name. would it be possible to have a dropdown to select, maybe use excel's validation faeture?
Field10 (A button to add a new record)
Field11 (A button to close the form)
*(Invoice type): would it be possibl... Read more

Answer:Excel 2010 - Userform that will populate worksheet

May I point you to click here mrexcel.com forum that will give more indepth help for what you want to achieve.

10 more replies
Relevance 99.22%

I am currently maintaining a critical excel 2010 worksheet, that takes forever to load and forever to refresh all data.

I have tried removing formats on most of the workbooks, but it only increased the size of the file and the whole processing was even slower.

This situation will probably worsen as the file gets loaded with more data.

I'm aware that Excel 2010 has around 2GB of memory, but is there a way to determine how much memory is being utliized, so at least I can monitor whether the file is coming close to its memory limit?

I know there is a Function called INFO(type)

where for Type "memavail" displays Amount of memory available (bytes).

"memused" displays Amount of memory being used (bytes).

"totmem" displays Total memory

But this function only applies to Excel 2007, Excel 2003, Excel XP, Excel 2000 versions.

well this link tells me no longer available in Excel 2010.
Anyway to monitor the memory usage in Excel 2010 and to speed up processes too?
 

Answer:Determine how much memory Excel 2010 worksheet is using

12 more replies
Relevance 99.22%

Excel 2010 worksheet is operating slow. It says "not responding". Lately, it shuts itself down but when it boots up it still operates slowly. I copied the worksheet to a flash drive and tried it in my wife's laptop and it did the same so I suspect it has something to do with the worksheet itself.

Answer:Excel 2010 worksheet - not responding & shuts down

Try turning OFF auto calculation.On the Ribbon, Select FormulasSelect Calculation OptionsCheck ManualThen load your workbook and see if that speeds things up.MIKEhttp://www.skeptic.com/

3 more replies
Relevance 98.81%

It's pretty easy to use the wizards in MS Access 2003 to first create an Excel workbook and then open Excel. Using the wizards causes Access to open Excel without a workbook loaded. My question is how to modify the open Excel command to load a file (e.g. c:\myfile.xls). Here's the code to open Excel that the wizard creates. Can anyone tell me a quick way to modify it? Thanks in advance.

Private Sub Command37_Click()

Dim oApp As Object

Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
oApp.UserControl = True

Exit_Command37_Click:
Exit Sub

End Sub
 

Answer:Solved: Create and Open Excel Worksheet from Access

Add this line to your code:

Code:
oApp.Workbooks.Open ("C:\myfile.xls")
Your final code should look like this:
Code:

Private Sub Command37_Click()

Dim oApp As Object

Set oApp = CreateObject("Excel.Application")
oApp.Workbooks.Open ("C:\Test.xls")
oApp.Visible = True
oApp.UserControl = True

Exit_Command37_Click:
Exit Sub
Regards,
Rollin
 

2 more replies
Relevance 98.4%

I have a macro setup, but each time the code is ran it errors out on this line of code.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Err_Handler
Dim YN As Long
Dim b As CommandBarButton
Dim x As Long, ctls As CommandBarControls
Set ctls = Application.CommandBars("Standard").Controls

For x = 1 To ctls.Count
If ctls(x).Tag = "LGG" Then
Call ctls(x).Execute
Exit For
End If
Next

Err_Exit:
Exit Sub
Err_Handler:
YN = MsgBox(Err.Description, vbCritical, "Change Setup Worksheet")
Resume Err_Exit
End Sub

Hope you can help me out

Thanks in advance
 

Answer:Excel Macro – Change Setup Worksheet - Invalid procedure call or argument

16 more replies
Relevance 97.99%

Hello

Big problem for me

I install Office 2013 about 2 months ago. Prior, I was using Office 2010 after Office 2007 etc.

Today, I saw the both version was install on My PC. Office 2013 and Office 2010. So I close all Excel sheet and proced to uninstall Office 2010 and Office 2010 language pack,

Since, all work done prior to the un-installation seem to be empty worksheet All work done in Excel

What can I do

Martin

Answer:Help!! Excel worksheet look empty after Office 2010 uninstall

You may be able to recover by doing a System Restore to a time before you did the Office 2010 uninstall.
To do that open Control Panel & select Recovery & follow through.

If that fixes the problem then you should back up all your Excel & other files that you wish to keep, then uninstall Office 2010 again & then copy your backed up files into Office 2013.

2 more replies
Relevance 97.17%

I have a company logo on each worksheet in a workbook that is an inserted image. Every time I copy or move the worksheet it loses the image. I have went to Image properties and checked the "Move & size with cells" but it doesn't help.
 

Answer:Excel 2010 worksheet loses image upon copy or move

6 more replies
Relevance 94.3%

Can someone give me some advice, or a good website to learn "how" to create a macro in Excel?

Thanks in advance
 

Answer:How to create a Macro in Excel

6 more replies
Relevance 93.48%

I have the following code, but it doesn't put the correct date into the cell. It supposed to find "LT" (in column C) then decrement to see if the characters before the "LT" are a match and when they are it should take the date from that row in column H and move it to the row where the "LT" was found also in column H. Its making me nuts any help would be appreciated. Here is the code I have:
Sub LT_Search()
r = 0
rx = 0
For r = 1 To 10000 'Range("C1:C100000").End(xlUp).Row
tst = Right(Range("C" & r).Value, 2)
If tst = "LT" Then
rx = r
tst2 = Left(Range("C" & r).Value, Len(Range("C" & r).Value) - 2)
Do Until CStr(tst2) = CStr(Range("C" & rx - 1).Value)
If Range("A" & r).Value = "" Or rx = 1 Then Exit Do
Range("H" & r).Value = Range("H" & rx).Value
Loop
End If
Next

End Sub
 

Answer:Help with a macro for Excel 2010

Hi welcome to the forum,

First I suggest you put the code in a code bos for better reading
second it is good habit to Dim you variables.
Code:

Sub LT_Search()
Dim r As Long, rx As Long
Dim tst As String, tst2 As String
r = 0
rx = 0
For r = 1 To 10000 'Range("C1:C100000").End(xlUp).Row
tst = Right(Range("C" & r).Value, 2)
If tst = "LT" Then
rx = r
tst2 = Left(Range("C" & r).Value, Len(Range("C" & r).Value) - 2)
Do Until CStr(tst2) = CStr(Range("C" & rx - 1).Value)
If Range("A" & r).Value = "" Or rx = 1 Then Exit Do
Range("H" & r).Value = Range("H" & rx).Value
Loop
End If
Next r
End Sub

Anybody reading this will wonder what you are comparing, that would make it much easier to to understand.

You don't have to put the 100000 rows in there but some so that we can also test the code.

I have attached a file with code in Sheet1's VBA

All you need is put some non private data there and then reattach it.
 

2 more replies
Relevance 93.48%

Hello,

In Excel 2007, Having thousands of cells with text having a leading apostrophe, I used this macro to auto remove the apostrophes.

Then when using the same file and macro in Excel 2010, after runing the macro I can't remove the apostrophes at all! It doesn't give any error but it's not working, anyone can tell me why?

Bottom line is: Macro working perfect in vs2007, why not in 2010

I've attatched the file (only the 1st and 2nd "names" contain the apostrophes)

OBS:
- Also tried without sucess 3 or 4 diferent types of macros found in other foruns
- The copy-paste values and add doesn't work and the copy blank cell etc
- In Excel Options/advanced - Transition navigation keys: Disabled
- Also tried saving in compatibility mode 2003-97

The macro:
Sub DeleteApostrophes()
Dim rCell As Range

For Each rCell In ActiveSheet.UsedRange
If rCell.PrefixCharacter = "'" Then
rCell.Value = rCell.Value
End If
Next rCell

End Sub

... seems that the file doesn't upload to this post, maybe it needs some sort of approval? I'm new here, sorry guys.
 

Answer:MS Excel 2010: Any help with the macro/vba?

Would you be open to using a formula to accomplish this? You could use the SUBSTITUTE formula to remove the apostrophes and then copy the values back over the original to finish the replacement. Let me know if you're open to the idea, I can explain further. I have no idea why a macro wouldn't work in 2010 when it works in 2007, though.
 

5 more replies
Relevance 93.48%

Hi i am trying to create a macro that clear's contents in an excel file for 5 seperate tabs. When i run the macro i get a run time error 1004 and select method of worksheet class failed. Does anyone know what is wrong with the code or what the correct code would be for the macro to run succesfully? I pasted below the code from the macro:

Sub ClearContents()
'
' ClearContents Macro
'
' Keyboard Shortcut: Ctrl+d
'
Range("A2:B2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A2").Select
ActiveSheet.Previous.Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A9").Select
ActiveSheet.Previous.Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A9").Select
ActiveSheet.Previous.Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range... Read more

Answer:Macro excel 2010 help

7 more replies
Relevance 93.48%
Question: Excel 2010 Macro

Hello

I have a series of worksheets that represent monthly client statements. I ultimately want to be able to send the statements individually or in mass at month end to clients via MSExpress. Having never delved into Macros I am a complete novice and therefore need to be treated with kid gloves.

The worksheets are identical in layout and only differ with user data. I have created cells per worksheet that refer to a 'Master' worksheet to mange statement dating, period, year etc. Also I have a unique 'account number' per worksheet. I want to be able to save the individual worksheets as .pdf documents, creating the path and file name from the existing path and atributes within the file. This I have done as follows.

=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)&K7&"_"&K4&J4&".pdf"

which results in a string as follows

C:\Documents and Settings\Kenneth Smart\My Documents\Business Ventures\ABC\COD001_20124.pdf

All goes well when recording the macro below and it appears to work well, that is until I run it on any of the worksheets that it was not recorded in.

My problem is that the .pdf file name is not updating to the new worksheet data and retains the original file name. I have looked at some comments on the www, however, only being a geek wannabee I have yet to acquire the skill-set to understand whether my problem is unique.

I don't think it has anything to do wi... Read more

Answer:Excel 2010 Macro

OOPS, Serious OOPS.

I see that in my recorded macro that the path string is a literal one and not the variable I expected from the cell formula. This is obviously where I need assistance.
 

1 more replies
Relevance 93.48%

Can someone please help me to write a macro to assign to a button on a form that prints a copy of the excel page and saves the workbook to a specific file (Network/ESL-DC01/Datastore/Shared/Orders Placed) and uses the contents of a specific cell (E5) as the saved file name, when the button is clicked.
I think there might be a way to do this but it's driving me mad. Any help would be much appreciated, many thanks.
 

Answer:Help with macro in excel 2010 pls

I'm not an expert, but this code worked fine...Of course, you need to create the button and assign the macro PRINTandSAVE....Also, before you try it out, change the Path I used for the one you need.

Sub PRINTandSAVE()
'
'
'------------
'Print the Active Sheet
'------------
Set ws = ActiveSheet
ActiveWindow.ActiveSheet.PrintOut
'------------
' Save a copy of the workbook
'------------
Dim CSName As String

'This is the cell (E5) containing the name for the new book.
CSName = Worksheets("Sheet1").Range("E5").Value

ActiveWorkbook.Sheets.Select
ActiveSheet.Activate
'below, you should type the path where you want to save the file
ChDir "C:\YourPath\"
'Save the new workbook.
' FileFormat := 52 allows to save the Workbook containing macros
ActiveWorkbook.SaveAs (CSName), FileFormat:=52

'Close the original workbook, do not save. Remove if not needed
ActiveWorkbook.Close (False)
End Sub
hope it helps
 

3 more replies
Relevance 93.07%

I have a spreadsheet (Called "Expenses") with 3 (A,B,C) tabs. How do I create a macro to print all tabs. KOmegah

Answer:How do I create a print macro in Excel

re: "I have a spreadsheet (Called "Expenses") with 3 (A,B,C) tabs."I assume that you mean you have workbook called "Expenses" with 3 sheets named A, B and C.1 - Press Alt-F11 to open the VBA editor.2 - In the left hand pane, Double Click the word "ThisWorkbook"3 - Paste in the following code Whenever you Print the workbook, the code will "intercept" the Print command and run, printing every sheet in the workbook.Private Sub Workbook_BeforePrint(Cancel As Boolean)
Application.EnableEvents = False
For shts = 1 To Sheets.Count
Sheets(shts).PrintOut
Next
Cancel = True
Application.EnableEvents = True
End SubPosting Tip: Before posting Data or VBA Code, read this How-To.

2 more replies
Relevance 93.07%
Answer:create a macro to insert a row below in excel

Do you really expect us to offer any assistance with a post like that? If you saw a post like would you have any clue what the poster is trying to do?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 93.07%

I need to create a macro that creates a set of multiple sheets basedupon a number entered. For example: I enter the number 20 and thenrun the macro sheets A1,B1,C1,D1,E1,&F1 are created and repeated 20times with the second set being A2,B2,C2,D2,E2,&F2, etc, all the wayto A20,B20....... My knowledge in this area is pretty limited so anyhelp is greatly appreciated.

Answer:excel macro to create sheets

re: "sheets A1,B1,C1,D1,E1,&F1 are created and repeated 20 times with the second set being A2,B2,C2,D2,E2,&F2, etc"Are you saying that the sheet names should be A1, B1, C1, etc.?So, if you enter the number 20, the code should create 180 sheets named A1 though F20?

11 more replies
Relevance 93.07%

I have a audit template that takes two files and compairs them and opens a new workbook to display the differences. This new workbook is then saved to a specific location for audit reasons.

In the new workbook created, I'm wanting to "remove" all the toolbars and menus so a user can't safe the file to another location by mistake.

Is there a way for the creating template to "insert" macros into the newly created workbook to do this? While I could just create new worksheets in the "creating" template workbook and remove the ones I don't need, I'm trying to keep the size of the file down by not having all the macros that created the new template in the final product.
 

Answer:Have Excel VBA Create A Macro In A New Workbook

Just for some thoughts on this concept, I would suggest you read this:
http://www.vbaexpress.com/forum/showthread.php?t=26263&highlight=hide+toolbars
 

1 more replies
Relevance 93.07%

I am building a spreadsheet for a data center move project and I want to build a macro that will automatically create a hyperlink each time I add a persons name into any event they are assigned to. There will be about 150 people on the project so it may have to be a very long macro. If anyone has any thoughts please let me know.

Thanks in advance!
 

Answer:Excel Macro to create Hyperlinks

16 more replies
Relevance 92.66%

Hi!I have made an macro that sould always be used in a specific excelfile. I am wondering, if I save this file with the macro in a databse, will others be ale to use the macro? Or do I need to Transport it in some ways, maybe like an add in ?Tank you for replying.

Answer:Transport macro in excel 2010

What do you mean by "if I save this file with the macro in a databse"?Assuming the macro is stored in a module within the file you are saving, and not in your personal.xls file, then...If you save an Excel file in 2003 format (xls), the macro will be saved with the file.If you save an Excel file in 2010 macro-enabled format (xlsm), the macro will be saved with the file.In both cases, users will be able to use the macro as long as they have macros enabled in their Excel application. If they don't, they will get a warning that file contains a macro when they try to open it.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 92.66%

Can anyone tell how to write a macro for Excel 2010 that will do the following?

After performing some modifications to a currently open Excel workbook, I want to:
Save it to a particular folder on a backup drive
Return to the original document and save it to its original location
Close the original document
Display the "Open" window for selection of the next desired document
End the macro

I have an old macro that performed these functions for many years, but it doesn't work properly in Excel 2010 on a Win 7 computer. I've been unsuccessful in trying to record a macro to do the above, but if I knew the necessary code wording, I could write the macro from scratch using the VBA editor that's provided with Excel 2010.

Tech Support Guy System Info Utility version 1.0.0.2
OS Version: Microsoft Windows 7 Home Premium, Service Pack 1, 64 bit
Processor: Intel(R) Celeron(R) CPU E3400 @ 2.60GHz, Intel64 Family 6 Model 23 Stepping 10
Processor Count: 2
RAM: 2013 Mb
Graphics Card: Intel(R) G41 Express Chipset, 782 Mb
Hard Drives: C: Total - 465551 MB, Free - 392139 MB; D: Total - 11285 MB, Free - 1058 MB;
Motherboard: FOXCONN, 2A8C
Antivirus: Norton Internet Security, Updated and Enabled
 

Answer:Backup macro for Excel 2010

16 more replies
Relevance 92.66%

With purchase of a new computer (HP Pavilion P7-1010t) running Windows 7, I successfully transferred a large number of Excel 97-2003 files from my former computer, and they continue to work fine in compatibility mode on Excel 2010.

For many years I've used an Excel macro to: 1) save the open document to a backup disc, 2) save the document to the original disc (hard drive), 3) close the document, and 4) pop up the 'Open' window from which to select the next wanted file (if any).

Although written originally in Excel 4.0, the macro continues to work just fine in Excel 2010. Except for new documents created in Excel 2010, for which when I execute the macro I get the following error message:

The file could not be accessed. Try one of the following:
Make sure the folder exists.
Make sure the folder that contains the file is not read-only.
Make sure the file name does not contain any of the following characters: < > ? [ ] : | or *
Make sure the file/path name doesn't contain more than 218 characters.

I'm OK on all 4 cautions cited above, but my macro no longer works on newly created documents regardless of whether the new document is saved as .xls or .xlsx.
What is it about Excel 2010 that allows the macro to work with old documents but not with new ones?
 

More replies
Relevance 92.25%

I need a macro so that it will conditionally format the cells of four different columns. I would like the formula to run until it sees a blank row for those columns that I want to condition; however, I'm not sure that is really necessary.I receive a regular report for each of my customers but the number of rows will vary based on the amount of business that particular customer has. Each customer has their own tab (worksheet) in the workbook, except for tab #1 which is where I document formula information. For whatever reason (I believe due to appending rows from the regular reports), my current CFs seem to change the range on their own and I have to go back into the Conditional Formatting to ?reset? them back to what they need to be.Each report has the same column headings which use Rows 1 and 2, so the CFs need to start in Row 3 (see below). I plan to put a button in the ?Formula Info? tab (worksheet #1) that I can use to initiate the macro.Currently the CFs are as follows:Use this formula to determine which cells to format: =$L3="LEN ? 9" =$L$3:$L$5003 in Bold redFormat only cells that contain Specific text containing: ? =$K$3:$K$5003,$M$3:$M$5003 in Bold redFormat only cells that contain Specific text containing: D70 =$C$3:$C$5003 in black on red background with vertical fill effects.Format only cells that contain Specific text containing: M75 =$C$3:$C$5003... Read more

More replies
Relevance 92.25%

Hi everyone,
I am runing windows XP pro woth Office 2002 and Acrobat writer 5
I am trying to write a macro that will create a PDF on a specific Driver\folder\name.pdf

this is what I have :
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Acrobat distiller:", Collate:=True

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Filename = "G:\Temp\Seth-quotes" '& ActiveSheet.Range("a13").Value
'SendKeys Filename & "{ENTER}", False

I print it on paper first and then I want to printer as a PDF and it kind of works but it gives me the screen where it ask me where to save the file.. so I know that the part on red doesn't work on the script.

I want to create two button that will create a pdf and save it on G:\folder1\filename.pdf
and the other button that same the file on G:\folder2\filename.pdf

Does anyone have any idea of how to do that.

thanks
AMD
 

Answer:Excel macro to create a PDF on a specific filename

Try This:

Button 1

Private Sub Button1_Click()

Dim ThisFile as Variant

ThisFile = "G:\Folder1\" & Replace(ActiveWorkbook.Name, ".xls", "") & ".pdf"

ActiveWorkbook.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=ThisFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

End Sub

Button 2

Private Sub Button2_Click()

Dim ThisFile as Variant

ThisFile = "G:\Folder2\" & Replace(ActiveWorkbook.Name, ".xls", "") & ".pdf"

ActiveWorkbook.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=ThisFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

End Sub
 

2 more replies
Relevance 92.25%

Hi,
I have a major problem crunching large amounts of numbers coming off an instrument I work with but the file format it puts the results into is terrible and I cannot change it. So I must export the results in a format which needs some serious formating before it becomes of any use to me.

I have used excel macros to delete rows and rows with specific text values. But now I would like to create a macro that will reorganise this data in a more user friendly format. I did use the sort asending command to partially solve the formating problem but it would still require alot of work/time to organise it into the final format I need.

This is the current format of the instrument date:

Analyte Meas. Intensity
Rh 10
Y 98
Cs 66
Co 98
Analyte Meas. Intensity
Rh 10
Y 114
Cs 72
Co 86
Analyte Meas. Intensity
Rh 20
Y 108
Cs 44
Co 90
Analyte Meas. Intensity
Rh 20
Y 68
Cs 44
Co 66
Analyte Meas. Intensity
Rh 490.008
Y 58
Cs 34
Co 78
Used the "sort asending" command to separate data into useful form but need to get the data into either of the two tables shown below

Analyte Meas. Intensity
Analyte Meas. Intensity
Analyte Meas. Intensity
Analyte Meas. Intensity
Analyte Meas. Intensity
Co 98
Co 86
Co 90
Co 66
Co 78
Cs 66
Cs 72
Cs 44
Cs 44
Cs 34
Rh 10
Rh 10
Rh 20
Rh 20
Rh 490.008
Y 98
Y 114
Y 108
Y 68
Y 58
I would like to turn the data into this final format using a macro.

Co Cs Rh Y
98 66 10 98
86 72 10 114
90 44 20 108
66 44 20 68
78 34 490.008 58

Can somebody help me please ... Read more

Answer:create an excel macro to reorganise data?

ZIP and post a sample workbook.
Rollin
 

1 more replies
Relevance 92.25%

Hello guys,

I'm basically new to here What I need is a MACRO for automatized process.

1. get data from Sheet1,
2. insert data to Sheet2,
3. print Sheet2 with data from Sheet1,
4. repeat it with another row until everyone's name is printed

I need to print cca 10 pages A4 only with one set of Name, Surname and Age per page ... ;-)

Hope you know what I mean.

P.S.: Here are the pics of my problem.


Thank you very much.
 

Answer:EXCEL: How to create Macro for repeated print?

toki_sk,

Welcome to the forum.

Attached is a workbook that should loop down column A for the names and then print the sheet.
I added a delay code to slow the print process down to give the printer time to "Q" up.
You will see in sheet1 a button for "Print" when you click it the names will be copied to sheet2 for printing.
 

1 more replies
Relevance 92.25%

Have a procedure that is being used where large amounts of data are copied from Excel template to individual templates for users.

Wrote a macro to perform this task, worked perfectly for a few minutes, and then the formatting was off for the individual spreadsheet; the individual performing is again copying and pasting.

Other than picking up empty spaces in the macro, what else could be wrong?

Here is the macro:

'This macro copies all visible data from the source file to the create file, removes any excess rows/columns/formatting,
' deletes any sheets from the create file if no data is present, and renames the create file to the manager's name
' so that it's ready to be mailed to them
'Ranges need to be modified for each new set of data. They have been included in the comments for convenince for using
' find and replace throughout this page. A2 should always been the starting point for manager data. If
' multiple headers exist within a single sheet, delete all but 1 row for this macro to function properly.
' Only the end range should need to be changed. This should indicate the last row containing data for each sheet.
' Ranges in this function need to be updated to match the ranges called in each sub
'Vista Range("A2:A18210")
'EAS Range("A2:A34785")
'EFC Range("A2:A8265")
'INAS Range("A2:A1886")
'Vertex Range("A2:A109")
'Oracle Range("A2:... Read more

Answer:Excel Template, macro to create new spreadsheet

Hi, welcome to the forum
Nice code etc etc etc, but I don't think you realized that nobody is going to copy this and then try and figure out all the sheet names and so to test and troubleshoot.
If I read it correctly and seing that the excel file has the xlsx extension I assume at least Excel 2007.

I suggest you upload a copy of the file with dummy data or at least non-sensitive private dat in it so that we can try to figure out what the problem is.

A pice of advice when writing macro code: never hardcode filepaths into your code, everytime the location changes you have to go back to the drawing board.
 

1 more replies
Relevance 92.25%

need a simple excel macro to create 31 sheets and name them for the dateexample:sheet 1 is named 05-1sheet 2 is named 05-2sheet 3 is named 05-3etc for the whole month to 05-31

Answer:need a simple excel macro to create 31 sheets and name them

Assuming that you are starting with a "standard" new workbook with 3 blank worksheets, this code will result in 31 sheets named as you requested...
Sub DateSheets()
'Rename Sheets 1 - 3
For sht = 1 To 3
Sheets(sht).Name = "05-" & sht
Next
'Add and Name sheets 4 - 31
For sht = 4 To 31
Sheets.Add after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "05-" & sht
Next
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 91.84%

I have a spreadsheet that I put on a group drive for another region of the state to use. This spreadsheet was done in Excel 2007. It was working fine until the other region updated their Excel to 2013. Now the other region is having trouble with the spreadsheet; namely the drop-down boxes are showing that there are no values, but there should be a list of people in the list. When I open the spreadsheet it works fine. Because the other region is four hours away, I can't just go and look at it on their computer. Is there something that the other region (or that I) need to do to make the spreadsheet compatible with Excel 2013?

Answer:How do I make an Excel 2007 worksheet work in Excel 2013?

Take a look here:http://www.ozgrid.com/forum/showthr...

7 more replies
Relevance 91.43%

Good Day
I am fairly new to code and writing Macros.
I am workig in Excel 2010 and creatinfg an estimating tool for bidding condtruction project
My goal is to create a user control form (Done)
Parts DB (Done)
automate the creation of the proposals and project file using the user form
My issue now is creating the macros to input the dats

my list, combo boxes see the cell ranges by the range put in the control source that works fine
I currently can not figure out the Macro to input new date if the current infromation is not listed
example: col B=project Name. row 1 is title. B2 = bakery, B3 = Bank. I ned my next entry to input new information in B4 then auto populate a new qu0te number in col A

The user form has function over the entire workbook and is located in the VBA this workbook load upon opening

I apoligizes if my terms or explinations are not clear as I said I am really very new to this and need all the assistance I cane get
 

Answer:Excel 2010 User Form Macro

7 more replies
Relevance 91.02%

HiI am using this code below to create Excel sheets based off of a range of cells from a main sheet.I would like to do everything below but in addition when it creates the sheet and copies the row info, I would like it to make each row or cell on the main sheet link to each indivual sheet. Also within each individual sheet (that contains the one record info from the main sheet) to contain the link back to the main sheet. In other words, would like the hyperlinks to navigate back and forth when I have 75 sheets to work with. Any thoughts? thanks! BrahioSub AddMonthlySheets()Dim mMonth As Range For Each mMonth In Sheets(1).Range("A1:A12") ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count) ActiveSheet.Name = mMonth Sheets(1).Range(mMonth.Address).EntireRow.Copy Destination:=ActiveSheet.Range("A1") NextEnd Sub

Answer:Excel Macro to create worksheets from list with hyperlinks

First, a posting tip: Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum. Thanks!As for your question, something seems a bit strange. You said:"...would like the hyperlinks to navigate back and forth when I have 75 sheets to work with."Yet your code only creates 12 sheets:For Each mMonth In Sheets(1).Range("A1:A12")Assuming that's just an example, I'll assume you can modify the code below to create as many sheets as you need.You didn't provide the Name of the main sheet, so I am going to be creative and use the name Main in my code below. Again, I assume that you can modify the code to meet your needs.This code will create a new sheet for each value in Main!A1:A12. It will also create links in Main!A1:A12 to the respective sheets. Finally, it will create a link in A1 of each new sheet back to Main!A1.Sub AddMonthlySheets()
Dim srcMonth, dstMonth As Range
'Loop Through A1:A12
For rw = 1 To 12
'Create Worksheets and Copy Row
Set srcMonth = Sheets("Main").Range("A" & rw)
ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = srcMonth
srcMonth.EntireRow.Copy Destination:=ActiveSheet.Range("A1")
'Create Hyperlink to new sheet
srcMonth.Hyperlinks.Add Anchor:=srcMonth, _
Address:="", SubAddress:=srcMonth & "!A1", _
TextToDisplay:=srcMonth.Value
'Create HyperLink back to Main sheet
Set dstMonth = ActiveSheet.Range("A1")
dstMon... Read more

4 more replies
Relevance 91.02%

Hello,I am new to macros. I am not sure if this can be done, but I hope it can. I would like to create a macros that will create a new sheet for each day of the week automatically. Then pull certain information from the previous day to carry over. I would like it to automatically generate 5/1/2010, then 5/2/2010, etc. for example. I would like it to then take those days of the week and compile them into a new sheet labled 5/1 - 5/8 (looking @ my calendar for May). I would like for the info from the days of the week to be compiled into a summary for the week.I would then like it to compile the data from the weeks of the month into a new sheet labled month (May for example). And again, compile me a summary for the month based on the weeks and days information.Finally, I would like it to take all the months of the year, and compile that data into a new tab for the year (2010) for example. And the automatically summarize certain data for the entire year. Then, start over for 2011 doing the same thing. Days, weeks, months, year. I would like the months of the year to automatically go under the year tab, so that if I click the year tab it exapands to months, if I click on a certain month it expands to weeks and if I click on a certain week in the month it opens the days of that week.Hope that make sense. Is this possible to do?

Answer:Excel macro to Create Sheets, Copy Data

Hi,If you are asking for macros to do all of what you are asking for, then I suggest that you hire a programmer!Most of what you want is possible in Excel, although some things don't work the way you suggest:I would like the months of the year to automatically go under the year tab, so that if I click the year tab it expands to monthsI don't know what you are thinking of when you refer to tabs, such as 'the year tab'. Are you referring to the worksheet name tab, or are you thinking of custom forms with a series of tabs. Worksheet name tabs don't lend themselves to 'expanding'.create a new sheet for each day of the week automaticallyThis will result in 365 worksheets just for one year's data, excluding the month and other summary tabs.It is far better to start with all your data in one block - on one worksheet, then you can extract data for specific periods from it.Having daily data on separate sheets makes finding it and analyzing it a lot more difficult, even using visual basic macros. If you have a column containing dates, Excel stores dates as numbers which can be used to find a range of dates. If you have dates on worksheet name tabs, the 'date' is stored as a string, not as an Excel date number, so selecting worksheet names based on text representations of dates becomes quite complex.If you add 1 to a cell containing 01-Jan-2010, you get 02-Jan-2010, but there is no direct way to add 1 to a worksheet named 01-Jan-2010 to get 02-Jan-2010.If your monthly data is on separ... Read more

4 more replies
Relevance 91.02%

Hello, all. I'm looking to get assistance in creating a Macro to help format and move data in an Excel file: I have an Excel file with over 12,800+ rows and over 20+ columns, of data from about 192 companies (spanning ~16 years - 1994 - 2010 for each company).

Trouble is that there are several entries per year for each company.

What I want to do is to have every year, per company, be on one row: so I want to move the data for the same years, which come on different rows (in the data file), to the end of the row with the very first data for that year, which would make one long row (eg. if 5 rows of year 1999, make into 1 row of year 1999 by concatenation, and so on etc). And do this for each year for all the 192 companies.

Doing this manually is just inefficient, time consuming, and possibly prone to mistakes.

I've been told that a macro can do this, but as I'm a novice Excel user, don't know how to go about doing that. Even customer service & technical support at Microsoft weren't able to help me very much: they directed me here.

I have screen shots (of a simple spreadsheet I created) if anyone needs further clarification.

In the pictures, I only did the first company. But this is to happen for each year for all of the companies.

Please help. Thanks.
 

Answer:Help needed in trying to create a Macro? to cut, and concatenate rows in Excel

16 more replies
Relevance 91.02%

I am trying to recreate a macro I had on my old computer (written by someone I used to work with). The macro was made to run on a large excel spreadsheet (a lot of data) and copied all rows with the same value in a certain column and put them in a new tab (and named the tab whatever the value in that column was). Then it applied a subtotal function on each tab (same for each tab) and did some other formatting (same header row for all tabs, hides certain columns on each tab). The original tab retained all of the information, but was subtotaled and formatted the same as the other tabs (although, that isn't crucial). Please help!

Answer:How to create a macro to sort data to new tabs in excel

If you can post a short example of your data, noting which column has the values to be sorted on, perhaps we can help.Please click on the following line and read the instructions found via that link before posting your data.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 91.02%

Excel noob needs help. I have a spread sheet that changes daily. I would like a macro that will search worksheet named "Schedule Report" column G and create new sheet for each city listed and copy the row to new sheet. file today may have 10 cities and next day may be 20"Don't hit at all if you can help it; don't hit a man if you can possibly avoid it; but if you do hit him, put him to sleep."Theodore Roosevelt New York City, February 17, 1899

Answer:Excel macro help - Create Sheets/Copy Data

re: "file today may have 10 cities and next day may be 20"If the macro runs today, it would create 10 new sheets and copy the data based on today's data.What should happen the next day? Should it just create sheets/copy data for the 10 additional cities or does it also have to copy/change/update data for the original 10 cities?If it has to work with the data from the original cities, how does that need to be a handled? What will have changed? Will it just be additional lines, etc.?

10 more replies
Relevance 91.02%

Hi guys i'm hoping you can help me i'm a complete noob when it comes to macro's
 

Answer:How do i create a macro in Word that opens the data in Excel?

12 more replies
Relevance 91.02%

Hi

I am looking to create some code to move a sheet from one file to a new excel file. SO the code needs to open a new excel workbook and place the required sheet in the resultant new file.

I have managed to do this with an existing file "Moved Pivot" per the code below. This is ok but other people will be using my sheet so the location needs to be chosen by them. A new file seems to be the only solution?

Workbooks.Open Filename:="C:\desktop\Optegra Installer\Moved Pivot.xls"

Dim oTargetSheet As Worksheet
Set oTargetSheet = Workbooks("Moved Pivot.xls").Worksheets(1)
Call ThisWorkbook.Worksheets("PIVOT_TB").Copy(oTargetSheet)
Set oTargetSheet = Nothing


Any help much appreciated. Thanks

Jeremy
 

Answer:Solved: Excel VBA to move worksheet to new excel file

8 more replies
Relevance 90.61%

I'm trying to write a macro for Excel 2010 to count cells only if the background fill color is a certain color. I'm having trouble finding how to access the background color information in Excel 2010.
 

Answer:Excel 2010 - Macro to count if cell is a certain color

6 more replies
Relevance 90.61%

I have a macro I wrote in excel 2003 (by recording small steps as a chain of macros), I just upgraded to a new machine and Office 2010.

When I try and run the macro it comes back with error message "Run time error 438, Object doesn't support this property or method."

When I run debug the code line that is flagged is:

Selection.End(X1 to Left).Select

Are there any commands or options which have changed between 2003 and 2010 and is there a conversion list anywhere?

George intermediate user running Windows 7 64bit, Office 2010
 

Answer:Solved: Excel macro 2003 to 2010 error 438

16 more replies
Relevance 90.61%

Hi there,

To start off, I'm not too bad with excel, but I'm not very familiar with macros.
Scenario:

- Working from 3 sheets (1)Payroll sheet (current), (2)Payroll sheet (previous period), (3)Values which have changed when comparing (1) and (2)

My dilemma:

The issue is that when new entries are added to (1), it creates a mismatch with (2). I am thinking maybe a macro that runs from an if statement which compares the name fields for each entry and then runs a macro that adds a blank row so that differences will be easier to isolate.

if('sheet1!'a1='sheet2!'a1,,run macro)

Am I on point or even at that realistic in my assumption or is there another easier way to get this to work that I am not thinking of.....

PS, I hope this makes some sort of sense!

**ALSO! The rule needs to apply uniqely to about 405,000 records...
 

Answer:Excel 2010 - IF statement as event to trigger macro?

You can use a vlookup to check for difference.
As for you If macro, it doesn't exist.
You can however get macro to add rows for you though.
 

2 more replies
Relevance 90.61%

Hi I need a Macro to support the following:

Search Column C for cell(s) containing the content of B1 and then replace all instances of B1 (located in Column C) with the content of Cell A1. and so on, and so on.

I found what I believe to be the solution here in an old thread below although the solution does not appear to be attached! Please help.

http://forums.techguy.org/business-applications/1032256-solved-macro-multiple-find-replace.html
 

More replies
Relevance 90.2%

Hello again!
I am having trouble with a macro that is supposed to perform the following tasks:

Prompt the user to input a cell where the drop down list is to be located
Prompt the user for a range of data to use in the drop down list values
Create drop down list
Hide the rows in which the drop down list values are located

I originally recorded a macro to do this, and then I am tweaking the code accordingly. What is troubling me is getting the macro to select a range from an input box, the using that input to create a list. It's the partcular language included in the .Add command that I do not understand I defined the input box as a range variable celRng, and I am trying to get Formula1: = celRng. But it is not working.

I have attached the code below, and marked in which lines the errors are located:


Code:
Sub CreatDropDownList()
'
' CreatDropDownList Macro
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Dim celNm, celRng As Range
On Error Resume Next
Application.DisplayAlerts = False
Set celNm = Application.InputBox(Prompt:= _
"Please select a cell to create a list.", _
Title:="SPECIFY Cell", Type:=8)
On Error GoTo 0
Application.DisplayAlerts = True

If celNm Is Nothing Then
Exit Sub

Else
With Selection.Validation
.Delete
On Error Resume Next
Application.DisplayAlerts = False
... Read more

Answer:Excel Macro to Create a Drop Down List and Hide Cells

DJL,

It looks to me like you open the 'With Selection Validation' statement without closing it before the If statement.
Try adding End With before the If statement above your error, then add another 'With' Selection Validation' after the Else statement.

You can also put msgbox = celRng after the else statement to check the variable value before the error.

Stoneboysteve
 

5 more replies
Relevance 89.79%

Hi,
I was wondering if it is possible to trigger a macro when an particular custom tab is clicked.
This is part of my XLM code from the Excel file:
Code:

<!-- Add Custom group to the Home tab in the ribbon with one button-->
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon startFromScratch="false">
<tabs>
<[COLOR="Red"]tab id="customTab" label="Inzet Planner" insertBeforeMso="TabHome[/COLOR]">
<group id="customGroup1" label="Planner Maand Selecties" >
<button id="cG1B1" label="Huidige Maand" size="large" onAction="BtnOnActionCall" imageMso="ArrangeByAppointmentStart"/>
etc ....

It all works perfectly for the buttons, that's not the issue.
What I am looking for is that if CustomGroup1 is pressed a macro is triggered. An then of course I have a series of groups and that can be a different macro or no macro at all.
I tried sendKeys and so but that does not work well.
Like the Button Id has an onAction function, is there something similar for when a Group is selected?

Thanks for any answers
 

Answer:Solved: Excel 2010 trigger macro when a ribbon tab is selected

15 more replies
Relevance 89.79%

HI,

I have a huge product listing that i need to find and replace on a mass scale. I have one spread sheet that has 3 columns (see example below)
Column A****************Column B**********************Column C
1************************ M2123*************************M3455
2************************M3455*************************M3455
3************************M3433*************************M3455
4************************M6543*************************M3433
5************************M8793*************************M3433
6************************M6543*************************M7832
7************************M7832*************************M7832
8************************M4893*************************M7832
9************************M3938*************************M2123
10**********************M4837*************************M2123
*********************************************************M2123
*********************************************************M2123
*********************************************************M4837

In essence Column A is the id for column B (A=B), column C is longer than column a and b, because it contains duplicate (M)numbers. I would like to use the defined id in column a to replace the (M)numbers in columns c

So the spread sheet with the end result would look like this

Column A****************Column B**********************Column C
1************************ M2123*************************2
2************************M3455*************************2
3************************M3... Read more

Answer:Solved: Macro Multiple Find and Replace in Excel 2010

11 more replies
Relevance 89.79%

I'm trying to create a macro that will sort on numerous fields. The macro will be stored in my "personal" file as I need to use it in a new report every month. I think I know what the probelm is. It looks like it hard coded the name of the worksheet where I originally created it. I need the highlighted piece to be flexible depending on whatever file is open.

Sample portion of macro starting at the beginning...

ActiveWorkbook.Worksheets("PWQ QA Report v.05").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("PWQ QA Report v.05").Sort.SortFields.Add Key:= _
Range("O2:O15173"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
ActiveWorkbook.Worksheets("PWQ QA Report v.05").Sort.SortFields.Add Key:= _
Range("D215173"), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("PWQ QA Report v.05").Sort.SortFields.Add Key:= _
Range("E2:E15173"), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
 

Answer:Solved: Sort Macro for multiple files- Excel 2010

6 more replies
Relevance 89.38%

Gents,

I have excel file which has a huge data for projects and every day I'm going to add data a minimum of 10 and I have to use scroll down tool. It is possible to create Add button which will bring focus to the next empty column.

Any help is much appreciated.

Best regards,
 

Answer:Create Add button in excel 2010

the control key and the End key together will take you to the bottom row but the righthand column of used data
if you click on the column you want to start entering data
and then use the control key + the down arrow key will take you to the last entry in that column - MAY NOT be the bottom of the spreadsheet though - not sure how your data is structured

otherwise a macro could be written and added to a button or just run
 

3 more replies
Relevance 89.38%

I am currently trying to create appointments in a calender i have created in outlook using information from an excel spreadsheet. I have copied several codes from other forums to help with this Macro but have only been able to add the appointments to my calender.

The code i am currently using is:

Sub ExportAppointmentsToOutlook()
Dim olApp As Outlook.Application
Dim olApt As AppointmentItem
Dim blnCreated As Boolean

'Read the table with appointments:
Dim arrAppt() As Variant, i As Long
arrAppt = Range("A2", Cells(Rows.Count, "E").End(xlUp)).Value
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
blnCreated = True
Err.Clear
Else
blnCreated = False
End If
On Error GoTo 0
'Create the outlook item for the table entries:
'Rows:
' Row 1 = date
' Row 2 = starttime
' Row 3 = endtime
' Row 4 = Description
' Row 5 = Location
For i = LBound(arrAppt) To UBound(arrAppt)
Set olApt = olApp.CreateItem(olAppointmentItem)
With olApt
.Start = arrAppt(i, 1) + arrAppt(i, 2)
.End = arrAppt(i, 1) + arrAppt(i, 3)
.Subject = arrAppt(i, 4)
.Location = arrAppt(i, 5)
.Body = "Created by excel tool"
.BusyStatus = olBusy
.ReminderMinutesBeforeStart = 5
.ReminderSet = True
.Save
End With
Next i

Set olApt = Nothing
Set olApp = Nothing
End Sub
I also have the calender location but have no idea where it would need to fit into... Read more

Answer:Macro/VBE to create appointments in Outlook from an Excel Spreadsheet (Office 2003)

16 more replies
Relevance 88.56%

I am working with a spreadsheet to track shipping information. I have a table with columns A - J and 5000+ rows. I need to sort the data into new worksheets, according to the value in column B, and named after the value in column B. Each of the rows with the same value in column B need to be transposed to the new worksheet titled for that value.

Answer:macro to create & name new worksheet

re: "I need to sort the data into new worksheets.."Thanks for letting us know what you need.If you want some help with that, feel free to ask.Posting Tip: Before posting Data or VBA Code, read this How-To.

2 more replies
Relevance 88.56%

I am creating an inventory sheet in excell.I would like to create a macro button on first spreadsheet. When you click the button, it would would look for values in column A found in column B (which will be hidden). Example:Column A Column B234 234134 174123 123134 133If the values are present or not present, a summary report would be generated in another sheet that would report the following:Column A Column B234 In Stock134 Not in Stock123 In Stock134 Not in StockCould you send me instruction to create the macro button which will do the above and create a summary report in excel 2010?

Answer:Creating a Macro button/ Sending data/ Reporting Excel 2010

I just put something together really quick for you. I haven't tested this, but I have created very similar Macros before. You will need to modify this a little, but for the most part, I think this may do what you want.Sub FindInventory()
Dim NewSheet
Dim DateValue$
Dim FindString As String
Dim Rng As Range
Range("A:B").EntireColumn.Hidden = False
FindString = InputBox("Please Enter full text of value you want to find", "You Must Enter something!")
If Trim(FindString) = "" Then
MsgBox "You did not enter something. Lookup cancled."
Range("A:B").EntireColumn.Hidden = True
Exit Sub
Else
With Sheets("Sheet1Name").Range("A:A")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
Else
MsgBox "No Records Found."
Range("A:B").EntireColumn.Hidden = True
Exit Sub
End If
End With
End If
Set NewSheet = Sheets.Add(After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet)
NewSheet.Name = "InventoryReport"
Selection.Copy
Shee... Read more

8 more replies
Relevance 88.56%

Hi Guys,

I need help creating a dynamic macro that could save me alot of time in the future.

In a nutshell:

- "Name" value in Column A
- "Yes" or "No" value in Column B
- "Name" value in Column C

I need:

- Every cell in Column C to be crossed referenced with Column A
- If there is a match, I need the value in Column B to be changed to Yes

Before:

------Column A ------------------------------ Column B ------------------------------ Column C -------
-------------------------------------------------------------------------------------------------------------------------
-- Jen NO Jen --
-- Jane NO Jane --
-- Sarah NO Sarah --
-- Mike NO James --
-- Mitch NO Fred --
-- Joy NO Lee --
-------------------------------------------------------------------------------------------------------------------------

After:

------Column A ------------------------------ Column B ------------------------------ Column C -------
-------------------------------------------------------------------------------------------------------------------------
-- Jen Yes Jen --
-- Jane Yes Jane --
-- Sarah Yes Sarah --
-- Mike NO James --
-- Mitch NO Fred --
-- Joy NO Lee --
-------------------------------------------------------------------------------------------------------------------------

Thanks for the help,
Ben
 

Answer:Need Help - Excel 2010 - Macro - Find, if Found Replace Text In Another Column

As just a Formula, put this in column B ' =IF(A1=C1,"Yes","No")'

As a Macro -
Sub MatchReplace()
Dim LastRowColA As String
LastRowColA = Range("A65536").End(xlUp).Row
For i = 1 To LastRowColA
Range("B" & i).Select
ActiveCell = "No"
If ActiveCell.Offset(0, -1) = ActiveCell.Offset(0, 1) Then ActiveCell = "Yes"
Next
End Sub
 

2 more replies
Relevance 88.56%

Here is part of my code where it errors out in 2013 for the sort .apply but not 2010 and can't figure out why. Help appreciated. I have recorded same in 2013 with same results.
Code:

Application.DisplayAlerts = False
Workbooks.Open Filename:="C:\Temp Data\mydata.csv"
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A:$AM").AutoFilter Field:=33, Criteria1:="<>"
Cells.Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Range("A1").Select
ActiveSheet.Paste
Sheets("Mobility").Select
ActiveSheet.Range("$A:$AM").AutoFilter Field:=33, Criteria1:="="
Application.CutCopyMode = False
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Range("A1").Select
ActiveSheet.Paste
Range("AC2").Select
Application.CutCopyMode = False
Sheets("Sheet2").Select
Cells.Select
Range("U1").Activate
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range( _
"Q:Q"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet2").Sort
.SetRange Range("A:AM")
.Header = xlYes
.MatchCase = F... Read more

Answer:Solved: Excel 2013 Sort Macro errors out at .Apply but not in 2010

13 more replies
Relevance 88.56%

Hello all. Not a regular user of Excel; but do need help in creating something that would be useful to me and a few others at work. I suppose the best way of explaining what I'm after is by giving an example.

I have a directory C:\Users\Tekko\Desktop\Maintenance Project\Cape Nelson
In Cape Nelson are a number of folders named alpha beta charlie delta echo and foxtrot and so on.

I would like to have an excel template in "Cape Nelson" with a macro that when activated names the file as whatever folder name might be in say cell A1 and whatever ever date might be in cell B1. Eg charlie_15-mar-2013.xls
This then is saved in the relevant folder. So in the end I would end up with
C:\Users\Tekko\Desktop\Maintenance Project\Cape Nelson\charlie\charlie_15-mar-2013.xls

Also the macro script would ensure that the macro was disabled in the saved file.

Hoping this is achievable and look forward to replies.
 

Answer:Solved: Excel 2010 - Macro to name and save file to a specific folder

16 more replies
Relevance 88.56%

Hello,

I am creating histogramm for statistics course. How can I please add two adjacent Y-axes to histogramm?

Typical example image of histogram is attached.

Thanks.

Answer:How to create two adjacent Y-axes in Excel 2010

Hello Dodge1, welcome to Seven Forums.

Looks to me like the answer in this thread might do the trick:
How can I create a Dual Y axis in Excel? - Yahoo! Answers

Good luck! Let us know if it works.

1 more replies
Relevance 88.56%

Hello,

I need help with a formula. I need the formula to calculate totals from 10 worksheet.

Totals to come from C164 from each sheet to total in the summary worksheet D20. I can't seem to get it past the first worksheet. This is the formula that I have. The work sheet are named xx1-xx10

=Sum(xx1!c164,xx2!c164,xx3!c164,xx4!c164,xx5!c164,xx6!c164,xx7!c164,xx8!c164,xx9!c164,xx10!c164) IIs this possible?
Please and thank you!

Answer:Excel Formula using multiple worksheet to calculate into a summary worksheet.

Moving thread to the Office Forum for better results

2 more replies
Relevance 88.15%

Thanks for reading, first of all.

I am not the most savvy person when it comes to programming. I can manage my way around formulas pretty well, but when it comes to VBA and macros I am utterly lost. I believe I need a macro to do what I want, but I have no clue where to begin. I have searched forums but what I need is so complicated that I don't know where to look to find it so I thought I would ask for help.

I have a spreadsheet which tracks projects I am working on. Right now the sheet I am working on is "2013" for example. The sheet has several columns, but the only columns of significance to this macro are:

Account Name
Requested Date
Urgency
Due Date
Completed Date
Request
Receive
Complete

Account and Requested Date are currently manual entries.
Urgency is a data validation dropdown with: NORMAL, RUSH and OTHER.
Due Date is a formula which calculates a due date based on the Urgency selected. (Except Other, in which case I override the Due Date manually.)
Completed Date is also a manual entry.
The 3 stages are data validations which default to a blank cell and then have a Square Root sign (which looks like a checkmark) as the only other option.

Now that I have explained the source data, allow me to go into detail what I would like this to do.
I would like to populate the first sheet of the spreadsheet with seven lists. These lists would, ideally, tell me at what stage each project is at (by account and due date). The lists I have set up are in seven bo... Read more

More replies
Relevance 87.74%

Hello my fellow databasers.

The Information:
I am currently working for a company that is tracking their part(s)
expenses per each customer job.

All customers' expenses are entered into the same 'primary' workbook so entering information is easier than going to each customer workbook. The column headings are as follows.

Customer Name - Date - Part Used - Cost - Invoice # - Additional Notes

There is 1 primary workbook and then each customer has their own workbook.

I have researched for a week now and am not savvy enough with Excel to know exactly what I should search for. I believe a macro is what I want though.
The question:
How, or what would I do, to have each workbook search for its relative 'Customer Name' and paste (auto populate) the information into the workbooks?

ex. I entered a part for John Doe within Primary Workbook. The second workbook sees John Doe, copies the row of all parts that have John Doe in the first column and populates it in the second workbook.

Also, would I need to create a separate Vlookup first to add a value to my customers so it wasn't text based to help the above equation work?
I know I could copy and paste each one, but sometimes we retroactively change values of costs, and for the amount of time I could save by creating ... a macro? or some formula would be well worth continued research. Any help is appreciated.

Application:

It's important for my employer to be able to see what parts were used ... Read more

Answer:Solved: Excel 2010: Macro or substitute to automatically copy data between workbooks

hi
A macro can do what you need but have you considered using filters on the primary workbook where you can select the customer name and only those records will be displayed? This will eliminate the need for multiple workbooks and make your life much simpler. An added benefit would be the ability to filter on other criteria like Date Or Part # so you can see across Customers what occurred on a specific date or which customers ordered a specific part.
Alternatively, this would be an ideal application for Access which would make data entry simpler and provide the information you need in the format you need.
 

2 more replies
Relevance 87.33%

I need to change the color of cells F6:F39 if F5="Actual". Can this be done? If yes. how? Thanks!

Answer:how do i create if statement in 2010 excel to change cell co

Use Conditional Formatting.Select F6:F39Home RibbonConditional FormattingNew RuleUse a Formula...=$F$5="Actual" (Make sure you use the dollar signs as shown)Select your formatClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

4 more replies
Relevance 87.33%

In excel 2010 new sheet, I would like to enter order number on column A1 then column B2 will show time and date automatically then after entered data then A1 and B1 should locked the cell and won't change anymore. Is this possible to do it?Thanks

Answer:Auto create time and date in Excel 2010

If B2 will be empty the first time an order number is entered, then this code will enter the date and time once and will not change for subsequent changes to A2.If there will already be something in B2, then things get more difficult and I will need more specifics.(BTW...I am assuming the A2 and B2 are just examples. The code actually applies to the entire Columns A & B)
Private Sub Worksheet_Change(ByVal Target As Range)

'Determine if change was made to Column A
'and respective row in Column B is empty
If Target.Column = 1 And Cells(Target.Row, 2) = "" Then

'Unprotect Sheet, Enter Date/Time in Column B, Protect sheet
ActiveSheet.Unprotect Password:="zzzzz"
Cells(Target.Row, 2) = Now
ActiveSheet.Protect Password:="zzzzz"
End If
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

8 more replies
Relevance 85.69%

Hi Wayne, I have another one...

I have two Excel Sheets. One is a list I call database with all of the information in a list. The second sheet is ?prettied up? into a the looks of a nice form (but not a form, just formatted to look nice).

I have the same column names in both locations. The list layout is landscape and the ?form? is portrait. The data in each row of the list/database is what I would like to have prepopulate into the ?form? below to print a single record, but I can?t figure out how to make this work. The data in the list will change continue to have data added at the bottom.
 

Answer:Excel - Prepopulating a worksheet from a worksheet list

would you change the image to an actual spreadsheet with a sample
also the image is a screen shot of the other post
 

1 more replies
Relevance 85.69%

I would like to have a macro create a new worksheet naming it the value of the cell in the second row when I click on the 5th cell in the same row.Here is an example of my data.A B C D E(Blank) Asset ID Service Class Unit # Details L01L22A26 Class 1 01 Details L03L01A36 Class 1 03 Details L03L06A08 Class 1 03 Details L03L07A44 Class 1 03 Details L03L07A45 Class 1 03 Details L05L01A01 Class 1 05 DetailsSo, when I would click on Details in the last column, a new worksheet would populate named L01L22A26. Then I would like to have the data in the column B and C of that row, copied over to the new worksheet at Range D2:E2. I have thousands of rows of data and only want them to create a new sheet when clicked.Any help is greatly appreciated!! Thank you in advance for your time.

Answer:Macro to create new worksheet based on cell clicked

re: ...create a new worksheet naming it the value of the cell in the second row...The second row? If you are always going to be copying data from the second row, why does it matter that you have "thousands of rows of data"?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 85.28%

Hello again,

I have a word doc that has a form in it. The form may have any number of fields (boxes) to type text answers into, or be a drop down list to select an answer from. I need an excel macro that extracts all data from the form, but with every five fields starting a new row in excel. So all data from the form will appear within 5 columns in an excel spreadsheet.

field 1 field 2 field 3 field 4 field 5
field 6 field 7 field 8 field 9 field 10
etc

Is that possible? I have had no luck to do it myself.

Sample form attached, thank you
____________________________
 

Answer:Solved: Excel macro to extract Word form data into Excel

7 more replies
Relevance 85.28%

Im using Windows 7 and I used a macro which worked totally fine under Excel 2003. Now, under Excel 2007 it does not run anymore but displays the following error message:

"Run-time error '445':
Object doesn't support this action.

The function of the macro is to open up all other Excel spreadsheets located in the same folder and copy data out of them into the spreadsheet in which the macro is stored.

When I click on "Debug" the Visual Basic Editor highlights the row which says: Set FilSrch = Application.FileSearch

Can you help me with this? I've searched for a solution for a long time but could not find anything which worked.

The following is the start of the macro code:

Sub UpdateTable()
Dim X As Range, I As Integer, J As Integer, RecNo As Integer, CopyVal As Variant
Dim SourceBk As Worksheet, DestBk As Worksheet, StartRow As Integer, IndRange As Range
Dim ServRange As Range, SrcOpen As Boolean, SourceName As String
Dim FilSrch As Object, MyFilArray() As String

'MsgBox "This function not available in this version.", vbInformation, "Function Not Available"
'Exit Sub
'Sheets("LookUp").[a12] = Now
With Application
.ScreenUpdating = False
.StatusBar = "Counting source files ..."
End With
Set FilSrch = Application.FileSearch
With FilSrch
.NewSearch
.LookIn = ActiveWorkbook.Path
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
ReDim MyFilArray(.FoundFiles.Count)
For I = 1 ... Read more

Answer:Solved: Excel 2003 Macro Doesn't Run in Excel 2007 - Help Requested

7 more replies
Relevance 85.28%

Hi,

I was wondering if anyone could help me. I need to extract data from multiple excel files into a new excel file and sum the values. The data comes from excel files that are made from a template so the data is always in the same place or same cells. For example, i have data saved daily in excel files with the following naming convention 080204, 080304, 080404,..... and so on. What i would like to do is have a macro that will create a week ending sheet by importing and summing the data from that week.

Thanks for reading!
 

Answer:excel macro for copying and pasting data from multiple excel files

thanks for all those who read!!!! let me know if anyone needs the code!
 

1 more replies
Relevance 85.28%

I am trying to create a code that will autofilter data on an Excel worksheet. The code works fine to filter the data, but it does not hide the AutoFilter Arrows.

Below is the code I am using. What (if anything) is wrong with the code below?
Code:

ActiveSheet.Range("A1:D500").AutoFilter Field:=2, Criteria1:="<>", Visibledropdown:=False
 

Answer:Solved: Excel 2007 Macro -> Hide Excel AutoFilter Arrows

I noticed that the code above (in Post #1) that the AutoFilter Arrows were beinf truned off on Field 2, but none of the others.

I have played around, and done some research to come up with the code below:
Code:

Dim c As Range

For Each c In ActiveSheet.Range("A1:D1")
ActiveSheet.Range("A1:D500").AutoFilter Field:=2, Criteria1:="<>"
c.AutoFilter Field:=c.Column, Visibledropdown:=False
Next

Does anyone have a better solution?
 

1 more replies
Relevance 85.28%

Hello,

I need help to create an Excel macro that would

1. ask users to select x number of rows to be copied from one worksheet of one Excel file
2. once users have selected the rows to be copied, then the rows are copied from the one original Excel file into X number of target Excel files

Notes:
the target Excel files are all based on the same template
there is one worksheet in each of the target Excel files

in the target files, the rows should be copied from the first available empty row, going down

Looking forward to your help!

Thanks a lot.
Mzz
 

Answer:Macro needed to copy x number of rows from one Excel file into a batch of Excel files

Hi, welcome to the board.
Not much info there to get the correct picture.
Sample? Of source and template please
And not to forget, what version of Excel are you and the users using?
 

1 more replies
Relevance 84.87%

Hi,
I want to merge two excel files into a 3rd one.

The data are in the files Book1, Book2 and the result in Book3.

I know that i can use VLookup to do the above. But what i have pasted is just an example. The real excel files contains about 9 columns with at many repeating row!!!

Any help will be much appreciated.
Thx
 

Answer:Excel - macro to merge 2 excel files using a common column

Hi welcome to the board. How are you going to treat the duble items, If you want to merge 2 files adn duplication occurs here must be one cell whihc is common to let's say Book1 and Book2.

Do you have a common value? Index? Whatever you call it?
 

3 more replies
Relevance 84.87%

I'm working on a Bill of Material creation automation project that requires some expertise in VBA, and I have none.
The objective is to run a macro from an excel spreadsheet called "PGE BOM", to do the following:
1) Go to the folder "C:\Documents and Settings\Desktop\Auto Project"
2) Find all the .mdb databases in this folder
3) Find "HistoricalMaterialItemsAll" table in EACH of those databases in step 2, and import the data from the columns listed below into PGE BOM.xls's columns C through G:
DrawingNumber
ItemNumber
Quantity
PgeCode
Description
The following is a VBA code that my friend had written in Excel 2007. Unfortunately I have an older version (2000) and the code does not seem to be compatible with Excel 2000.
Sub ImportAccessData()
Stop
dPath = "C:\Documents and Settings\Desktop\Auto Project\"
sFile = "*.MDB"
strSrch = dPath & sFile
Set TargetWB = Application.ActiveWorkbook
Set TargetWS = TargetWB.ActiveSheet
sRow = 2
bFile = False
If Dir(strSrch) <> "" Then
strFlNm = Dir(strSrch)
bFile = True
End If
Do Until bFile = False
strPath = dPath & strFlNm
Call GetData(strPath)
strFlNm = Dir
If strFlNm = "" Then bFile = False
Loop
End Sub
Sub GetData(fl)
Stop
strSQL = "Select HistoricalMaterialItemsAll.* From HistoricalMaterialItemsAll"
Workbooks.OpenDatabase fl, strSQL, xlCmdTable
Set WB = Application.ActiveWorkbook
Set WS = Applicatio... Read more

Answer:Excel Macro - VBA code to import access data to excel

Hello, and welcome to the board!

When posting code, please use CODE tags, which extremely helps with readability.

Perhaps you could try using the code constant???...
Code:

Option Explicit

Sub ImportAccessData()
Dim dPath As String, sFile As String, strSrch As String
Dim TargetWB As Workbook, TargetWS As Worksheet
Dim sRow As Long, bFile As Boolean, strFlNm As String, strPath As String
dPath = "C:\Documents and Settings\Desktop\Auto Project\"
sFile = "*.MDB"
strSrch = dPath & sFile
sRow = 2
bFile = False
Set TargetWB = Application.ActiveWorkbook
Set TargetWS = TargetWB.ActiveSheet
If Dir(strSrch) <> "" Then
strFlNm = Dir(strSrch)
bFile = True
End If
Do Until bFile = False
strPath = dPath & strFlNm
Call GetData(strPath, TargetWB, TargetWS)
strFlNm = Dir()
If strFlNm = "" Then bFile = False
Loop
End Sub

Sub GetData(fl As String, WB As Workbook, ws As Worksheet)
Dim strSQL As String, iRow As Long, sRow As Long
strSQL = "SELECT HistoricalMaterialItemsAll.* FROM HistoricalMaterialItemsAll"
Workbooks.OpenDatabase fl, strSQL, 3 'xlCmdTable
iRow = 2
Do Until ws.Cells(iRow, 1) = ""
ws.Cells(sRow, 7) = ws.Cells(iRow, 5) 'Get the Description
iRow = iRow + 1
sRow = sRow + 1
Loop
Application.DisplayAlerts = False
WB.... Read more

1 more replies
Relevance 84.05%

I run a weekly excel 2007 macro that converts a spreadsheet to a Word document, but it stops after row 29 of data. Any spreadsheet with 29 rows or less works fine. If I have 34 rows of data, the macro hangs. See below for script. Any help would be appreciated.

Public Sub AddControls(WrdApp As Word.Application, CurRow As Integer)
Dim OptChecked As Boolean
Dim GrpName As String
GrpName = "Grp" & CurRow
'Calculate the colors based on the cell information.
CurRange = "F" & CurRow & ".." & "F" & CurRow
Range(CurRange).Select

If InStr(1, UCase(ActiveCell.Text), "X SRM") > 0 Then 'SRM
OptChecked = True
Else
OptChecked = False
End If

SetCellBG WrdApp, True
WrdApp.Selection.TypeText Text:="Governance:" & vbCrLf

Set RadioObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.OptionButton.1")
With RadioObj.OLEFormat.Object
.GroupName = GrpName
.Caption = "SRM"
.Font.Name = "Arial"
.Font.Size = 8
.Value = OptChecked
End With

If InStr(1, UCase(ActiveCell.Text), "X PPO") > 0 Then ' PPO
OptChecked = True
Else
OptChecked = False
End If

WrdApp.Selection.Collapse Direction:=wdCollapseEnd
WrdApp.Selection.TypeParagraph

Set RadioObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.OptionButton.1")
With RadioObj.OLEFormat.Object
.GroupName = GrpName
.Caption = "PPO"
.Font.Name = "Arial"
.Font.Size = 8
.Value = O... Read more

Answer:Excel Macro - converting excel to word doc - stops after row 29

13 more replies
Relevance 84.05%

Hi, I have created an excel macro that runs sensitivities in my model. The excel button runs 5 different cases.

The macro runs fine and does as I want it to.

Once the macro has run, and I go to edit a cell and hit "F2" excel crashes. This only seems to happen in certain cells and not all of the time.

Does anyone have experience with similar questions?

Thanks
 

Answer:Excel Macro runs fine..then excel crashes

11 more replies
Relevance 84.05%

Does anyone know how to run Excel 2003 print macro on Excel 2007 or higher? When I tried running my 2003 macro, the VBA editor pops up w dialogue box, " Run-time error '1004': Method 'ActivePrinter' of object'_Application' failed ".

This is my code: Application.ActivePrinter = "Epson LQ-300 ESC/P 2 on USB003"

Can anyone help?

Thanks!
 

Answer:How to run Excel 2003 print macro on Excel 2007?

Welcome to TSG hope we can help you and you can help others.

I'm not an Excel macro guy but you code looks reasonable. What happens if you run this. I suspect printer name is a little off.

MsgBox "The name of the active printer is " & Application.ActivePrinter
 

1 more replies
Relevance 84.05%

I used a macro which worked totally fine under Excel 2003. Now, under Excel 2007 it does not run anymore but displays the following error message:"Run-time error '445':Object doesn't support this action.The function of the macro is to open up all other Excel spreadsheets located in the same folder and copy data out of them into the spreadsheet in which the macro is stored.When I click on "Debug" the Visual Basic Editor highlights the row which says: Set FilSrch = Application.FileSearchCan you help me with this? I've searched for a solution for a long time but could not find anything which worked.The following is the start of the macro code:Sub UpdateTable()Dim X As Range, I As Integer, J As Integer, RecNo As Integer, CopyVal As VariantDim SourceBk As Worksheet, DestBk As Worksheet, StartRow As Integer, IndRange As RangeDim ServRange As Range, SrcOpen As Boolean, SourceName As StringDim FilSrch As Object, MyFilArray() As String'MsgBox "This function not available in this version.", vbInformation, "Function Not Available"'Exit Sub'Sheets("LookUp").[a12] = NowWith Application .ScreenUpdating = False .StatusBar = "Counting source files ..."End WithSet FilSrch = Application.FileSearchWith FilSrch .NewSearch .LookIn = ActiveWorkbook.Path .FileType = msoFileTypeExcelWorkbooks If .Execute > 0 Then ReDim MyFilArray(.FoundFiles.Count) For I = 1 To .FoundFiles.Count If .FoundFiles(I) <> ActiveWorkbook.Path & "\" & ActiveWorkbook.Name Then ... Read more

Answer:Excel 2003 Macro doesn't run under Excel 2007

As far as I know, Application.FileSearch is not available in 2007. Searching for filesearch replacement turns up lots of hits with various workarounds, such as this:http://groups.google.com/group/micr...Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 84.05%

Hi guys,

I have been on the lookout for a macro that will compare 2 worksheets within MS Excel and output the differences on a third sheet.

I am being directed to the URL below but cannot for the life of me work out how to implement this:

http://exceltip.com/st/Compare_two_worksheets_using_VBA_in_Microsoft_Excel/477.html

There are two snippets of code which I will include here for convenience but not sure whether these should be pasted into Workbook Code (i.e. right-clicking Excel icon left of "File" - top-left), Worksheet Code (i.e. right-clicking worksheet tab) or inserting a new Module - or some other method. Is there anyone here that could guide me to how to implement this code?

Quote from webpage - "With the macro below it is possible to compare the content of two worksheets.
The result is displayed in a new workbook listing all cell differences. "

Code:
Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
Dim r As Long, c As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long
Application.ScreenUpdating = False
Application.StatusBar = "Creating the report..."
Set rptWB = Workbooks.Add
Application.DisplayAlerts = False
While Worksheets.Count > 1
Worksheets(2).Delete
Wend
Application.DisplayAlerts = True
With ws1.UsedRange
lr1 = .Rows.Count
lc1 = .Column... Read more

Answer:Solved: How to use this Macro in MS Excel (compare excel worksheets)

Apologies... I have sorted this... For those that might be looking at this for help here's what I missed:

These are both Sub's that should be created as a Module - both in the same module. I am just comparing worksheets within the Active Worksheet so just removed:

' compare two different worksheets in two different workbooks
CompareWorksheets ActiveWorkbook.Worksheets("Sheet1"), _
Workbooks("WorkBookName.xls").Worksheets("Sheet2")
Click to expand...

I was then able to run this from Macro's. All sorted.

Apologies again
 

1 more replies
Relevance 82.41%

A macro I wrote in VBA for Excel 2004 won't run in Excel 2011 (alas...). Upon opening the spreadsheet an "on open" routine runs, and bombs out on the following command:
Code:
Set cmdbar = Application.CommandBars.Add _
(Name:="Sort", _
Position:=msoBarTop, _
MenuBar:=False, _
temporary:=True) 'msobartop docks the menu at the top

Does anyone know if the VBA structure ("CommandBars.Add") has been removed or altered?

Is there another way to accomplish the same thing in 2011? I want to create a custom command bar (I populate it later in the module).
 

More replies
Relevance 82.41%

Does anyone know the best way to setup a Macro in Excel 2007 that will work in Excel 2003 as well?

I have an Excel 2007 workbook that I am using a macro to change the "Fill Color" and "Font Color" of cells. However, I will get everything working in Excel 2007, but when I open the workook in Excel 2003 I get many debug errors.

If I change the code in Excel 2003, and then re-open in Excel 2007 I get debug errors.

There must be a way to setup teh workbook to work on both Excel 2007 & 2003.
 

More replies