Question about Compilers & Programming Tools

1 Answer

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.

Posted by on

  • Royal May 11, 2010

    If I understand yoour request you want to type an entry onto a cell in one worksheet, then activate a macro and this will copy a group of cells from another worksheet in this workbook to this worksheet. The typed entry will have the value of the top left cell being copied from. Does this sound correct?



    If it does then my question is how is one to determine the range of the cells to copy?

×

1 Answer

  • Level 1:

    An expert who has achieved level 1.

  • Contributor
  • 2 Answers

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


'save the return values
wksname = ActiveSheet.Name
returncell = ActiveCell.Address
searchfor = ActiveCell.Value

'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

Cells.FindNext(After:=ActiveCell).Activate

'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

Posted on Nov 29, 2008

Add Your Answer

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video. Add

×

Loading...
Loading...

Related Questions:

7 Answers

Software problem; Lotus 1-2-3 Release 9 for Windows


Do Alt Print Screen and the paste into wordpad the print it

Mar 05, 2008 | Compilers & Programming Tools

Tip

How to export datagridview data to excel in C#


First, add this to your namespace.<br /><span style="font-weight: bold;">using Microsoft.Office.Interop.Excel;</span><br /><br />Then, copy the codes below and paste it into the export button click event.<br /><br /> <span style="font-weight: bold;">Microsoft.Office.Interop.Excel.Application wapp;</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> Microsoft.Office.Interop.Excel.Worksheet wsheet;</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> Microsoft.Office.Interop.Excel.Workbook wbook;</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> wapp = new Microsoft.Office.Interop.Excel.Application();</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> wapp.Visible = false;</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> wbook = wapp.Workbooks.Add(true);</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> wsheet = (Worksheet)wbook.ActiveSheet;</span><br style="font-weight: bold;" /><br style="font-weight: bold;" /><span style="font-weight: bold;"> if (dataGridView1.RowCount == 0)</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> {</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> mssgbox_noretrieve();</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> return;</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> }</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> try</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> {</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> for (int i = 0; i &lt; this.dataGridView1.Columns.Count; i++)</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> {</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> wsheet.Cells[1, i + 1] = this.dataGridView1.Columns[i].HeaderText;</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> }</span><br style="font-weight: bold;" /><br style="font-weight: bold;" /><span style="font-weight: bold;"> for (int i = 0; i &lt; this.dataGridView1.Rows.Count; i++)</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> {</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> DataGridViewRow row = this.dataGridView1.Rows[i];</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> for (int j = 0; j &lt; row.Cells.Count; j++)</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> {</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> DataGridViewCell cell = row.Cells[j];</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> try</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> {</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> wsheet.Cells[i + 2, j + 1] = (cell.Value == null) ? "" : cell.Value.ToString();</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> }</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> catch (Exception ex)</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> {</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> MessageBox.Show(ex.Message);</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> }</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> }</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> }</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> wapp.Visible = true;</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> }</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> catch (Exception ex1)</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> {</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> MessageBox.Show(ex1.Message);</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> }</span><br style="font-weight: bold;" /><span style="font-weight: bold;"> wapp.UserControl = true;<br /><br /><br /><br /></span><br /> That's all and enjoy...<br /> Larrix<br /> <span style="font-weight: bold;"><br /></span>

on Jul 09, 2011 | Compilers & Programming Tools

Tip

How to datagridview to excel in C#


First, add this to your namespace.
using Microsoft.Office.Interop.Excel;

Then, copy the codes below and paste it into the export button click event.

Microsoft.Office.Interop.Excel.Application wapp;
Microsoft.Office.Interop.Excel.Worksheet wsheet;
Microsoft.Office.Interop.Excel.Workbook wbook;
wapp = new Microsoft.Office.Interop.Excel.Application();
wapp.Visible = false;
wbook = wapp.Workbooks.Add(true);
wsheet = (Worksheet)wbook.ActiveSheet;

if (dataGridView1.RowCount == 0)
{
mssgbox_noretrieve();
return;
}
try
{
for (int i = 0; i < this.dataGridView1.Columns.Count; i++)
{
wsheet.Cells[1, i + 1] = this.dataGridView1.Columns[i].HeaderText;
}

for (int i = 0; i < this.dataGridView1.Rows.Count; i++)
{
DataGridViewRow row = this.dataGridView1.Rows[i];
for (int j = 0; j < row.Cells.Count; j++)
{
DataGridViewCell cell = row.Cells[j];
try
{
wsheet.Cells[i + 2, j + 1] = (cell.Value == null) ? "" : cell.Value.ToString();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
wapp.Visible = true;
}
catch (Exception ex1)
{
MessageBox.Show(ex1.Message);
}
wapp.UserControl = true;




That's all and enjoy...
Larrix



on Jul 09, 2011 | Compilers & Programming Tools

1 Answer

In microsoft excel sheet how do we copy zodiac signs written in front of employee from one sheet to another by writing a formulae in the blank space eg 1 amit scorpio 2 divik saggitarius 2 tisha...


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:
=[file-name]sheet-name!cell-name

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

Sep 06, 2009 | Compilers & Programming Tools

1 Answer

C++


http://www.cplusplus.com/doc/tutorial/

May 01, 2009 | Compilers & Programming Tools

1 Answer

How to lock cell, column or row?


Lock (Colume / Row)
  1. Select colume or row that you want to lock (by default . it is locked)
  2. Right click and select menu 'Format cell'
  3. Click tab "Protection" and then check on "Lock" option.
  4. Click ok and close this dialog.
  5. Go to ribbon 'Review' and select "Protect sheet"
  6. Typing you password
  7. Saving and close your workbook and reopen again.

Nov 19, 2008 | Microsoft Compilers & Programming Tools

1 Answer

Fanuc cnc macro programming


Dim Str As StringStr = "ashu"
MsgBox UCase(Left(Str, 1))

Cut the first Char and Concatenate other

Aug 01, 2008 | Compilers & Programming Tools

1 Answer

Program


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

Feb 27, 2008 | Compilers & Programming Tools

Not finding what you are looking for?
Compilers & Programming Tools Logo

Related Topics:

429 people viewed this question

Ask a Question

Usually answered in minutes!

Top Compilers & Programming Tools Experts

Robin Hill
Robin Hill

Level 3 Expert

1251 Answers

Fred Block
Fred Block

Level 2 Expert

289 Answers

Les Dickinson
Les Dickinson

Level 3 Expert

18299 Answers

Are you a Compiler and Programming Tool Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides

Loading...