Computer Support Forum

excel - saving data to another worksheet

Question: excel - saving data to another worksheet

Hi, i have a question in creating a file in which i can fill out the worksheet "form" and by clicking a save button, the data will be saved into worksheet "database" in the next row. basically i do not need to amend data from the "form", but i will rather update in "database" manually.

thx for your help in advance

Relevance 100%
Preferred Solution: excel - saving data to another worksheet

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 - saving data to another worksheet

Hi there, welcome to the forum.

I think you should take a look at the link blow, this is something similar

http://www.contextures.com/xlForm02.html

If you get stuck, let us know.

1 more replies
Relevance 83.23%

Hi,

I am sure this is going to be something simple?
When entering a number in a cell (i.e mobile) and clicking away from the cell
the zero is not saving? i.e 7766.
I did go into the format options and play about with the numbering and general options but was unable to solve?
 

Answer:Zero not saving in Excel worksheet?

First select the column and format as text.
Or enter with an apostrophe in front of the value.
Or you could format, for instance, a 7-digit number with a custom format of 0000000
 

3 more replies
Relevance 79.95%

I am exporting report data from an accounting program into Excel 2003, then having to change some of the worksheet data into to a specific format (some columns show dates, phone numbers, etc - which need to be converted to text - translation:HEADACHE) and then saving file as ".csv" to submit reports online to another agency. I am pulling my hair out here because it keeps changing to funky formats in the .csv sheet and I find myself back at square one. I am required to do this on a monthly basis, and will soon be submitting a similar file to a different agency, so I will double my headache if I can't get my head wrappred around this.

Any help would be great!!!

Thanks.

I'm running XP Pro sp3, Office 2003
 

More replies
Relevance 79.13%

Hey All,

Is there a way in Microsoft Money 2004 to open up my checking account register and than export (copy) the account register to an Excel Worksheet? I don't want to just send a report, I want to copy my checking account to an Excel Workbook so that when I open the Excel Worksheet, it looks like a checkbook register.

I than want to be able to enter data into my Excel Workbook and use this as a backup of my check register.

Jack
 

Answer:Solved: Saving My Microsoft Money Check Register as Excel Worksheet

You should be able tho' I can't say for sure (haven't used Money for a few years).

This article may help.

Re: "a backup of my check register", I definitely remember Money having in-built capability to back up to floppy disk. ( )
 

2 more replies
Relevance 77.9%

Please can anyone remind me how to type in the formula for accessing data from worksheet 1 and using the data on worksheet 2 for instance all in the same work book. I used to do it but forgot how . I am using XP.

Answer:Excel - using data from other worksheet

Something like='First Sheet'!A1 + 'Second Sheet'!C3The ' are only necessary if there are spaces in the sheet name.

2 more replies
Relevance 77.08%

Hi,

I would like to collect data out of another worksheet with more than 600 lines; this worksheet is structured like this:

Column A: Name or "not required"
Column B: Sum

I'm OK with the formula which searches through column A and retrieves the sum of all amounts with the same name (=SUMIF) in column B.

What I can't figure out is a way to skip lines with "not required" or where a name has been found before, i.e.

A1:SOPHIE
B1:20.00
A2:SOPHIE
B2:50.00
A3:JOHN
B3: 40.00

should bring up in my new worksheet:

A1:SOPHIE
B1:70.00
A2:JOHN
B2:40.00

not:

A1:SOPHIE
B1:70.00
A2:SOPHIE
B2:70.00
A3:JOHN
B3:40.00

Cheers for any hints!

Andreas
(New Zealand)
 

Answer:EXCEL Collecting data from worksheet

10 more replies
Relevance 77.08%

I budget with Excel and was wondering if it was possible to send an email to a specified email address and upon receiving the mail, Outlook executing a rule or something similar and it editing an Excel worksheet for me. My main goal is to, after purchasing an item, sending an email or text to a specified email address and then that info going straight into an Excel worksheet. Yes, I know I can do this manually but just out of curiosity I'd like to know if this is remotely possible. Thanks.
 

Answer:Is it possible to email data to an Excel worksheet?

You could do that with a Powershell or VB script that checks periodically for new mail to a specific account and then parses the mail message for particularly formatted message data.

The machine running it would need to have Outlook or another email client that supports COM object automation installed.
 

4 more replies
Relevance 77.08%

I am using Excel 97 with two worksheets. On one worksheet I have a query pulling info from an Access database query. On the other worksheet I have a report that uses formulas and the information pulled. However, say the query fills up rows 1-10, and after updating the query it fills rows 1-14, the report worksheet does not look to the cells refernced in the formulas, it seems to look for the information within the cell it had before the update. It ignores any new data that has been added to the bottom and gives ref errors for any new data rows within the original 1-10 rows. The absolute and relative cell reference does not seem to make a difference.
What is up with this?
I was thinking maybe making the pulled infomation a linked object but I can't paste the querry as a link. I am not even sure that would make a difference if it is an issue between the two worksheets rather than between excel and access.
 

More replies
Relevance 77.08%

Okay so here is what i am trying to do I have a worksheet called template and i was modifying the below VBA to copy that Worksheet and then change the name to a date range. Now if i remove template from Set wsNew = ActiveWorkbook.Worksheets("template").Add(After:=Worksheets(Worksheets.Count)) it will work making new worksheets with the date range but with that template line it copies template but i need to know how to get it to rename that to the date range i need. Any help would be awesomeSub AddDatedWS()Dim strStartDt As StringDim strEndDt As StringDim dtStart As DateDim dtEnd As DateDim wsNew As WorksheetDim n As Double'get start datestrStartDt = InputBox("Enter start date", "Create dated worksheets")If Not IsDate(strStartDt) Then Exit Sub'get end datestrEndDt = InputBox("Enter end date", "Create dated worksheets")If Not IsDate(strStartDt) Then Exit Sub'convert text to Excel's date formatdtStart = CDate(strStartDt)dtEnd = CDate(strEndDt)'test if start date equal to or later than end dateIf dtStart >= dtEnd Then Exit Sub'confirm number of sheetsIf MsgBox("Create " & dtEnd - dtStart + 1 & " worksheets", vbOKCancel) = _ vbCancel Then Exit SubFor n = dtStart To dtEnd 'create a new worksheet Set wsNew = ActiveWorkbook.Worksheets("template").Add(After:=Worksheets(Worksheets.Count)) 'name it with a date (date text can't contain : \ / ? * [ or ]) wsNew.Name = Format(n, "mm.dd.yy")Next nEnd Sub

Answer:Excel Worksheet data range

First, a posting tip...Please click on the blue line at the end of this post and read the instructions on how to post data and/or VBA code in this forum.It looks like your code adds a new worksheet as the last worksheet in the workbook. Therefore, this code should rename the last worksheet by referring to it by its "sheet number" since Sheets.Count will always reflect the highest sheet number in a workbook.Sheets(Sheets.Count).Name = (Whatever you want your sheet name to be)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 77.08%

Extracting data from one Worksheet and populating to another using same value from both
Hi,
I have 2 spread sheets which have a primary value, linking (not a formula) both sheets together "account number".
Spread sheet A has the following columns:
account number, account name, postal name, address 1, address 2, address 3, address 4, postal code and account balance.
Spread sheet B has the following columns:
account number, account name and account balance.
I would like to populate spread sheet B with the following columns (contents) from spread sheet A:
postal name, address 1, address 2, address 3, address 4 and postal code.
This document will be getting used as a source for a mail merge job, thanks.
At the moment I have both sheets open and I'm using find and replace and copying from spread sheet A to spread sheet B, unfortunately there is nearly 2000 records, thanks.

More replies
Relevance 76.26%

Excel 2010I have a worksheet with address data arranged as one long list, with the title in Column A and the info I need in Column B, like so...A BName: Bob JonesAddress: 123 Main StreetCity: Northshoreetc...How can I extract and arrange the info in columns, like so...?A B CBob Jones 123 Main Street Cityetc...Thanks in advance for your kind assistance.message edited by SMoran

Answer:How to Extract Address Data from an Excel Worksheet?

As an alternative to Mike's fine suggestion, enter these formula in C2:C5C2: =B2C3: =B3C4: =MID(A7,FIND(":",A7)+2,LEN(A7))&", "&MID(A8,FIND(":",A8)+1,LEN(A8))&" "&MID(A9,FIND(":",A9)+1,LEN(A9))&" "&MID(A10,FIND(":",A10)+1,LEN(A10))C5: =""(C4 was stolen from Mike)Next, select C2:C22Next, grab the lower corner of C22 and drag downward.Assuming (as Mike noted) that each block of data contains the same number of lines, you should get something like this next to each block of data:360 Sign Group Inc.6525 Santiago CourtApollo Beach, FL 33572(A Blank Cell)Next:Select Column CPress [F5].In the resulting Go To dialog box, click Special.Click the Blanks option and click OK. Doing so highlights the blank cells in the selected range. Right-Click, Choose Delete, Choose "Shift Cells Up"You should end up with this:360 Sign Group Inc.6525 Santiago CourtApollo Beach, FL 33572 AAA Mobile Auto Glass IncP O Box 10574 St. Petersburg, FL 33733etc.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.message edited by DerbyDad03

7 more replies
Relevance 76.26%

Hi Admin members,
Well, I'm new to this site n I'd like to give a bit intro, that me is called Raj, done my master degree (DCA, BCA & Mgmt n then MCA-Master in Computer Application). I've done my study along with my job, n for the last ten years I've been working in Christian Medical College & Hospital, as a Software Engg. I've just a couple of years before completed my MCA. Also I'm lookin job in UK or USA.

I just wannted to know that how I can export the data into the excel worksheet and use in VB.Net Form. Can u send me the sample coding for this.

Thanx

Raj K. Masih
 

More replies
Relevance 76.26%

I need to write a formula to merge data from two Excel Spreadsheets. Both spreadsheets are located on my local “C” hard drive on my computer. The spreadsheets are used to track technician visits to stores. The primary spreadsheet is called “Compliance” and has mostly information about the stores. The second spread sheet is called “Access” and has information about the technicians and their visits to the stores. The data is alpha numeric. Each spreadsheet has a column of data in common which is called “Store Number”. On both spreadsheets the Store Number data occupies Column A.

I need to copy the four columns from my spreadsheet called Access onto my Compliance spreadsheet and have the data from Access line up with the data from Compliance by matching via the Store Number field. I would like to have all four columns from Access end up to the left of the data on my Compliance spreadsheet. I have been trying to set up a VLookup formula but I have not been able to get it to come out right, maybe I am using the wrong formula or going about this wrong.

If anyone has any suggestions they will be appreciated. Thanks.
 

Answer:Solved: Need Help Copying Excel Worksheet Data

9 more replies
Relevance 76.26%

Hi there all, can someone please help?
I have a scenario that I want to create but lack the required knowledge.
I have a worksheet created and working.
I want to be able to enter data into this worksheet, perhaps on Row 27.
I then want a selection of cells from this row to appear in another worksheet / workbook, as the next available empty row.
For instance,
when entering data into Row 27 of workbook 'NonWebPurchases' worksheet 'Hardware'
The data should automatically appear in some cells of row 42 of workbook 'Accounts' worksheet 'creditCard', this being the next available empty row
when entering data into Row 72 of workbook 'NonWebPurchases' worksheet 'CleaningSupplies'
The data should automatically appear in some cells of row 43 of workbook 'Accounts' worksheet 'creditCard', this being the next available empty row
Hope that this is clear and rather simple to solve.
I already link data from worksheet to worksheet without any problems.
Thanks in advance for ANY suggestions.
 

Answer:Excel worksheet linked data creation

Hi there, welcome to the board!

You'll need VBA for such automation. To do so, you'll need to clearly define what goes where and under what circumstances (logic) this should be done. Also, what if you change the data, or select the cell and enter the same twice, do we need to worry about duplication? Also, something to think about, doing this will generally have a large overhead in lieu of opening other files. It is best if you can keep the information contained within one workbook. Is this something where you could merge workbooks and move all sheets/data to one place? If so, that would generally be preferred.
 

1 more replies
Relevance 76.26%

Hello. I am hoping for a speedy response if possible.
I have about 2000 excel workbooks representing data for the last 4 years. Each workbook has 7 worksheets. I would like to create a new worksheet in a new file that will pull the value from one single cell in one single sheet from each of the other 2000 files. It is the same cell, same sheet in every file.
I have tried using microsoft query to pull the data but i can't seem to make it work. Does anyone know if what i am trying to do is actually possible? And if so can you tell me how? I think I am getting close on my own and I enjoy this sort of thing but I don't have the time! ANy assistance is much appreciated.
--Grace
 

Answer:inserting external data into an excel worksheet

I also should mention, as a disclaimer, that I did NOT generate these files. Had I done so, and known in what ways they would need to be used, I would have used Access. I'd like to avoid importing 2000 spreadsheets into Access if possible, but at this point it seems like it might be the easiest thing to do...
 

1 more replies
Relevance 75.44%

At work, our computer does not have Access, but it has Excel. I am creating a database of clients in Excel. Is there any way to enter contents into a cell, hit Tab button, and automatically move to the next cell to the right and at the end of the row, automatically Tab to the first cell in the next row(entering the next record, client)?
Thanks,
Fred
 

Answer:Auto Tabbing in Excel Worksheet Data Entry

Hey Fred,

Try this. When you enter data in a cell use TAB to go to the next cell and continue along using Tab until you need to go back to the first column. When you get to the last cell in that row, enter your data and then hit ENTER.

This then returns to the next row in the first column.

Hope that helps
B
 

3 more replies
Relevance 75.44%

Part of my question was answered in Solved: Formula For Duplicating
Started by floydcojacket, last post 13-Mar-2010 08:16 AM by bomb #21

However what I have done to date may or may not be the best way and I would appreciate guidance and advice. I have attached worksheet for tracking attendance for my team at work. I have tried (as a macro beginner) to have a summary sheet with names on. This needs to copy to the other tab sheets but names only. I have used tab links for all first summary tab and pivot table tab but only want to link names from summary tab.

I have a macro to sort all the sheets which works fine but omits summary sheet. I also have the button for a macro to delete a line (havent been able to make delete a line work as it removes whole line so that the full array of names starts to decrease as eah name is deleted. I need to be able to delete data from a line through all sheets withour decreasing number of lines in name field.

Sorry I am not explaining this too well but I think you will understand when you see the attached file. I am using Vista and Excel 2007 but need to be able to use in Windows 2003 at work.
 

More replies
Relevance 75.44%

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 74.62%

I need to be able to take one worksheet and see if the data on there is located on any other worksheet. I don't need to know which worksheet it is on, just if it exists. If there is a way to mark which worksheet the matching data is on, that would be better. Please see my example. On sheet 4, it is looking for matching data from worksheets A, B and C from column A. I tried a 3D reference but I don't think you can do that with Match from what I'm reading. Thank you!
 

Answer:Excel - Want to compare data from one worksheet to several others and mark true/false

Make 4 columns and use Match function fore one sheet a time!
 

2 more replies
Relevance 74.62%

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 70.52%

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 69.7%

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 68.47%

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 68.47%

I have a column of numbers which I'm summing together everyday. The data in those columns changes everyday. In another column I have various dates and in the column next to that I have the per day totals.

So, in the column with the per date totals I have the formula
=IF(TODAY()=C2,SUM($B$2:$B$50),0)
=IF(TODAY()=C3,SUM($B$2:$B$50),0)
etc.

The problem is that when today becomes tomorrow, I lose the total. Is there a way to keep that total without loosing the today() command in those cells?

Hopefully that's not too confusing. I would post an attachment but my work thinks the popup is a gaming site... Thanks for the help!
 

Answer:Excel - saving today() data

16 more replies
Relevance 66.83%

I have setup excel pie charts whereby I only want the data label option “percentage” to be shown. If I save the workbook with these settings, close and then re-open the settings are retained.

When I then upload this workbook to my SharePoint 2007 site and select open, open snapshot in excel the workbook opens, but all other data label options are set such as series name, category name, value, as well as percentage (percentage option is all I want, and the only one i set).

If I choose the option “open the workbook in excel” the data label options are set correctly. I need to be able to open the workbook as a snapshot, with the correct data label options because the other option to simply open in excel through SharePoint strips out all the formulas which reference particular data.

The odd thing with this is that if I open the snapshot in Excel 2007 the correct data label options are retained (series name, category name, value) are unticked, which is how I set it.

I tried re-building the excel workbook again in excel 2010, but I’m having the same issue.

I tried with a simple pie chart and the same happens.

We are using Excel 2010 X86 SP1 and SharePoint enterprise server 2007

Any help would be appreciated.
 

More replies
Relevance 66.83%

I have setup excel pie charts whereby I only want the data label option “percentage” to be shown. If I save the workbook with these settings, close and then re-open the settings are retained.
When I then upload this workbook to my SharePoint 2007 site and select open, open snapshot in excel the workbook opens, but all other data label options are set such as series name, category name, value, as well as percentage (percentagewhich is all I want, and the only one i set).
If I choose the option “open the workbook in excel” the data label options are set correctly. I need to be able to open the workbook as a snapshot, with the correct data label options because the other option to simply open in excel through SharePoint strips out all the formulas which reference particular data.
The odd thing with this is that if I open the snapshot in Excel 2007 the correct data label options are retained (series name, category name, value) are unticked, which is how I set it.
I tried re-building the excel workbook again in excel 2010, but I’m having the same issue.
I tried with a simple pie chart and the same happens.
We are using Excel 2010 X86 SP1 and SharePoint enterprise server 2007
Any help would be appreciated.

More replies
Relevance 66.83%

I have an excel file with the current scenario:

A12 - this cell uses a CONCATENATE formula (from other cells) to create a directory structure and file name (example- C:\project\vendor\41576_info.txt). The directory structure already exists. The the *.txt file does not exist.

I would like to use a VB macro that will create the new text file in the specific directory and add any content in cells A13:A17 to the text file. Any suggestions on how to do this? Thanks in advance

PS - I did try to look through previous posts but the search function was retuning errors.
 

Answer:Solved: Saving Excel Data to a text file

6 more replies
Relevance 61.5%

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 60.68%

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 60.68%

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... Read more

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 60.68%

Hello. I am looking for copy data based on criteria into a new work sheet and format it at the same time. My data is a database dump with thousands upon thousands of records and for my purposes I want to create a semi-refreshing excel dashboard that will show the data I want to extract. Basically I am dumping the database to an excel tab but can also access the database so getting to the data is not the problem. What I would like to do is get the data based on criteria and add it to an excel worksheet with a row being created based on the successful criteria of the data.Basically my data looks like this:## DATA FILE TAB ##[![Data file sample][1]][1] [1]: http://i.stack.imgur.com/HSL7o.jpgAnd this is what I want the data to end up looking like. One thing to keep in mind is that I can't simply populate a worksheet as I need to be able to create new rows once "good data" has been filtered.## Formatted Worksheet ##[![Formatted Worksheet][2]][2] [2]: http://i.stack.imgur.com/twXGS.jpgThanks for your help in advance.

Answer:How do I add rows of data to new worksheet from data dump

I'm not quite sure what kind of help you are asking for. Your subject line asks about "adding rows to a new worksheet" yet you posted an image [2] of data that is apparently sorted in some way and formatted with different font sizes and fill colors. In addition, the 2nd contains data that doesn't even appear in the original data set.What exactly are you asking for help with? Are you asking us to help create that 2nd, formatted sheet or are you asking for help with adding rows to that formatted sheet? I also don't understand this line:with a row being created based on the successful criteria of the data.What does "successful criteria of the data" mean?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 59.86%

Apologies if this isn't the correct forum but im having problems saving spreadsheets in excel. When saving to a specific folder on my hard drive I receive the error message "Data Error (Cyclic Redundancy)". It doesn't happen every time but most of the time. It only happens in Excel and only happens when saving to one specific folder. Does anyone have any ideas about the problem?

Thanks.
 

Answer:"Data Error (Cyclic Redundancy)" - message received when saving in Excel 2003

The hard drive has a bad sector under that folder?

Test the hard drive with the manufacturer-provided utililty to confirm.

The bad sectors can be marked unusable with the chkdsk utility to make Windows work around the bad areas, but you should consider repacing that drive.
 

2 more replies
Relevance 57.4%

Hi
Is it possible to use the "Find" feature ( In excel, under Edit, on menu bar) to search and locate more than one cell at a time?
If not, is it possible any other way?
Thanks
Ricky

Answer:Finding data on a worksheet

What version of Excel are you using?

5 more replies
Relevance 57.4%

Can I pick someones brain please? - Apologies in advance as this is probably such an easy thing

I have an Excel worksheet using 52 tabs each one being the weekly financial period in a year. On the first tab there is a list of products (and associated sales data) that will be added to throughout the year.

My question is what is the best way of ensuring that all the following tabs (Week 2 onwards) will pick up the correct data from the previous one?

I've tried referencing the cell to the previous weeks one but this looks messy - zerio's where there's no data etc. - unless I'm doing that wrong

Thanks in advance,

Moll
 

Answer:Referring to data on another tab on worksheet?

7 more replies
Relevance 56.99%

I have a folder with .csv and .txt files in it that I get every month from clients. 1. I need to get them into one Excel spreadsheet to do analysis with the information. I know I need to create a macro or a series of macros to be able to do this, but I really don't know how to begin. The information consists of three parts, I ultimately would like the macro to be able to seperate these individual parts and drop it on a seperate sheet within this workbook.... OR 2. I would like to be able to eliminate this first step and create an append query in Access to import the information into a table.

I ultimately need to have the information in Access and would much rather be able to do the entire process from an append query in Access.
thanks again.
 

Answer:.csv to Excel worksheet

6 more replies
Relevance 56.99%
Question: excel worksheet

Hi
may be a dumb question but can i assemble a series of "loose" worksheets into a workbook? someone sends me a monthly report as an excel WS I want to put them all in one book from the folder they download to so I can analyse the data across all the sheets.
Regards Bloodaxe

Answer:excel worksheet

See if this helps click here

6 more replies
Relevance 56.99%
Question: Excel Worksheet

I was told if changes are save on current excel worksheet that you can recover the original worksheet or that it no longer exist on the hard drive is this true?

I need to find old worksheets. I created a worksheet to keep track of proceeds and updated it almost daily for 9 months. Is it possible for all of these worksheet to still exist on the hard drive?

Sorry with my pea brain I don't even know if this is a dumb question.

Thanks in advance,

popupgotme

Answer:Excel Worksheet

To the best of my knowledge, unless you saved each worksheet separately before making changes to it and then saved THAT worksheet, then you have overwritten the same file with new data. There would be no different files to retrieve practically.
Regards,
John

2 more replies
Relevance 56.99%
Question: Excel Worksheet

I have a large Excel Spreadsheet that lists equipment (unit#, make, model, serial-no, branch location (1 to 5), status of equipment codes (0=avail, 1=on rent, 2=sold, 3=used for rent, 4=repair, 5=to be scrapped) and my assignment is to summarize how may pieces of equipment are in each of these categories at each location. Would I use a "countif" or a "vlookup" formula? I am using Excel 97.

Thank you
 

Answer:Excel Worksheet

None of them! I think you should use sumif or even sumproduct!
But also it can be done even easier using pivot tables!

If you wanna us assist you with that post a sample with some dummy date, bu with the real structure of the worksheet!
 

3 more replies
Relevance 56.58%

I have an excel worksheet with 50,000 different book isbn, one isbn per cell. Out of the 50,000 there are about 20,000 or 30,000 duplicates with varies qtys. Is there a way to consolidate the duplicate isbn and qty to a single cell without having to do it manually. In column A I have all the isbn and Column B the qty

Answer:consolidating data in a single worksheet

There's lots of ways depending on what you are looking for as the output.One simple way is to create a Filtered Lst of Unique values someplace else in your workbook and then use SUMIF.Select Column AData...Filter...Advanced FilterCopy to another location (e.g. G1)Unique valuesThis should creat a list containing a single instance of each ISBN.In H2 enter this and drag it down:=SUMIF(A:A, G2, B:B)This will SUM the cells in Column B which correspond to the value in the criteria argument whenever it's found in Column A.

5 more replies
Relevance 56.58%

I want to search one worksheet for employee ID (column named employee ID) and place that number in the blank employee ID column on the 2nd worksheet based on employee name. The first and last names are in separate columns on both worksheets. Both first AND last names must match in order to copy the employee id number. Is this even possible in Excel?

Answer:search one worksheet and copy data to another

There are two ways you can do this, and it's dependendant on how your ID number is configured.If it is ALL numbers then a =SUMPRODUCT() function can get the dataIf is a mix of numbers and Text or all Text then the =VLOOKUP with a helper column should work.If your data looks like this:=SUMPRODUCT() - ID's must be numbers.
Sheet 1
A B C
1) Last First ID
2) Public John 123
3) Jones David 234
4) Smith Joe 345
5) Mouse Mickey 456
6) Mouse Minnie 567
On Sheet 2Enter the Last Name in cell A1Enter the First Name in cell B1In cell C1 enter the formula: =SUMPRODUCT((Sheet1!$A$1:$A$6=A1)*(Sheet1!$B$1:$B$6=B1)*(Sheet1!$C$1:$C$6))With =VLOOKUP() you will need to insert a helper column before column Aso your data looks like this:
A B C D
1) Last First ID
2) PublicJohn Public John jp01
3) JonesDavid Jones David dj02
4) SmithJoe Smith Joe js03
5) MouseMickey Mouse Mickey mm04
6) MouseMinnie Mouse Minnie mm05
In Column A enter the formula =B2&C2 and drag downOn Sheet 2Enter the Last Name in cell A1Enter the First Name in cell B1In cell C1 enter the formula: =VLOOKUP(A1&B1,Sheet1!A2:D6,4,FALSE)See how that works.MIKEhttp://www.skeptic.com/

2 more replies
Relevance 56.58%

I have a "Master" spreadsheet, and on that spreadsheet there are tab names that correspond with about 4 other worksheets. What I am wanting to do is copy the data from the worksheet, to the "Master" workbook's worksheet with the same worksheet name. I have this code:

Code:

Subscript out of range
Set wb = Workbooks.Open(Filename:="R:\Test\Master.xls")

Set wb = Workbooks.Open(Filename:="Z:\Test\Test1.xls")
Sheets("January").Rows("3:200").Copy
ActiveWorkbook("January.xls").Activate
Sheets("January").Select
Range("A3").Select
ActiveSheet.Paste
Range("A3").Select
Set wb = Workbooks.Open(Filename:="Test2.xls")
Sheets("Feb").Select
Rows("3:1000").Select
Selection.Copy
Windows("Feb.xls").Activate
Sheets("Feb").Select
Range("A3").Paste
Range("A3").Select

But I keep getting a subscript out of range debug error. Can someone shed some light onto what is causing that error and what modifications need to be made so that the error doesn't continue?
 

Answer:Solved: Copy Worksheet Data

When and at which line of code do you get the problem?
Have you tried running the macro step-by-step?

Subscript out of range means that you're referring to something that is not there "out of range"

Since I don't have your files and sheets it can be anything.
 

2 more replies
Relevance 56.58%

To monitor a solar system, I have a spreadsheet with a summary sheet which uses data from monthy figures in individual tabs named APRIL 2011,MAY 2011 etc.
Of course any reference to the tab in any formula on summary sheet needs to be changed each month.
In this regard there are 4 cells that need the tab reference changed and I do this manually.

Have attached a copy of spreadsheet and am looking for a way to automatically transfer data to rows 6,19,23 and 29 of Summary sheet from relative cells in row 38 of individual tabs.

You will note that until a number appears in row 3 of Summary sheet no transfer of any data from tabs occurs.

Any suggestions appreciated

Tech Support Guy System Info Utility version 1.0.0.1
OS Version: Microsoft Windows 7 Home Premium , 64 bit
Processor: Intel(R) Core(TM)2 Quad CPU Q9400 @ 2.66GHz, Intel64 Family 6 Model 23 Stepping 10
Processor Count: 4
RAM: 4061 Mb
Graphics Card: Intel(R) G41 Express Chipset, 1806 Mb
Hard Drives: C: Total - 953867 MB, Free - 788328 MB; E: Total - 152617 MB, Free - 58565 MB;
Motherboard: ASUSTeK Computer INC., P5G41T-M LX, Rev X.0x, MT709B020422235
Antivirus: Microsoft Security Essentials, Updated and Enabled
Pedro
 

Answer:Automatic transfer of data from one worksheet to another

16 more replies
Relevance 56.58%

Hi there,This is a little bit more in depth than my last question. It is an amalgamation of two previous questions. The following is a formula I have created=IF(AS3=0,0,INDEX(INDIRECT("'"&AS3&"'!A5:ZZ26),(MATCH(AS7,(INDIRECT("'"&AS3&"'!A5:ZZ5),0)+11),(MATCH(AQ5,(INDIRECT("'"&AS3&"'!A8:ZZ26),0)+12)))))AS3 = the name of a worksheetA5:ZZ26 is the array within that worksheetAS7 = Date A5:ZZ26 is the row in which the date can be foundAQ5 = value to look forexample of the answer Date is found in N5 Value found in L8 Returned value = P8Where have I gone wrong with this formula??????

Answer:Find specific data within another worksheet

Without testing anything, I'll take a shot..The INDIRECT function is looking for a text representative of a range reference. Therefore, all parts of the reference need to be inside the quotes so that INDIRECT sees it as test.It doesn't look like you have closed the quotes after the second & in any of your INDIRECT functions.Maybe something like this...INDIRECT("'"&AS3&"'!A5:ZZ5")Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

5 more replies
Relevance 56.58%

A couple of months ago my Excel worksheets became immobile - unable to be reduced in size or repositioned on my monitor. The minus button works, and the X button works, but the middle button works the same as the minus button. As a result, I see the sheet covering the entire screen or not at all. When I try to Restore from the bottom bar I only get the maximum screen again. The individual windows within the overall workbook can be repositioned within the maximum screen. I am using XP Home and Office 97 on a Toshiba Satellite Laptop, with a 19" CTX auxiliary monitor. Unable to find out what I did to cause this, or to find any help. Any suggestions would be greatly appreciated. IREBob
 

Answer:Excel Worksheet Resizing

13 more replies
Relevance 56.58%

In my excell, whenever I open an workbook, the colour or the cells and chart colours changes to different (randomly) than I originally set. Any information/help what's causing the problem and how to resolve this?Regards!

Answer:Excel worksheet problem

closing without any resolution.

1 more replies
Relevance 56.58%

i applied allinternalpasswords.xls to unprotect a worksheet. it has done it job. i opened the xls. when enabled macro i got a addin on the toolbar of excel. then i opened the problematic excel worksheet on the same window. i clicked on the addin and i got the link to remove password. it has done its job. now i still have the addin on the excel which i want to remove. how to remove it?
winxp-sp3-amd-asus-1 gb ram-
 

More replies
Relevance 56.58%

I have a code that runs when an Excel workbook is opened. The code runs on all the worksheets within the workbook. When the code stops running the worksheet selected is always the last worksheet. There are 12 worksheets (named by each month).

How can I get the code to select the current month worksheet when the code reaches the end?
 

Answer:Solved: Excel -> Worksheet Name

6 more replies
Relevance 56.58%

I am trying to build a shop schedule to keep track of work being performed in 14 different bays. I have started the work sheet but run into difficulties with keeping the customer names below each other in the next cell. I end up with gaps in the work sheet where the formula does not find a value. Can someone assist me with completeing this worksheet? Please have a look.
Thanks
 

Answer:Solved: Need Help with an excel worksheet

16 more replies
Relevance 56.58%

I have 250+sheets, summary sheet in excel. I did a formula an IF THEN statement it works ex; IF(sheet1'!$B$1<=5,sheet1'!$B$2,sheet1'!$B$3) but now have to repeat that formula 249 more time on my summary sheet.

Answer:Excel Worksheet Problem

Hi,If you are trying to do the same as before then the initial 'formula' looks like this:="XXXIF(Sheet" & G3 & "!$B$1<=5,Sheet" & G3 & "!$B$2,Sheet" & G3 &"!$B$3)"The formula you posted has an extra ' in it, sheet1'or a missing ' depends on how you look at it.The worksheet name can be used without single quotes if there are no spaces in the name, so =Sheet1!B1 is OKbut with a space it has to be ='Sheet 1'!B1Regards

2 more replies
Relevance 56.58%

I have a document that is read only suggested...not password protected. I have been adding worksheets as necessary. I only have 6 worksheets at this time. Today when I tried to add a worksheet the worksheet option is inactive.

Any ideas?

Thanks so very much!
 

More replies
Relevance 56.58%

I did something to make a worksheet that I had prepared in Excel to go to my external drive G. Now each time I need to get the worksheet for some reason it tells me that it is not there, but I can see it in the external drive. I want to it on drive C.
Suggestions?

Answer:Excel Worksheet in Drive G

Why not copy & paste or move the worksheet from the G: drive to the C: drive if that's what you want. What's stopping you?

3 more replies
Relevance 56.58%

If a2 is less than $65000, 0, but if a2 is $65000 to $70000, $100, but if a2 is $70001 to $74999, $200, but if a2 is $75000 to $79999, $300, but if a2 is $80000 to $84999, $400, but if a2 is $85000 to $89999, $500, but if a2 is $90000 to $94999, $600, but if a2 is $95000 to $99999, $700, and if over $100000, $800 . I will manually enter the amount in 'a2'. How do I do this calculation in my excel worksheet?

Answer:Excel Worksheet Calculation Help!

Why did you start a whole new thread?You could have just answered my query in the last one.Try this:=IF(A2<65000,0,IF(A2<=70000,100,IF(A2<=74999,200,IF(A2<=79999,300,IF(A2<=84999,400,IF(A2<=89999,500,IF(A2<=94999,600,IF(A2<=99999,700,800))))))))MIKEhttp://www.skeptic.com/

4 more replies
Relevance 56.58%

I have created an Excel spreadsheet but cannot limit its size. In 'Print Preview' it fits nicely onto one A4 page, but when trying to email it spreads to 26 pages! How do I get rid of the thousands of extra rows and columns? I have Office XP.

Answer:Excel worksheet size

Try selecting the area that you want to print then File > Print Area > Set Print Area.

10 more replies
Relevance 56.58%

I have 4 work sheets:worksheet one has pending ordersone ordered confirmed i have a column confirmed i have a row that says YesI want the information in that row to go to the next worksheet#2 which is on route to delivery. Once delievered I have a row that saids YesThat row would go to the next Worksheet that states Delivered row with Yes to next work sheet for billing.What is a formula to activate a whole row to move?

Answer:Excel Moving a row to another worksheet

There is no formula that can "move" a row. A formula/function can only return a result in the cell in which it resides.You'll probably need to use VBA to accomplish your goal.re: "one ordered confirmed i have a column confirmed i have a row that says Yes"I don't understand this. Please try to explain it differently and we'll see if we can help.Posting Tip: Before posting Data or VBA Code, read this How-To.

2 more replies
Relevance 56.58%

Is there a way to remove or dim(so they can't be accessed) the functions on the worksheet menu bar, i know i can go into tools>customise and turn it off but i would like to do this using vba for a particular workbook/worksheets so that when the workbook is closed the menu bar returns, i think you can cover the worksheet menu bar with a custom menu bar but i am not sure how to do this either.

any help please
 

Answer:Excel worksheet menu bar

Yes, you should check the link I sent you (Pearson) you have to look a bit but there are many options like disactivation copy and paste, delete sheets, you name it.
It will take some thinking and doing but it's all there
 

1 more replies
Relevance 56.58%

Hi All

I am trying to create a grading template on Excel. I have included hyperlinks, dropdown boxes and comments with no problem, but what I am trying to do is this.

I have the first 10 rows for static information - they don't change. I want to be able to change to next 10 rows without affecting the rows above. I want to be able to change column width, for example, without affecting what I have written above. Any ideas?

PS: If you know any sites that have intermediate tutors for excel, I'd love to know their addresses - especially any for info on creating grading templates.
 

Answer:Splitting an Excel Worksheet

11 more replies
Relevance 56.58%

I am looking to setup a simple worksheet, but I am having problems with tabing from one cell to the next. Is there anyway that I can assign a tab order to the cells, instead of the tab going where it wants to? This is after I have unlocked the cells that I need to type into.

Answer:Excel Worksheet Setup

Hi,If you are referring to how the selected cell changes after entering data, there is a setting in Options to set Up, Down, Left, Right or No move.I can't remember the exact location of this setting in Excel 2007, as I don't have that version on this PC.It's likely - Office Button - Excel Options (at the bottom of the pane), then select the Customize or Advanced tab from the tabs on the Left and look for Move Selection after Enter (or similar wording).Not sure if that helps you in your specific situation.Regards

2 more replies
Relevance 56.58%

I have created an invoice using Office Excel.

As a test I sent it to own e-mail address.

The problem I have is when I received it, it was sent directly to my deleted folder.

I have both Norton Anti Virus and Internet Security installed on my PC.

Could these two programs have anything to do with it???

Thanks
 

Answer:Sending Excel Worksheet

8 more replies
Relevance 56.58%

tab scrolling buttons will not advance the worksheetI've turned them off and don't know how

Answer:worksheet won't advance in Excel

tab scrolling buttonsNot sure what you mean.What version of Excel are you running?MIKEhttp://www.skeptic.com/

14 more replies
Relevance 56.58%

In one workbook I have a number of worksheets that I wish to summarise.From the summary sheet is possible for excel to supply the worksheet name in a cell ?If so how ? I have tried '=sheetname!' but it does not work.

Answer:MS excel worksheet linking

except that you haven't defined the cell to be linked after the exclamation mark

10 more replies
Relevance 56.58%

i need a formula for my excel workbook

Answer:i trying to complete my excel worksheet

Here, use this one:=IF(IF(INDEX(temp.xls!A:F,SUM(MATCH("EENU",temp.xls!A:A,0),MATCH("BF304",OFFSET(temp.xls!A1,MATCH("EENU",temp.xls!A:A,0)-1,0,MATCH("FCLI",temp.xls!A:A,0)-MATCH("EENU",temp.xls!A:A,0)+1,1),0))-1,5)<>0,5,6)=5,INDEX(temp.xls!A:F,SUM(MATCH("EENU",temp.xls!A:A,0),MATCH("BF304",OFFSET(temp.xls!A1,EQUIV("EENU",temp.xls!A:A,0)-1,0,MATCH("FCLI",temp.xls!A:A,0)-MATCH("EENU",temp.xls!A:A,0)+1,1),0))-1,IF(INDEX(temp.xls!A:F,SUM(MATCH("EENU",temp.xls!A:A,0),MATCH("BF304",OFFSET(temp.xls!A1,MATCH("EENU",temp.xls!A:A,0)-1,0,MATCH("FCLI",temp.xls!A:A,0)-MATCH("EENU",temp.xls!A:A,0)+1,1),0))-1,5<>0,5,6)),-INDEX(temp.xls!A:F,SUM(MATCH("EENU",temp.xls!A:A,0),MATCH("BF304",OFFSET(temp.xls!A1,MATCH("EENU",temp.xls!A:A,0)-1,0,MATCH("FCLI",temp.xls!A:A,0)-MATCH("EENU",temp.xls!A:A,0)+1,1),0))-1,IF(INDEX(temp.xls!A:F,SUM(MATCH("EENU",temp.xls!A:A,0),MATCH("BF304",OFFSET(temp.xls!A1,MATCH("EENU",temp.xls!A:A,0)-1,0,MATCH("FCLI",temp.xls!A:A,0)-MATCH("EENU",temp.xls!A:A,0)+1,1),0))-1,5)<>0,5,6)))Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 56.58%

I made an Excel worksheet and I want to set limits to where a user is able to tab to. Ie...The user starts in A2 and presses tab to get to B2, when the user presses the tab key again I want them to be taken to tab A3. Any Ideals? Thank you in advance for your help.

Answer:Excel worksheet tab order

Do you only want them to have access to the columns A and B only?? If so, try this.. Select the range of cells that you wan them to have access cells (EX: A2:B20). Right click on one of the selected cells and Select FORMAT CELLS. Then find the "Protection Tab" and UNCHECK the box next to" Locked". Close the FORMAT CELLS box. Then we are going to protect the worksheet and/or workbook depending on whichever you wish. This method will vary depending on which version of Excel you have. If it is the 03 version, go to Tools, Protect. If it is 07+, go to the Review Tab. Once you select Protect Sheet, you should see some options or "permissions" with check boxes next to them. Here we want to DESELECT "Select Locked Cells" so that they will only be able to select the cells that we previously "unlocked".BEST OF LUCK!

2 more replies
Relevance 56.58%

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 56.58%

Have a project from work to fill in worksheets for phone calls received each week. Column headers are Phone Line 1,2,3,4,5. Rows are days of the week - with totals at the end of each column/row. Is it possible for me to have a separate worksheet which will add each of the weekly sheets up as a running total. I know I can do it as an autosum for existing sheets, but is there a way for it to auto-update to include each new weekly worksheet I make?

Answer:excel help - totals worksheet

Other than using a macro, this isn't easily possible. However, if you enter the names of the sheets - Sheet1, Sheet2 etc - in Column A of your summary sheet then the formula=INDIRECT("'"&A4&"'!M4")and copied down will retrieve the value of M4 from each sheet. You could adapt this to sum them.

4 more replies
Relevance 56.58%

Windows 7
Office 2007
Computer is approx 1.5 years old as well as the printer also.
Ram is a train load (gigs)

Having trouble with my Excel spreadsheets printing.
In any given Workbook, some worksheets print, some won't.
The ones that won't, I get a dialog box stating a few things, eg, not enough memory, drivers, etc., generally a generic dialog.
I have checked these avenues out, update Windows daily, as well as uninstalled my printer (HP C309a), reinstalled printer, I've had a computer technician out here a couple of times and he's done the same things I have done, but to no avail.
It may print, it may not. Depending on mood swing I guess.
Any suggestions?????????
Please help,
Thank you.
 

More replies
Relevance 56.58%

I have checked the threads and can't quite find an answer.

I have a spreadsheet with multiple worksheets (16), but one, lets call it worksheet 4, is very slow to be selected. The workbook itself opens quickly, and all the other worksheets select quickly enough.

This particular worksheet, when selected, displays whatever worksheet info I was on before, I get the old egg timer, and often get that the sheet is not responding. As long as I sit there long enough, it eventually displays. The formulas & formatting on this sheet are the same as on 8 or 9 of the others, and I am not having this problem with any other worksheets.

It isn't a small sheet - 2.7MB, and it is formula heavy.

Operating on Windows 7, running Excel 2007

Any ideas? I have checked for objects and hidden code and can't find anyhting.
 

Answer:Slow Excel Worksheet

What type of formulas do you have on that particular sheet? Are they referring to other cells on the same sheet, to cells on another sheet, to cells in another workbook...?
Even if it wasn't confidential (which I suspect it is) your workbook would be too large to upload, possibly even if you zipped it.
But unless we can compare the bad sheet with one that behaves normally or have a lot more info, I can't think of a lot to suggest.
 

3 more replies
Relevance 56.58%

Printer shows "Printing 1 of 1862" how do I get rid of this number

Answer:How to print a worksheet in Excel

It should be somewhere in your Printer Setup or Page Setup.MIKEhttp://www.skeptic.com/

2 more replies
Relevance 56.58%

I need to print a worksheet (to make an order form) that contains 5 columns, but split into 2 so that one sheet looks like 2 sheets of A5 (A4 landscape, but wit two pages like a book - does this make any sense to anyone, !!!!!!!!!

Answer:Format Excel Worksheet

VoG's territory!!!

9 more replies
Relevance 56.58%

I know this may be an obvious question, but I have a number of Worksheets in my Excel Workbook.

How do I send just a single Worksheet via e-mail?
 

Answer:Sending Excel Worksheet

It's not obvious at all. Questions:

1. Always emailing to the same person?
2. Email as an attachment? Or as part of the message body in Outlook?
3. Want a button on the worksheet?

You can check here:
http://www.rondebruin.nl/sendmail.htm
 

3 more replies
Relevance 56.58%

Can not remove Worksheet menu bar from Excel 2002
 

Answer:Excel worksheet menu bar

14 more replies
Relevance 56.58%

Hi ,

I had an excel sheet having 26 worksheet of 6mb size and that too file is sharing, now the actual problem is whenever i save a file or save as, without doing anything in the file one of the sheet randomly get disappeared, sometime the file size get decrease without doing anything.

Does any one have face any such problem, because what i think that there is no limit on file size and number of worksheet.

Best,
Ashish Sharma

More replies
Relevance 56.58%

Is there a way of only displaying the columns which I am working with, on my Worksheet.

I am only using the columns A-K, but if I use the horizontal scroll button at the bottom of the screen I keep scrolling to unused columns to the right.
 

Answer:Viewing worksheet on Excel

8 more replies
Relevance 56.58%

Could someone please tell me the shortcut to enlarge the worksheet in Excel.

Thanks
 

Answer:Enlarging Excel Worksheet

Timeferret, depends what you mean by "enlarge".

From the Help file:

"If you use the Microsoft IntelliMouse pointing device to zoom more often than you use it to scroll on a sheet, you can set the wheel button to zoom instead of scroll. On the Tools menu, click Options, click the General tab, and then select the Zoom on roll with IntelliMouse check box."

This works with my non-M$ mouses too.

Rgds,
Andy
 

1 more replies
Relevance 56.58%

I have a question on adding a hyperlink from my intranet website to an excel worksheet:
The actual complete workbook is located on a LAN folder on our computer system... I want them to be directed to a specific worksheet w/in a workbook in excel. Is there a way to do this? Thanks so much!
 

Answer:hyperlink to an excel worksheet

I think all you need to do is something like this:

"C:\\My Documents\\MyExcelFile.xls#MySheet!A1"

If your sheet name contains a space, then you need to enclose it in single quote marks, like this:

"C:\\My Documents\\MyExcelFile.xls#'My Sheet'!A1"

Best,
Dan
 

1 more replies
Relevance 56.58%

I have a file in Excel. I recently added information to the file but when I tried to save it, a message said I cannot save it because it is a read only file. I check the Help information and it showed how to change the properties but when I tried that and went to remove the check mark from the 'read only' box there was no check mark in the 'read only box'. No matter what I tried I could not get the file to save the changes I made. Then it said to save with a different name but when I tried that it said I couldn't because the file was 'read only. I have run into a brick wall. Does anyone have an answer for this?

Tech Support Guy System Info Utility version 1.0.0.2
OS Version: Microsoft Windows 10 Home, 64 bit
Processor: AMD Athlon(tm) II X4 630 Processor, AMD64 Family 16 Model 5 Stepping 2
Processor Count: 4
RAM: 4094 Mb
Graphics Card: AMD Radeon HD 5700 Series, 1024 Mb
Hard Drives: C: Total - 476387 MB, Free - 409840 MB;
Motherboard: Gigabyte Technology Co., Ltd., GA-MA74GM-S2
Antivirus: Windows Defender, Disabled
 

Answer:Cannot save Excel worksheet

try saving with a different name
and closing the spreadsheet
Now using file explorer , goto the original file and rename that to somethink like OLD
now you should be able to rename the new file, back to its original name

have you emailed or shared the file at all
Anyone else on the PC - different user - open it
Does it contain any Macros at all?

I find this problem , when using dropbox or onedrive to save a copy into
often I have to save a copy , as dropbox/onedrive will be syncing the old file and locked it out
 

3 more replies
Relevance 56.58%

Hi, I am new to Excel and I just need help with linking to another worksheet. So if the user clicks on a cell, they will be taken to another worksheet with more detailed information. I realize this is very basic to most people but, as I said, I am new to Excel and I could not find information on this in the help files. Thanks for your help.
 

Answer:Excel - need help linking to another worksheet

ac11 said:

Hi, I am new to Excel and I just need help with linking to another worksheet. So if the user clicks on a cell, they will be taken to another worksheet with more detailed information. I realize this is very basic to most people but, as I said, I am new to Excel and I could not find information on this in the help files. Thanks for your help.Click to expand...

See the Help file for Hyperlinking
 

1 more replies
Relevance 56.58%

Hello guys, this is my first post here.I have a work laptop that is on a domain. When I try to open any colored document in Excel 2002, I am not able to see any color in cells at all. Others can see them but not on my laptop. I tried the Detect and Repair option but it did not work. Do you guys have any suggestions? Thanks in advance and glad to be here.

Answer:Excel 2002 worksheet

Welcome to ComputerHope.What happens if you save a file in Excel 2000 with colored cells and open it?

8 more replies
Relevance 56.58%
Relevance 56.58%

I have been given the job of merging around 2,000 workbooks, each having around 12 worksheets. So I expect to have 24,000 worksheets in total. I am looking for a utility that will merge these onto one continuous worksheet, or if this is too big, I will initially group them into smaller worksheets. My problem is, I need to audit which workbook each row of data is taken from, so the utility must insert a column with the workbook name inserted against each row.

This is a nightmare, but I have to do it. Can anyone suggest any utility, at any price, that might carry out this function ?

Thanks in advance
 

Answer:Excel worksheet merge

A friend of mine goes here for help,try it
http://ozgrid.com/forum/index.php
 

1 more replies
Relevance 56.58%

How can I restrict the use of an Excel worksheet to one computer? I believe that macros can be used to ID the serial number of a PC's hard drive and that an algorithm can be used to generate an activation code which would prevent migration.If it is possible (by any method), I would appreciate advice on how to achieve this.

Answer:Excel Worksheet Security

That is actually incredibly difficult to do.Would validation using the user's Windows logon name be an option?If you are trying to create a secure environment in Excel then forget it - Excel security is pants. Anubvody can crack it.

10 more replies
Relevance 56.17%

Hi,

I want to know how i can export the data from Access to excel using Access VBA for the specified sheet using data linkage with access database. Like we used to do it manually in excel as external data from access.

So that the excel size won't be that big and also it saves processing time.
 

Answer:Solved: Access data export into Excel as the data linked to excel.

16 more replies
Relevance 56.17%

I have two spreadsheets:

One has rows with the invoice numbers and date and sales number etc for only one state

the other has rows with invoice numbers and dates and sales data etc for ALL state.

I would to match the info with first spreadsheet with the data of the second spreadsheet
using the invoice numbers, that are the same in both, as a key match .

How would I go about this?

A simple Example to illustrate:

Sheet 1 ------------------ Sheet 2
A --------- B ---------------- A ------ B
X --------- 500 ---------------- Y ------- 540
Y -------- 1000 ---------------- Z ------- 100
Z -------- 200 ----------------- X ------- 800

And I need the result as this :
A -------- B --------.... C
X -------- 500 -------- 800
Y -------- 1000 -------- 540
 

Answer:Matching data between two worksheet using common column

11 more replies
Relevance 56.17%

Hi all

I am creating a table at work, which lists the sales managers for different products and then further sub-sorted for different locations.

The old table had every name manually filled into it. Now, I have to update the table as new people have come in and existing ones have left etc. I was wondering whether instead of manually updating each and every name, if I could create a new table in a new worksheet which holds the names and e-mails of the personnel and the main table can refer the names from this table? So after doing this, in the future if any changes are to be made again, I only have to update the table in sheet 2 instead of updating the entire table in sheet 1.

Here is the outline of the tables in Question:

Here is Sheet 1 which contains the name & e-mails of personnel based upon location and designation. I want to lookup the data for name and e-mail from the table in Sheet 2



Here is Sheet 2 which contains the name and e-mails of the personnel.



So can this be done? Any help would be greatly appreciated!

Answer:Using HLOOKUP & VLOOKUP to pick data from another Worksheet

It's quite simple, indeed.

Formula =SourceSheetname!SourceCell on a cell on your target sheet gets the value from source sheet. For instance in your example case Sheet 1 Cell D2 needs to have a formula =Sheet2!$C$3 (do not forget the exclamation mark after sheet name & dollar signs before row and column!). Now whenever the Manager North is changed (Sheet2 C3), the value is automatically updated in Sheet 1 Cell D2.

This works for strings and formulas / sums as a source. If the source is a formula or a sum instead of a string, you can also right click source cell on sheet 2 and select Copy, then right click target cell on sheet 1 and select Paste Special > Paste Link.

Kari

3 more replies
Relevance 56.17%

Hi,

I have a master list of data from which i want to make seperate lists. The first seperate list I want to make is a copy of all rows in the master table which have a certain column value set to yes, an example is below.

Name Age Verified
James 18 Yes
Billly 17 No

For all rows where the "Verified" value is yes, I want a copy to made in Workbook 2 and placed in the next available row. Then, whenever I change the data in Workbook 1 for that row I want the information to be updated in Workbook 2 so long as the verified value remains Yes.

How could I do this?
 

Answer:Copying data to a second worksheet depending on cell value.

Hi maracles,
Your using the word "Workbook" when trying to explain what you want to do with the data. Are you intending to copy the data to a seperate Workbook, which means that your copying it to a new Excel document, or are you copying it from Sheet1 to Sheet 2 within the same Workbook. If you could post a ziped copy of the file it would be most helpful.

Peace Out,
 

2 more replies
Relevance 56.17%

Hi,

I have two worksheets Sheet1 and Sheet2 having 3 columns (A,B,C) each within one excel sheet . Coulmn A consist of IDs and other two columns show the informaton of Stages and Processes for respective IDs. like mentioned below.

Column A Column B Column C
ID Stages Processes

- Sheet1 has 1000 records having IDs 1 to 1000, out of which, value is populated in Columns B and C for only 500 IDs.
- Sheet2 consist of any 600 IDs within 1-1000 (all these IDs are available in Sheet1) having updated values populated in column B and C for all these IDs.

I am looking for a macro which will first check the availability of ID from sheet2 in sheet1 and if the ID is available in sheet1, then it will update the values of Column B & Column C in sheet1 with the values of column B & Column C available in Sheet2 for that respective ID.

Please help me in creating a macro for the same.

Regards,
Akshay
 

Answer:Macro to compare and move data from one worksheet to another

Hi,

Can you provide a copy of your workbook. You can remove sensitive info.
 

2 more replies
Relevance 56.17%

Maybe I'm just over thinking this, but I couldn't find anything about it anywhere. What I'm trying to do is copy a range of cells from a table on one WS, to 1 of 3 tables on another WS based on criteria.I could just re-work things if there is an easier way to do it.Source WS: 'February Closings'!End WS: 'Nylx Pricing'!'February Closings'!Table header: B6:L6Data: B7:L35Criteria is column G, which is either Dialer, Co-Marketing, or Referral*I would like to copy Columns B:F Based on column GB6 C6 D6 E6 F6 G6 H6 I6 J6 K6 L6
X X X X X Dialer X X X X X
X X X X X Co-Marketing X X X X X
X X X X X Referral X X X X X
X X X X X Dialer X X X X X
X X X X X Dialer X X X X X
X X X X X Co-Marketing X X X X X
'Nylx Pricing'!-Dialer:Table header: B10:M10*Will always start here'February Closings'! copied data would be B:F-Co-Marketing:Table Header: B35:M35*Could change depending on how many Call Center'February Closings'! copied data would be B:F-Referral:Table Header: B46:M46*Could change depending on how many on above 2 tables'February Closings'! copied data would be B:FX:Data copied from 'February Closings'!Y:Other DataB10:M10 - HeadersDialer
B10 C10 D10 E10 F10 G10 H10 I10 J10 K10 L10 M10
X X X X X Y Y Y Y Y Y Y
X X X X X Y Y Y Y Y Y Y
X X X X X Y Y Y Y Y Y Y
X X X X X Y Y Y Y Y Y Y

Co-Marketing
B35 C35 D35 E35 F35 G35 H35 I35 J35 K35 L35 M35
X X X X X Y Y Y Y Y Y Y
X X X X X Y Y Y Y Y Y Y
X X X X X Y Y Y Y Y Y Y
X X X X X Y Y Y Y Y Y Y

Referral ... Read more

Answer:Copying data to another worksheet based on criteria. - VBA

I figured it out

2 more replies
Relevance 56.17%

Multiple users enter Purchase Order info for all suppliers in to one spreadsheet. I want to automate it so that this information is duplicated into separate supplier worksheets enabling me to identify easily total spend YTD for each supplier

Answer:How do I automate copying selected data from one worksheet i

Without knowing the exact layout of your worksheets, it's hard to give a specific answer.2 options come to mind:1 - In each supplier worksheet use either VLOOKUP or INDEX-MATCH to pull supplier specifc data into each sheet.2 - Use a macro to transfer the data once it has been entered. If you choose the macro route, then macros will need to be enabled on each user's system, something that has to be done at the user's workstation by the user or by someone authorized to perform that action. You can not "force" a system to enable macros without user interaction. That would be a serious security risk.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 56.17%

Hi Everyone.

I have a Workbook with two work sheets. One sheet is called Course Data and the other is a sheet called Instructor Stats.

The Instructor Stats sheet has a cell B5 to enter an Instructors initials and a command button that when clicked will search the Course Data sheet for that Instructor.

I need help with the vba code that when the command button is clicked it searches the Course Data sheet for the Instructors initials in Column I and copies all occurances of that Instructors entries from B28 to N28 on the Course Data sheet and downwards too the end of the sheet, and copies them to the Instructor Stats sheet and places them in cells B14 to N14 and downwards on this sheet.

Can anyone help at all with this ? It would be really appreciated. I am only just starting to learn a bit about Excel and havant even looked at VBA yet
Thanks

Ortz

I have uploaded the workbook here. It is too large to attach here.

http://wikisend.com/download/789942/Course Data.xls
 

Answer:Solved: VBA to search for data and copy to new worksheet.

16 more replies
Relevance 56.17%

I'm trying to use a combobox that would allow the user to select a worksheet in the dropdown list that would refer to one of the sheets. Then, when all the info is entered, the data would be sent to the proper sheet, selected in the combobox. Below is what my code looks like, except I can't figure out the combobox part.

Thanks,
Dan

Note that I name the combobox "Sheet"
Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets me.sheet.value

'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlNext, LookIn:=xlValues).Row + 1

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.vendor.Value
ws.Cells(iRow, 2).Value = Me.Description.Value
ws.Cells(iRow, 3).Value = Me.CodeP31.Value
ws.Cells(iRow, 4).Value = Me.CodeP32.Value
ws.Cells(iRow, 5).Value = Me.CodeP33.Value
ws.Cells(iRow, 6).Value = Me.P31.Value
ws.Cells(iRow, 7).Value = Me.P32.Value
ws.Cells(iRow, 8).Value = Me.P33.Value
ws.Cells(iRow, 9).Value = Me.amount.Value

'clear the data
Me.vendor.Value = ""
Me.Description.Value = ""
Me.amount.Value = ""
Me.vendor.SetFocus

End Sub
 

Answer:Userform + use combobox to specify in which worksheet the data is entered

Hi. welcome to the forum.

You forgot (in some cases important fact) to say which version of Excel are you using?
Can you post a sample with the macro and userform to test, it's easier then 'inventing a sheet'.

You explanation and partial code sheds no light on what you really need, or maybe I'm reading it incorrectly.
 

1 more replies
Relevance 55.76%

I am trying to print the gridlines in an Excel file. I did a search and followed the instructions, went to File/page setup/ then in the Print section I put a check mark in the gridlines box, clicked OK, then clicked Print but did not print the gridlines. I followed the instructions to the letter. What can I be doing wrong?

Tech Support Guy System Info Utility version 1.0.0.2
OS Version: Microsoft Windows 7 Home Premium, Service Pack 1, 32 bit
Processor: Intel(R) Celeron(R) CPU B800 @ 1.50GHz, x64 Family 6 Model 42 Stepping 7
Processor Count: 2
RAM: 1893 Mb
Graphics Card: Intel(R) HD Graphics Family, 818 Mb
Hard Drives: C: Total - 238372 MB, Free - 196442 MB;
Motherboard: Acer, HMA51_HR
Antivirus: avast! Antivirus, Updated and Enabled
 

More replies
Relevance 55.76%

I have always used microsoft works and to make a list of names, addresses, telephone numbers etc. it was very easy in works data base, but I now have windows 10 and I cannot open .wdb files.
I don't know how to name columns or insert a row in excel. Do not understand their explanations in help
 

Answer:Naming columns in excel worksheet

http://www.dummies.com/how-to/computers-software/ms-office/excel.html

 

3 more replies