VBA to take data in a sheet and paste it into another open workbook but into individual tabs by matching the names in columns without taking forever?

[tonywatsonhelp]

Hi Everyone,
I hope someone can help me?

I have a workbook that I will run the macro from that I'd like to just refer to as "Thisworkbook" if possible but its Called "Weekly Sales"
the sheet is "Sales"

I the have another workbook Called "Salespersons Data1" and this hold all the weekly sales for all salespersons in their own tab with the dates in Row 5 of each tab and the data being added from row 7.

Both workbook will be open when I run the macro, and its ok to assume that all sheet names exist as they will.

So the salespersons sales are recorded in sheet "Sales" columns H to AZ of "Thisworkbook" and the "date the info is for" is in cell B3.

Now because Not every sales person makes a sale everyweek etc. I have used Row1 to identify the Columns of data I want to copy and have marked them with the word "Move"

Each salespersons has their own Column, The Name in Row 8 is the name of the tab the data needs to go into in the "Salespersons Data1" workbook, and the range is row 10 to Lastrow (which can be worked out using row 1 as they will all have the same last row.

So, what I need is a macro that can look along Row1 find each Column that has the word "Move" in row1, goto Row 8 of that column, get the tab name and got to cell B3 and get the date its for, then copy Rows "10:lastrow" of that column, goto workbook "Salespersons Data1" look for the sheet called "the name in row8" select that sheet, Look along Row 5 until you find the matching date and paste the values in from row7.

To put it another way

macro runs,
find "Move" in L1
Get Name "David" from L8
Gets Date From B3

Copies data in cells L10 to L & lastrow

activates workbook "Salespersons Data1"
looks for sheet named "David" goes along Row 5 and finds the column with the date that matches "B3" and pastes values into that Column for row7.

If it helps, every tab in workbook "Salespersons Data1"is the same and there is one tab called "Master" you could have it look up the Column for the date in master and then use that column every time as it does not need to find it each time.

Please help if you can thanks

Tony

 

[tonywatsonhelp]

Just wondering if anyone had any help, I'm really stuck on this on?
Tony

 

[tonywatsonhelp]

If its easier to just give me a way I don't have to repeat code 200 times then great because the code below work great

If Cells(1, 10).Value = "Move" Then
Range(Cells(10, 10), Cells(LastRow2, 10)).Copy
Workbooks(WBName & ".xlsm").Activate
Sheets(DataSheet.Cells(8, 10).Value).Select
Cells(8, M).PasteSpecial xlPasteValues
Range("s3").Select
ThisWorkbook.Activate
Cells(1, 10).Value = Now()
End If


If Cells(1, 12).Value = "Move" Then
Range(Cells(10, 12), Cells(LastRow2, 12)).Copy
Workbooks(WBName & ".xlsm").Activate
Sheets(DataSheet.Cells(8, 12).Value).Select
Cells(8, M).PasteSpecial xlPasteValues
Range("s3").Select
ThisWorkbook.Activate
Cells(1, 12).Value = Now()
End If


If Cells(1, 14).Value = "Move" Then
Range(Cells(10, 14), Cells(LastRow2, 14)).Copy
Workbooks(WBName & ".xlsm").Activate
Sheets(DataSheet.Cells(8, 14).Value).Select
Cells(8, M).PasteSpecial xlPasteValues
Range("s3").Select
ThisWorkbook.Activate
Cells(1, 14).Value = Now()
End If

So if there is some way I could use this code but say cell are 10 to 250 (every other one i.e 10,12,14,16 etc)

and just get it to run the code for each then great?

Tony

 

[tonywatsonhelp]

Sovled! thanks