Moving Excel Data between workbooks 1

[rob9740]

Hi all,

I'm try to automate the transfer of a large list of data from a report to an archive. I've a basic macro that will move it across but you have to select the data to move and then you have to make sure that the cells it'll be transferred to haven't got data from the previous weeks or it'll be overwritten and lost.

Does anybody know if there is a way to make the macro itself choose the lists (which are named) and transfer them over to the archive workbook without overwriting what's already there?????

Cheers,
Rob

 

[Hasit]

You'll have to use some code to do that work for you. I am not an expert in VB, so I don't want to give you code that may be complete rubbish. Others in the expert list may be able to help.

 

[Abi1]

To get the macro to chose the list, I'd create a form so that the user can input the list to move.

The code you use depends on how you've named the lists. For example, if you've given the cell at the top of the list a range name then you could select the list by using something like:

Range("my range&quot.Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select

You also wanted the macro to check if the new workbook is empty. I'd do this at the beginning. If you name a range in the target workbook that you want to transfer into, then you can use the following code:

Option Explicit
Dim myrange As Range
Sub Is_It_Blank()
Set myrange = Range("myrange&quot
If myrange.Value = "" Then Call Datatransfer Else MsgBox ("Target workbook is not empty&quot
End Sub

Sub Datatransfer()
MsgBox ("Data is being transfered&quot
End Sub


Where you would put the data moving code within the Datatranfer subroutine.

I hope this is clear! If it isn't, give me a few more details about what you're trying to do and I'll try to help.

Abi

 

[rob9740]

Cheers for that Abi.

I don't want to use a form cos I just want to keep the users input as simple as possible. I'm going to try and tie the macro into a button so they can just press it and everything will go through as planned, hopefully.

I'm looking more for the macro to make sure the transferring data is appended and is not going to overwrite the data that's already in the archive workbook rather than seeing if it's empty. Does that check to see if the entire range is empty or does it look for the next empty space in the range???

You've given some ideas to play with for the mo any way. If you come up with any other ideas post them up..

Cheers again,
Rob

 

[rob9740]

I can't figure out why I'm getting a type mismatch from the "if..then..else" statement?? Anybody any ideas cos i'm stumped.

Rob

 

[Abi1]

Make sure your If..then..else is on one line.

Macros tied to buttons are fine. You'll have to have a different button for each range.

The code I've written above will just look at whether the target cell in the target workbook is empty. If not, it doesn't do anything. If you want it to copy into the next blank cell, you need the following code:

Range("my_range&quot.Select

If ActiveCell.Value = "" Then
ElseIf ActiveCell.Offset(1, 0) = "" Then ActiveCell.Offset(1, 0).Select
Else
ActiveCell.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
End If

This select the range "my_range" then, if it isn't empty, it will move to the next empty cell (downwards) and make that the ActiveCell.

Abi

 

[rob9740]

Thanks a mil for that, Abi.
It works perfect on a tested range so hopefully it'll work on the real thing.
Thanks again,
Rob