Macro Help please

  • Thread starter Thread starter James201
  • Start date Start date
J

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
 
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
 
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
 
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
 
Back
Top