Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

function/formula to copy/past multiple data rows in Excel worksheet

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


Post your answer or comment

comments powered by Disqus
Hi all,

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

Issue with being able to select 64 bit Data Sources in Excel 2007

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

I have a spreadsheet set up with formulas referring to certain cells. I would like to copy-paste NEW data into these cells, but every time I do so the formulas referring to the cells get an error message and lose the reference. I am trying to avoid re-entering the formulas each time I paste in new data. Suggestions? Thank you. (Excel 2003, Windows XP)

I use excel 2007 and have a spreadsheet which has 350,000 rows of
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.

I use excel 2007 and have a spreadsheet which has 350,000 rows of 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.

I created a form, from an excel list with different formats, text,
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

Looking for a Macro or Formula to copy Column range if value in cell D87 is found in Row D84:R84. This value will change from day to day. So if value today is 6.75 is in D87 it will look at the Row D84:R84 and find 6.75 and copy the data in cells that correspond to the column the number is found in, say it was found in F. So it would copy F3:F77 then paste it in C264:C339.

I created a form, from an excel list with different formats, text, 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

The attached should explain what I'm trying to accomplish.

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 Sub
Thanks!

Hello;
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.

Hi,

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

Thanks
Niklas

(EXCEL 2003)

I would like to be able to select several non-sequential rows in a worksheet called "Data" (using a check box or just entering a value in Column A) and then be able to press a Command button to copy the selected rows to another worksheet called "Estimate" at the bottom of a table, and delete the designators in Column A (i.e. deletes the value, or unchecks the boxes) so I can repeat the process again if needed.

Thanks for your assistance,

Nathan.

We are trying to create a formula in a cell located on one worksheet (TAB) in
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'm trying to build a user form for an Excel worksheet. I’m having trouble getting the user form to find the “last row” of data.

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.”

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
So far so Good. The next step is to add the following code to find the last row:

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 Function
At 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:

Private Sub UserForm_Initialize()

	LastRow = FindLastRow
	GetData

	End Sub
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.

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?

NBVC I ran into a problem getting the formulas to work pas the first row in the worksheet. I tried to do as you suggested and changed the values for the cells but it doesn't seem to help. Do I change each value to the new row? What don't I change? The next row would be B15:B23.

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.

Hey,

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

How do I set up a formula to copy both value and format from one worksheet to
another?
When I use the = sign, it copy the value only. How do I get the format be
copied also?

Hi All,
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..

Hi, does anybody know how to format multiple data series in Excel 2007 at once?

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

I currently have a program that opens up mutiple workbooks anyways I am never
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.

Hi,

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.

Hello,

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

I am using 1200+ rows in a worksheet, but there are more than 65,000 rows on
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,

I'm stuck with ms excel as an interface with ms access as my back-end. This
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.