Data transfer between two sheets based on condition

**agasi999** · 11-25-2008, 06:28 AM

Hi,

I work for a sales team and we have a bit of a problem automating a process for copying data from one sheet to another based on phone numbers.As of now this process is manual and my team spend hours doing this manually.

We have weekly reports coming to us as shown in 'Week 34.xls' (copy of the sheets attached)spreadsheet,and we manually transfer data from Coloumn E and Coloumn AC into the sheet 'DM Tracker.xls',based on matching Telephone numbers in coloumn B of 'Week 34.xls' spreadsheet and coloumn B of 'DM Tracker'.

I was wondering if there is a way to automate this process? This will definitely save us hours of work,especially on those 'Dreadful Mondays'!

Any help on this will be very much appreciated..

Thanks,
Andy

**agasi999** · 11-25-2008, 06:31 AM

Sorry ..here are the attachments...thanks

**ravishankar** · 11-25-2008, 06:55 AM

Hi
keep both files open. Run the macro from sheet2 of dm tracker. it fills col C & D based on phone nos.
Ravi

**agasi999** · 12-04-2008, 06:08 AM

Thanks Ravi! I tried running your code,but I got a 'Run time error 9 - subscript out of range error' at line 4 'y = Workbooks(..)'

Thanks,
Matt

Sub pulldata()
x = Workbooks("DM Tracker.xls").Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
y = Workbooks("Week 34.xls").Worksheets("Overall").Cells(Rows.Count, 1).End(xlUp).Row
For a = 3 To x
Workbooks("DM Tracker.xls").Worksheets("sheet2").Cells(1, 26) = "=match(B" & a & "[Week 34.xls]overall!B2:B" & y & ",0)"
b = Workbooks("DM Tracker.xls").Worksheets("sheet2").Cells(1, 26)
Workbooks("DM Tracker.xls").Worksheets("sheet2").Cells(a, 3) = Workbooks("Week 34.xls").Worksheets("overall").Cells(b, 5)
Workbooks("DM Tracker.xls").Worksheets("sheet2").Cells(a, 4) = Workbooks("Week 34.xls").Worksheets("overall").Cells(b, 29)
Next a
MsgBox "collecting data complete"
End Sub

**VBA Noob** · 12-04-2008, 10:09 AM

Thread closed for ignoring forum rules.

PM me after you read the forum rules below and agree to add the link to the other excel forum and start using code tags

VBA Noob

**arthurbr** · 11-25-2008, 06:39 AM

Welcome to the forum.

Please read the Forum Rules about thread titles, and then edit yours to be descriptive of your problem.

Thanks.

**agasi999** · 11-26-2008, 06:37 AM

Hi Arthur,

Ive changed the title to reflect the nature of the problem. Please can you remove the lock on the reply provided by ravishankar?

Regards,
Matt

**arthurbr** · 11-26-2008, 09:31 AM

Hi Matt,
undeleted as asked.
Thx for your cooperation

**VBA Noob** · 11-26-2008, 04:01 PM

agasi999,

If you read the forum rules like you we're asked you would have seen cross posting without the link is not permitted.

Please add the link to the other forum

VBA Noob

Data transfer between two sheets based on condition

LinkBack

Thread Tools

Display

Hybrid View

Data transfer between two sheets based on condition

Hi Macro help

macro

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions