RE: Cell referencing in Macros

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Thanks JL,
The lastrow function has helped. The bulk of my macro now works. I am
having problems inserting the data from one sheet to the bottom of the list
on the other sheet. I have some total formulas that I need to shift down so
they remain at the bottom of my list. I'm having problems getting past the
error that requires the paste range to be the same size as the copied cells.
If I do it by hand, I can highlight the rows all the way across (use the
mouse and click on the row numbers to highlight all the way across) and then
use the mouse again to highlight the lastRow+1 all the way across and then
paste and it shifts my total formulas down.

Below is the bottom part of my macro. It's stopping at about the 5th line
down. Any suggestions? Thx

Range("A1:I" & lastRow).Select
Selection.Copy
Sheets("Medical 2009").Select
lastRow = Sheets("Medical 2009").Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("A227:IV227").Select
Selection.Insert Shift:=xlDown
Range("A232").Select
Selection.End(xlDown).Select
Range("A422").Select
Range("D2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=+'Quickbooks Import'!R[lastrow]C"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:E2"), Type:=xlFillDefault
Range("D2:E2").Select
Range("D2:E2").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D2").Select
Application.CutCopyMode = False
Sheets("Quickbooks Import").Select
Cells.Select
Range("A1:I" & lastRow + 5).Activate
Selection.ClearContents
Range("A1").Select
Sheets("Medical 2009").Select

"JLGWhiz" wrote:

You can set the last row in a range to a variable:

lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

The above line of code finds the last cell with data
in column A of the active sheet. To use it in a range
variable you would:

Set myRange = Range("A2:A" & lastRow)

If you want to then put something into the next empty
cell you would:

Range("A" & lastRow + 1) = 'Something

Once you have defined the range as myRange you can:

myRange.Select

But I personally try to stay away from using Select
since you and write code without having to select a
range or a cell to do something.


"Bern Notice" wrote:

When writing macros that will highlight and move data around, how do you
highlight the data when it will have different numbers of rows each time you
run the macro. Is there a way to simulate the typing <end><down arrow> to go
to the bottom of the range (no matter how big or small it is)?

Also, how do change the reference cell each time you run the macro. For
example, you want to continue to add data to the end of the list which keeps
growing each time you run the macro. I am having problems because my macro
wants to insert the copied data in the same cell reference each time instead
of going to the bottom of the list.

Does this have to be done in visual basic or can you create the macro with
the recorder?
.

Relevant Pages

  • Re: Capturing The First Change in a Formula
    ... Did you put the macro in the sheet ... "Otto Moehrbach" wrote: ... So the change is at a cell level - not at the worksheet level. ...
    (microsoft.public.excel.worksheet.functions)
  • RE: Cell referencing in Macros
    ... Now you can use the appropriate variable with its corresponding sheet ... they remain at the bottom of my list. ... Below is the bottom part of my macro. ... The above line of code finds the last cell with data ...
    (microsoft.public.excel.programming)
  • Re: Shading
    ... at Column A of the row that holds the active cell. ... I assume when you say you put the macro in ThisWorkbook that you mean ... I assume you are also putting it in the sheet ... If on sheet1 I click on jump, ...
    (microsoft.public.excel.worksheet.functions)
  • Re: checking that cells have a value before the workbook will clos
    ... The second macro must be placed in a standard module. ... Public CancelA As Boolean ... > group for data in each cell. ... Let's work with sheet EOS to demonstrate what you should do. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: lock a range question
    ... I added most of my unprotect code in the HideAllWageData macro (the ... macro will go to 30 pages and change a flag to match what is in cell ... protect code if neither condition was met, but got into a loop that I ... 'Start changing flags on each sheet here. ...
    (microsoft.public.excel.programming)