Free Microsoft Excel 2013 Quick Reference

I am trying to find a way to copy and paste multiple non concurrent rows of data from one spreadsheet to another.

Ex: I have a large worksheet with approx 20,000 rows of data. I need to copy

and paste every 100th row to a new worksheet.

I think this might be possible by setting up a formula and linking worksheets,

but I'm not exactly sure how to do it.

Can anyone help?

Thanks!

dj

Ex: I have a large worksheet with approx 20,000 rows of data. I need to copy

and paste every 100th row to a new worksheet.

I think this might be possible by setting up a formula and linking worksheets,

but I'm not exactly sure how to do it.

Can anyone help?

Thanks!

dj

- Formula to copy parts of an address in cells and paste in specific columns. - See att
- Issue with being able to select 64 bit Data Sources in Excel 2007
- Copy-paste new data in a spreadsheet leaving existing formulas referring to that data
- Excel formula to copy/paste formula needed please.
- Excel formula to copy/paste formula needed please.
- To find change and paste existing values/rows in excel with help of form
- Looking for a Macro or Formula to copy Column range
- To find change and paste existing values/rows in excel with help of form
- Loop to Copy/Paste Defined Data
- Macro to copy & paste cell data into another cell
- Inster Function (fx) to copy paste values for an area
- VBA to Copy Selected Rows in one Worksheet to the end of a table in another worksheet
- Formulas to copy contents of one cell in a worksheet to another ce
- Can't write code to find last data row in excel user form
- Copying Formulas to new row
- VBA code to hide rows when corresponding rows in another worksheet hidden
- How do I set up formula to copy format from one worksheet to anoth
- VBscript to copy paste chart in exact location in other sheet
- Format multiple Data Series in Chart in Excel 2007
- Counting # rows in a worksheet
- Editing Data Connections in Excel 2003
- Last non blank row in a worksheet
- Eliminating rows in Excel worksheet
- How can I display memo data type in excel form?

I'm looking for a formula to copy parts of an address in cells and paste in specific columns.

I attach a copy of a before and after (how i'd like it to look) sheet 1 - sheet 2.

Hope someone clever out there can help me with this.

Many Thanks

Marc

Hello,

I am having a couple of issues with Excel 2007 and being able to retrieve

data in Excel 2007.

The first issue is that I cannot see a System DSN that was added through the

64 bit ODBC Administrator screen on Windows 7 in Excel 2007.

What I do is add the System DSN and when I open Excel 2007 and then click on

the Data tab and then click on From Other Sources then click on From

Microsoft Query the Data Source I added is not in the list.

So since the System DSN did not show up I removed it and added a User DSN

and did the same steps as above and when I open the table I do see that Data

Source.

This gets me to the next issue. When I click on that User DSN name and then

OK I get the following Microsoft Query error screen:

The specified DSN contains an architecture mismatch between the Driver and

application

I then when I click OK I get this next Microsoft Query error screen:

Couldn't read this file

Since Office 2007 loads both 32 bit and 64 bit files shouldn't be able to

open the 64 bit Data Sources?

Just to let you know I am going against a 64 bit Oracle database.

Thanks

data.

The data consists of many ranges.

Each range has a cell containing a formula. These cells are in the

same column but refer to different cells on another sheet.

I want to put the formula on the row above each range.

If I copy/paste I just get the result of the formula. Is it possible

to to have a formula which copy/pastes another formula???

Big thanks.

Colwyn.

The data consists of many ranges.

Each range has a cell containing a formula. These cells are in the same column but refer to different cells on another sheet.

I want to put the formula on the row above each range.

If I copy/paste I just get the result of the formula. Is it possible to to have a formula which copy/pastes another formula???

Big thanks.

Colwyn.

dates, currency.

by filling in the form, I can add lines in the excel sheet.

The next step I would like to do with the form, is to find lines from

the same excel sheet, so that data of that row is filled in the right

boxes again, make it possible to change the boxes or add info in empty

boxes and paste it back at the correct line.

1.I am trying to work with VLookup function

txtcustomer.Value = WorksheetFunction.vlookup(txtmovexno.Value,

Range("A:N"), 2, 0)

and for some textboxes it works and I see the correct info back, but

with the same formula I receive error message 1004 for other txtboxes.

I think the formula would be correct. Could it be the problem that the

format is not correct?

2.Is there perhaps an easier way to find, adjust and replace the

existing info in the rows in excel with a form?

I hope someone can advise me with this. (already breaking my head for

days already, can't sleep )

Thanks,

regards,

Claudia

--

Claudia

------------------------------------------------------------------------

Claudia's Profile: http://www.excelforum.com/member.php...o&userid=37332

View this thread: http://www.excelforum.com/showthread...hreadid=570323

by filling in the form, I can add lines in the excel sheet.

The next step I would like to do with the form, is to find lines from the same excel sheet, so that data of that row is filled in the right boxes again, make it possible to change the boxes or add info in empty boxes and paste it back at the correct line.

1.I am trying to work with VLookup function

txtcustomer.Value = WorksheetFunction.vlookup(txtmovexno.Value, Range("A:N"), 2, 0)

and for some textboxes it works and I see the correct info back, but with the same formula I receive error message 1004 for other txtboxes. I think the formula would be correct. Could it be the problem that the format is not correct?

2.Is there perhaps an easier way to find, adjust and replace the existing info in the rows in excel with a form?

I hope someone can advise me with this. (already breaking my head for days already, can't sleep )

Thanks,

regards,

Claudia

Essentially, I would like to loop through a column (where I have tested my data and returned a 1 if true, 0 if false - Column K in attached). For each instance of 1, I would like to copy the corresponding Name (Column D in attached) and paste to a defined area. Loop continues whereby each instance of 0 is ignored and each instance of 1 results in the corresponding name being copied and pasted offset to the most recent existing by 0 columns and 3 rows.

Souce code included in Module 1 is my effort, need help wrapping it up.

Sub AboveAnalysis() Application.ScreenUpdating = False Sheets("Sheet1").Select Dim Name As Range Dim counter As Integer Dim c As Integer counter = 0 Range("StartAnalysis").Select For c = 0 To 7 counter = ActiveCell.Offset(c, 0).Value If counter = 1 Then ' THIS IS WHERE I NEED ASSISTANCE TO COPY/PASTE THE CORRESPONDING NAME WHERE CODE = 1, SKIPPING TWO ' LINES FOR EACH CODE = 1. End If Next c End SubThanks!

I want to copy & paste the cell data when any cell in the worksheet

gets selected. I would like to have the data stored in a list either on the

same worksheet or in another worksheet. I have the selected cells background

color change when selected but having trouble putting the cells contents into

another cell.

Can I use the Insert Function (fx) to Copy an area and then Paste special values?

Thanks

Niklas

(EXCEL 2003)

Thanks for your assistance,

Nathan.

a file which will pull the information from a cell in another worksheet (TAB)

in the same file but can't figure out the correct formula that we should be

using.

Ideally what we want to do is to have the second cell display the same

contents as the first cell regardless if it's a number or text but if the

first cell is empty then we want the second cell to be empty as well instead

of displaying a "0".

Also, we are trying to sort the contents in a worksheet but the sorting

function will not work. It just doesn't sort when we go through Data-> sort

and make our selections.

Thank you in advance for your help.

Jack

I've been following a tutorial I found online. It seems pretty comprehensive and I’ve been following all the steps. For the purpose of the exercise, I’ve been told to

"add the constant LastRow to the start of the user form module as follows:

while testing this routine” The lesson says that “Later, you'll see how to determine the real last row of data in the worksheet, and you'll convert this constant to a module level variable.”So far so Good. The next step is to add the following code to find the last row:

I followed all the other steps for setting up the code for the form command buttons “First, Next, and Previous”,and everything was working fine (all the data from the worksheet was displayed in the proper text boxes on the user form as I pressed the above command buttons)

The problem came when I followed the directions for adding code to the “Last” command button. The lesson says:

“To make the last row dynamic, a few changes need to be made to the program. First the LastRow constant needs to be switched to a variable like this:”

Private Sub UserForm_Initialize() GetData End Sub

Private Function FindLastRow() Dim r As Long r = 2 Do While r < 65536 And Len(Cells(r, 1).Text) > 0 r = r + 1 Loop FindLastRow = r End FunctionAt this point I am still able to navigate through the data rows via the user form, but the “Last” command button still does not function (won’t take me to the last row)

Here’s where the trouble is. The lesson then says “Then you can set the LastRow variable by adding the following line to the UserForm_Initialize event.

I take this to mean the following:When I try this I get an error message “Compile error, Assignment to constant not permitted” I assume there is a conflict here with the temporary “Const LastRow = 20” that I was told to enter at the beginning.Private Sub UserForm_Initialize() LastRow = FindLastRow GetData End Sub

When I delete the “Const LastRow = 20” and run the user form I get an error message “Invalid row number” and no data is displayed in the form, and I am not able to navigate through the data.

I need to resolve this in order to make data entry possible with this form.

Am I missing something obvious, or is there a problem with the directions in the lesson?

the formula in question is below and used in Worksheet Votes. Workbook attached

=INDEX(B$2:B$10,SMALL(IF(C$2:C$10=C11,ROW(B$2:B$10)-ROW(B$2)+1),COUNTIF(C$11:C11,C11)))

Thank you again for your help. Still trying to learn.

Thanks in advance for any help. I have a workbook in which I have multiple worksheets which are dependant on one input page. I have vba code to hide and unhide blank rows in one worksheet. What I would like to do is be able to hide corresponding rows in another worksheet. I have run into 2 problems. First, I can write code to check for blank rows, but I want it to update on every enter keystroke and this cycles through 2000 lines and takes too long. I would like to have it execute only when a cell changes. Next, the rows contain the same data, but are arranged in a different order so that row 4 on one worksheet is not the same as row 4 on the other worksheet. Any suggestions?

thanks,

Mike

another?

When I use the = sign, it copy the value only. How do I get the format be

copied also?

I am writing vbscript to copy paste chart(or) shapes from one sheet to other sheet.

I want to copy paste the chart/shape in exact location as in the parent sheet.

Please help me.. stuck in this..

I have a 2D-Line-Chart and with 200 series. I need to adjust the line color, so they are all red, and i need to reduce the line width to 1 pt!

Does somebody know how to do this?

Thanks

sure of how many rows of data are in the worksheet that I need to use for a

vlookup in which I need to access the whole range. One day the workbook

could have data stored from A2:G55 and the next A2:G100. I was just

wondering if there is a way to count the number of rows in a worksheet that

hold data so I could pick the right range to lookup. Something like a

rowcount() maybe?

Any help is appreciated.

Thanks a bunch.

I have a workbook with multiple worksheets and over 25 Data Connections using MS Query to connect to different tables in an SQL Server database.

The problem is that the SQL Server database is now being moved to a different server. The database name and the schema of the tables will be unchanged. Only the server name needs to be changed from XXXXX to YYYYY.

I have been unable to find a way to successfully edit the data connections in Excel 2003. A web search produced one potential option. However, that did not work for me very well. Thought the code is designed to cycle through all tablequeries and pivotqueries, the change is only made to one of the data connections. Here is the option I tried:

http://support.microsoft.com/kb/816562

Note that it is very easy to edit data connections in Excel 2007. So I also tried opening the .xls file in Excel 2007. I used the Data Connections button under the Data tab in the ribbon and was successful in editing the connections to point to the new server. However, the graphs used to display the queried data behave weird. The "format axis" settings and chart sizes change. In general, default display settings for charts seems to differ between Excel 2003 and 2007. I used Excel 2007 to only edit the data connections and saved the workbook in the original .xls format.

Does anyone have any ideas on editing server settings within data connections in Excel 2003. I am frustrated with the compatibility issues between 2003 and 2007. Excel 2007 makes editing data connections a snap, but messes up the display of the charts.

Any input is much appreciated.

Whenevr I try to get the number of rows in a worksheet, it returns 65526

which is actually the maximum number of possible in a sheet. If I have used

only 7 rows in a sheet, I want to get the answer as 7. How can I get?

Thank you

the worksheet and it makes it cumbersome scrolling up and down to make

changes. How do I get rid of the rest of the rows? I've tried highlighting

and deleting, but that doesn't do anything. Thank you,

started off as a simple project and as simple goes, evolved into complex. My

problem is how to display a memo data type in excel OLE objects.

thanks in advance.

No luck finding an answer? You could always try Google.