Excel, 2 worksheets are linked, if row has data already goto row below 1

[coolcarls]

I'll try to explain this as best I can.
Worksheet1 row 1 is linked to the data I enter into worksheet2 row 1. Now I clear the data in worksheet2. A day or so goes by and I open up worksheet2 and enter new info, I am trying to get it to automatically be entered into worksheet1 row2 after detecting that that is the next available empty row.
I know that this is do-able as I have done stuff like it in the past, but I have not VBA'd for a while and am rusty, I suffer from long term loss of short term memory


Thanks

 

[DaleWatson123321]

Hi coolcarls,

Here's a routine that should work...

Note: This requires that you assign two range names:
1) "data" - to your data range on Worksheet1.
2) "input_range" - to your input range on Worksheet2.

Sub TransferData()
Application.ScreenUpdating = False
Application.Goto Reference:="data"
nextblank = [A65536].End(xlUp).Offset(1, 0).Address
Range(nextblank).Select
Range("input_range&quot.Copy
ActiveSheet.Paste
Application.Goto Reference:="R1C1"
Application.Goto Reference:="input_range"
Application.CutCopyMode = False
Application.Goto Reference:="R1C1"
Application.ScreenUpdating = True
End Sub

I have attached this to a button, so it's just a matter of clicking the button whenever you want to add another item to your database.

The only thing about your description that concerns me is what you refer to as "linking". I'm unsure what you mean by linking - i.e. how the linking is set up ... via formula to another sheet, or via formula to another workbook, or simply meaning that data is to be transferred from one worksheet to the other worksheet (which is what I created).

If you would like a copy of my file (that I created specifically for this situation), or if you would like to emial me your file, perhaps this will speed up the process.

Hope this helps get things started.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[coolcarls]

Thanks for all your work and especially you generosity in sharing your knowledge, it’s an amazing quality