copying to a new worksheet

[ctomblin]

I create a new worksheet at the beginning of every week and would like to be able to copy the final-total cell from last weeks worksheet into the new worksheet

 

[NateO]

Sounds like a cake walk. You can copy or paste or use vba. Depends on the cell location of the total and the cell location of the deposit.

 

[NateO]

Sample code (in the event you'd like vba):

Sub Copy()
Sheet1.[a1].Copy Sheet2.[a5]
End Sub

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-04-26 08:49

 

[Joe Was]

Try this:

Sub DataList()
'To use build a data list on Sheet1.
'Put a lable in A1 on Sheet2.
'Then select data on Sheet1 & run this code.

'Copy the current selection on Sheet1 &
'amend paste selection to bottom of data
'list on Sheet2.

'Copy the current selection on Sheet1.
Sheets("Sheet1").Select
Selection.Copy

'Go to sheet2, find the next empty cell & paste.
Sheets("Sheet2").Select
ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

'Go back to the original sheet.
Sheets("Sheet1").Select
Range("A1").Select
End Sub

Hope this helps. JSW

 

[NateO]

One could streamline JWS's a little, runs faster without selecting and extends the life of your keyboard:

Sub DataList2()
Selection.Copy Sheet2.[a65536].End(xlUp).Offset(1, 0)
End Sub

Or, if you can create methodology to consistently identify your target, you substitute [a1] with that methodology:

Sub DataList3()
Sheet1.[a1].Copy Sheet2.[a65536].End(xlUp).Offset(1, 0)
End Sub

No harm intended here...
_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-04-26 09:16

 

[Joe Was]

NateO,

No harm taken.

I know the selects are not needed, I just like to keep the code self-explaining for both new users and complex code.

Plus, you and I both know just about any code can be made specific or universal. Does the user want to select the cell to perform the action on or does the user want the same cell or range used each time?

Thanks, we all should like all the help we can get. JSW
This message was edited by Joe Was on 2002-04-26 09:56

 

[NateO]

Completely agree with your post JWS. With the [a1] deal, I meant you substitute it with:<pre>[a65536].End(xlUp).Offset(1, 0)</pre> or other ways of copying a range, even if it's 'moving' target.

It's tricky responding to fairly vague posts...Have a good one.


_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-04-26 10:07