Macro code for copying a range of cells from one Excell w/sheet t
Want to know macro code for copying a range of cells from one Excell 2007 worksheet to another worksheet in the same Excell workbook by simply typing the number allotted in the first column of that particular range of cells of first woksheet into first column of the second worksheet.
Re: Macro code for copying a range of cells from one...
You did not respond to my claification request. Here is some code that might help, it copies a selection of cells going down until an emty cell is found and across until an emty cell is found. This is form an earlier version of office but should comeclose to woring in 2007.
Sub copyrange() ' ' copyrange Macro ' Macro written by Royal 11/22/2008. '
'go to first worksheet and find entered value (note this is a value serach) Worksheets(1).Activate findfor = "A1" On Error Resume Next
findfor = Cells.Find(What:=searchfor, After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _ False).Address If findfor = "A1" Then erwks = ActiveSheet.Name Sheets(wksname).Activate er = MsgBox("Search item not found on Worksheet" + erwks, , "Search Error") Exit Sub Else findfor.Activate End If
'save this address and start searching for copy area boundaries begcell = ActiveCell.Address begcl = ActiveCell.Cells.Column begri = ActiveCell.Cells.Row 'search amaximum of 1000 rows and 676 columns endri = begri + 1000 endcl = 26 * 26 maxrow = 0 maxcol = 0
For col = Cells.Column To endcl If Cells(begri, col) = "" Then maxcol = col col = endcl ri = endri Else ri = begri End If For ri = ri To (Cells.Row + 1000) If Cells(ri, col) = "" Then If ri > maxrow Then maxrow = ri End If ri = endri End If Next ri Next col
maxrow = maxrow - 1 maxcol = maxcol - 1
'copy the selected area endcell = Cells(maxrow, maxcol).Address crnge = begcell & ":" & endcell Range(crnge).Select Selection.Copy 'go back and paste it in Sheets(wksname).Activate Range(returncell).Select ' use this if yo want to paste formulas etc. 'ActiveSheet.Paste ' use this code if you want to paste values instead of formulas etc. Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range(returncell).Select
- If you need clarification, ask it in the comment box above.
- Better answers use proper spelling and grammar.
- Provide details, support with references or personal experience.
Tell us some more! Your answer needs to include more details to help people.You can't post answers that contain an email address.Please enter a valid email address.The email address entered is already associated to an account.Login to postPlease use English characters only.
Tip: The max point reward for answering a question is 15.
When the cell is in a different sheet in the same file use:
=sheet-name!cell-name as in =Sheet1!A1
If the cell you want is in a different sheet in a different file use:
as in: ='[sample excel 2.xls]Sheet1'!$A$1
The easiest way to do it is in the blank cell, type an equals sign "=" then go to the cell you want to copy, then press enter. All the correct formatting will be handled for you. (Sometimes you need quotes when you have spaces in the filenames and or sheet names.)
Sub aa() ' ' aa Macro ' ' Dim a As String a = "how are you this is dharani" Dim leng, cnt As Integer
leng = 1 cnt = 0
While leng <= Len(RTrim(a)) If Mid(a, leng, 1) = " " Then cnt = cnt + 1 End If leng = leng + 1 Wend If Len(RTrim(a)) > 0 Then cnt = cnt + 1 End If MsgBox Str(cnt) + " Words" End Sub