Macro Help please

[James201]

Posted: Wed Feb 04, 2004 2:12 pm Post subject: Excel Macro

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

Im trying to do a macro in excel but im having some trouble, i thin
its caused by me getting the relative button wrong at times.

what im trying to do is use a macro that can copy information from on
sheet to a grid on another sheet.

so i start the macro and use ctl+shift+* to select the table i want i
in then ctl+downarrow to go to the bottom of the grid then press dow
to copy onto the last line. then i go into the other sheet and copy
individual cell back into the sheet i want it in and then press trab t
move across, then back into the sheet with the data where i cop
another cell of data into the other sheet but its going wrong. iv
managed to get it to work fine using 1 cell but when i use across arro
or tab it seems to start going wrong. i think its the relative cel
button im having trouble with.


does anyone understand what im trying to do if you could help me id b
very greatfull thanks. if you dont understand let me know il try an
xplain again thanks for ne hel

 

[Don Guillett]

As always, you should post your coding efforts for comments.

 

[James201]

heres the closest ive come to what i want i didnt do my full versio
just an example of 2 of the cells i wantedf to do.

with this i created a macro button but when i click it it doesn
allways enter the data on the end line of the data page where i want i
to end up. dont no if u understand wat im tryin 2 do.


Sub Macro1()
'
' Macro1 Macro
'

'
Range("A9").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Sheets("Expenses Form").Select
Range("B4").Select
Selection.Copy
Sheets("Expenses Database").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Range("A1").Select
Sheets("Expenses Form").Select
Range("D4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Expenses Database").Select
ActiveSheet.Paste
Sheets("Expenses Form").Select
Application.CutCopyMode = False
End Su

 

[James201]

when i run the macro from the "database" sheet it works but when i tr
using the buton on the "form" page it inserts the data into whcheve
cell that is selected on the other sheet.


anyone help please

 

[Dave Peterson]

It sounds like you know where you want to grab the data from and where to paste
it.

If that's the case, you can get rid a lot of the selects and just do it
directly:

Option Explicit
Sub testme01()

With Worksheets("sheet1")
.Range("a9", .Range("a9").End(xlDown).Offset(1, 0)).Copy _
Destination:=Worksheets("expenses form").Range("b4")
End With

Application.CutCopyMode = False
End Sub

I used sheet1 as the source worksheet--I like that better than activesheet in
lots of cases.

And there are techniques to find the next open cell in row 4 when you're
pasting.

Option Explicit
Sub testme01A()

Dim DestCell As Range

With Worksheets("expenses form")
Set DestCell = .Cells(4, .Columns.Count).End(xlToLeft).Offset(0, 1)
End With

With Worksheets("sheet1")
.Range("a9", .Range("a9").End(xlDown).Offset(1, 0)).Copy _
Destination:=DestCell
'if you need to move over one cell for the next paste:
Set DestCell = DestCell.Offset(0, 1)

'next paste here
End With

Application.CutCopyMode = False
End Sub