Copy rows into another workbook

[Relaxeed]

I have 2 workbooks. The data in the first comes from a download. Using a link into a database, the 1st workbook updates the data to show where the product is sold. Once this is done, I then have to copy the sales info into the 2nd workbook. The 2nd workbook has lots of separate sheets eg a sheet for sales in London, a sheet for sales in Manchester etc. Is there anyway that once the first sheet updates and shows where the sale is made I can automatically import these sales into the correct sheets in the 2nd workbook??

 

[DonkeyOte]

Yes - look up the "INDIRECT" function - as long as your worksheets are called "London" etc in the 2nd workbook this will be no problem at all.

 

[Relaxeed]

Forgive me if I'm missing something.

If I had 3 sales in workbook 1. One for Manchester and two for London. How can I get them into workbook 2 so that the Manchester Sale goes into the Manchester sheet in workbook 2 and the two London Sales go into the London workbook in workbook 2.

I've also realised this. The London and Manchester sheets in workbook 2 have previous sales there. So it would need to add the new sales underneath the ones already there.

 

[DonkeyOte]

You would simply set the variable for the worksheet in workbook2 to equal the sale location in workbook1.

You can loop through the existing sales in workbook2 sheet London - to find the next blank cell. Then you copy the sale from workbook1 into Workbook2 into the appropriate sheet and the appropriate cell(s).

Mail me an example if you want and I'll put the basics into code.

 

[DonkeyOte]

You would simply set the variable for the worksheet in workbook2 to equal the sale location in workbook1.

You can loop through the existing sales in workbook2 sheet London - to find the next blank cell. Then you copy the sale from workbook1 into Workbook2 into the appropriate sheet and the appropriate cell(s).

Mail me an example if you want and I'll put the basics into code.

 

[DonkeyOte]

Not the prettiest solution to look at it but it works.

Don't forget to put

Application.screenupdating = false

to make it run a little quicker.



Sub DATA_COPY_LOCATIONSHEET()

Workbooks.Open ("C:LUKETOTALSALES.XLS")

Windows("DAILYSALES.XLS").Activate
Sheets("SHEET1").Select

DATA = Sheets("SHEET1").Range("F2:F100")
CURRENT_ROW = 2

For Each SALE In DATA

If SALE = "" Then Exit Sub

Location_SHEET = SALE

Windows("TOTALSALES.XLS").Activate
Sheets(Location_SHEET).Activate
Data2 = Sheets(Location_SHEET).Range("A2:A15000")
START_ROW = 2

For Each I In Data2

If I <> "" Then

START_ROW = START_ROW + 1

End If

Next I

Windows("DAILYSALES.XLS").Activate
Sheets("SHEET1").Range(("A" & CURRENT_ROW) & ":" & ("G" & CURRENT_ROW)).Copy

Windows("TOTALSALES.XLS").Activate
Sheets(Location_SHEET).Select
Range("A" & START_ROW).PasteSpecial xlPasteValues
ActiveWorkbook.Save

Windows("DAILYSALES.XLS").Activate

CURRENT_ROW = CURRENT_ROW + 1

Next SALE

Windows("TOTALSALES.XLS").Close SAVECHANGES = False



End Sub

 

[Relaxeed]

Many thanks. Much appreciated. That's exactly what I needed. You are awarded 5 stars!!!

 

[Mortimer Knag]

So where does the "INDIRECT" function fit into all this?

 

[DonkeyOte]

you could still use the indirect if you wanted to but in this instance not entirely necessary as the second workbook is set to open and the sheet will be determined by the loop.