Macro ?-moving info from 1 sheet to first blank section on another

[carrera]

I'm creating a spreadsheet with 10 tabs, each one being a different hosptial.
Each section of rows on a sheet will contain various patient information (4
rows per patient)

For when patients transfer from one hospital to another, I'm going to have
hyperlinks so the user can highlight the information on one section of rows,
of let's say Southside Hospital, click on the hyperlink, and have the
information cut from that section (deleting those rows) and pasting into the
next available section in the receiving hosptial, let's say Northside
Hospital.

I'm recording other macros on each sheet for other purposes, but I don't
know how to get it that the information won't be pasted over existing
information on another patient.

Thanks

 

[Jim Thomlinson]

Here is some code that will find the first blank cell in a column...

cells(rows.count, "A").end(xlup).offset(1,0).select
What this is doing is it it going to Cell A65,536 and then traveling up to
the first cell with data in it. It then offsets down 1 cell to give you the
first blank cell.

 

[carrera]

Very cool Jim. I inserted it in the right place, and so far, so gool
However, that uncovered a small glitch in my macro...when I was recording
the macro originally, my intent was to be able to highlight any 4 rows and
run the macro. But, the first line of the macro reads

Rows("14:17").Select

so of course it keeps taking those 4 rows.
How do I rewrite that line so it take whatever 4 rows I highlight, then runs
the macro?

Thanks

 

[TB6387]

I'm creating a spreadsheet with 10 tabs, each one being a different hosptial.
Each section of rows on a sheet will contain various patient information (4
rows per patient)

For when patients transfer from one hospital to another, I'm going to have
hyperlinks so the user can highlight the information on one section of rows,
of let's say Southside Hospital, click on the hyperlink, and have the
information cut from that section (deleting those rows) and pasting into the
next available section in the receiving hosptial, let's say Northside
Hospital.

I'm recording other macros on each sheet for other purposes, but I don't
know how to get it that the information won't be pasted over existing
information on another patient.

Thanks

Alternitvely you could use something like
ActiveSheet.UsedRange.Rows.Count and add one to it to get the next
unused row.

For Instance, say you have some data found in Cells A1:C4 on Sheet1
that when you click this hyperlink you want it to remove the rows from
Sheet1 and move it to the first unused portion of Sheet2 to you could
use the following code:

Sheets("Sheet2").Range(Sheets("Sheet2").Cells(Sheets("Sheet2").UsedRange.Rows.Count
+1,1),Sheets("Sheet2").Cells(Sheets("Sheet2").UsedRange.Rows.Count
+5,1)).Value = Sheets("Sheet1").Range("A1:C4").Value ' Copy data to
other sheet

Sheets("Sheet1").Range("A1:C4").Delete Shift:=xlUp ' Delete old data

Although this may look long it has several advantages:
Setting range values equal to eachother is faster than using the copy
and paste method
Using the Sheets("Sheetx"). allows you to not have to change sheets to
run the macro, also making it run faster and removing screen flicker
associated with most macros.

-Terry

 

[TB6387]

Very cool Jim. I inserted it in the right place, and so far, so gool
However, that uncovered a small glitch in my macro...when I was recording
the macro originally, my intent was to be able to highlight any 4 rows and
run the macro. But, the first line of the macro reads

Rows("14:17").Select

so of course it keeps taking those 4 rows.
How do I rewrite that line so it take whatever 4 rows I highlight, then runs
the macro?

Thanks








- Show quoted text -

Try using ActiveCell.Row to detect which row is selected.

 

[Tom Hutchins]

If you are going to manually select the rows, then you can just use Selection
in your macro (they are already selected). Or, Selection.EntireRow.Select
will work whether whole rows or just cells are selected.

Hope this helps,

Hutch

 

[carrera]

Thanks Tom, once the solutions are in front of me, they make perfect sense,
that worked perfectly.

Thanks Terry for your contribution, I'm sure it would work too, but I was
already on this track, and didn't want to recreate the wheel.