Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

Find Copy Paste From A Sales Report To Another Sheet

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I need some help and would greatly appreciate anything the gurus here can provide.

I'm trying to create a macro where Excel will search SalesA for select SKU I have listed in a specific cell in ReportSheetB, copy the associated sales data (4 columns to the right of the SKU on the Sales Report A) and then paste it next to the associated SKU on ReportSheetB.

Another example-
Two Spreadsheets-
SalesA
ReportSheetB

SKU is WidgetA (the sku could change from time to time)

I have the SKU listed on ReportSheetB in Cell A3.
Have Excel search SalesA for the Row that has the SKU (from A3 above).
Copy the sales data that is 4 columns to the right of the SKU listing.
Paste the sales data next to the right of the SKU on ReportSheetB (Cell B3).

I tried several of the previously methods on here but I can't get it to work. I also tried the VBRecorder, but it doesn't handle the Copy/Paste/Find sequence correctly. (Or I'm just too braindead to make it work).

So any help that can be provided would be awesome. (Also many many thank yous in advance)


Similar Excel Video Tutorials

Helpful Excel Macros

Replace Formulas with Values (For The Entire Workbook)
- This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
- This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
Filter Data in Excel to Display Records that Contain a Value Between Two Values - AutoFilter.
- This free Excel macro filters data to display only those records or rows that contain a value between two values. For e
Automatically Run a Macro at a Certain Time - i.e. Run a Macro at 4:30PM every day
- Automatically run an Excel macro at a certain time. This allows you to not have to worry about running a macro every da
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth

Similar Topics







I have a sales report setup in Excel workbook with each sheet has sales details for the different weeks. And on the last sheet, I have a total sales list, where every week, I manually copy each week and paste in this sheet.
Questions:
1. Is there a way I where I can update the Total Sales sheet without manually copy & paste.
2. Can I take this data and create a database in Excel where I can pull different reports by sale date, sales person, weekly and monthly report...etc?

Some of the great minds out there, I hope you an assist here. Please see image attach of a sample report.


Hello everyone,
I've been increasingly working with Excel for my job. With some tasks becoming repetitive, I thought that I should start learning Macros to make what I have to do as quick and efficient as possible.

I don't have experience working with Macros, although I've been studying VB.Net for several months now, so that should speed along my learning process at least a little bit.

One of the first Macros I hope to write is something that I believe would help big time with some of the sales report that I have to do.

One simple report for example is I take the weekly sales figures for each of our sales people and put them into a spreadsheet every month, while comparing it to the previous year... It's not a difficult report to put together, but there's one issue that makes it a pain in the rear end. In the software that we use to store our sales reports, if a sales person has $0 in sales for the date range that I've specified, they will not show up at all. This makes me unable to just copy and paste columns because a sales person may be in the data one week and not in the data the next. I want to make a Macro that will compare a column listing sales people to a list of all of our sales people and create rows for sales person that is not already included in the column.

Since I'm new to this, I was hoping somebody could give me either some sample code or a suggestion on how to best go about accomplishing this. Thank you in advance for any helpful feedback.


Hello:
Every week I run a report that contains 10,000 rows with multiple columns to review sales.
Example header columns:
Sales Rep Store On hand Brand Store sales

I want to extract the entire row of data where the stores on hand is greater than 1 and sales are less than 1. I want to make a separate sheet for these. I realize that I could autofilter, copy paste into another sheet very easily but I have 30 of them to do. Just trying to find more time in my day.

thank you for your help


I would like to create a macro that takes 1 line of data from cells A8:F8 on sheet 1 and pastes that line of data into cells A8:F8 on either Sheet 2 (Sales) or Sheet 3 (Expenses) depending on whether Sheet 2 (Sales) or Sheet 3 (Expenses) was selected in cell B5 on Sheet 1.

When the macro has chosen which sheet to paste the data into I would like it to either paste it into cells A8:F8 of that sheet if those cells are blank or search the next row down until it finds an empty row to paste the data into: A9:F9, A10:F10.

I would also then like it to clear the info in cell A8:F8 on Sheet 1.

Could someone please help me as I've gotten half way through it but don't know how to make it select the appropriate sheet

Thank you


Worksheet to copy to: Sales - Decides which sheet to paste data to

Data:
Name Criteria 1 Criteria 2 Criteria 3 Criteria 4 Criteria 5
Paul Smith 20 30 20 10 10 - data to cut and paste


Hi All,

I've raised this in another query but thought it best to start a new one as it is based on SQL language in a Microsoft Query.

The following SQL code comes up from a Microsoft Query that I run in Excel on a Data Connection to an Access Database.

My question is - does anyone know how to get the scode converted to a 'General' format in the following Microsoft Query SQL code?

Code:

SELECT DISTINCT `101 Sales 2010`.scode, `101 Sales 2010`.Brand, `101 Sales 2010`.Market, `101 Sales 2010`.`1`, `101 Sales 2010`.`2`, `101 Sales 2010`.`3`, `101 Sales 2010`.`4`, `101 Sales 2010`.`5`, `101 Sales 2010`.`6`, `101 Sales 2010`.`7`, `101 Sales 2010`.`8`, `101 Sales 2010`.`9`, `101 Sales 2010`.`10`, `101 Sales 2010`.`11`, `101 Sales 2010`.`12`, `101 Sales 2010`.`13`, `101 Sales 2010`.`14`, `101 Sales 2010`.`15`, `101 Sales 2010`.`16`, `101 Sales 2010`.`17`, `101 Sales 2010`.`18`, `101 Sales 2010`.`19`, `101 Sales 2010`.`20`, `101 Sales 2010`.`21`, `101 Sales 2010`.`22`, `101 Sales 2010`.`23`, `101 Sales 2010`.`24`, `101 Sales 2010`.`25`, `101 Sales 2010`.`26`, `101 Sales 2010`.`27`, `101 Sales 2010`.`28`, `101 Sales 2010`.`29`, `101 Sales 2010`.`30`, `101 Sales 2010`.`31`, `101 Sales 2010`.`32`, `101 Sales 2010`.`33`, `101 Sales 2010`.`34`, `101 Sales 2010`.`35`, `101 Sales 2010`.`36`, `101 Sales 2010`.`37`, `101 Sales 2010`.`38`, `101 Sales 2010`.`39`, `101 Sales 2010`.`40`, `101 Sales 2010`.`41`, `101 Sales 2010`.`42`, `101 Sales 2010`.`43`, `101 Sales 2010`.`44`, `101 Sales 2010`.`45`, `101 Sales 2010`.`46`, `101 Sales 2010`.`47`, `101 Sales 2010`.`48`, `101 Sales 2010`.`49`, `101 Sales 2010`.`50`, `101 Sales 2010`.`51`, `101 Sales 2010`.`52`, `101 Sales 2010`.`Wk 1`, `101 Sales 2010`.`Wk 2`, `101 Sales 2010`.`Wk 3`, `101 Sales 2010`.`Wk 4`, `101 Sales 2010`.`Wk 5`, `101 Sales 2010`.`Wk 6`, `101 Sales 2010`.`Wk 7`, `101 Sales 2010`.`Wk 8`, `101 Sales 2010`.`Wk 9`, `101 Sales 2010`.`Wk 10`, `101 Sales 2010`.`Wk 11`, `101 Sales 2010`.`Wk 12`, `101 Sales 2010`.`Wk 13`, `101 Sales 2010`.`Wk 14`, `101 Sales 2010`.`Wk 15`, `101 Sales 2010`.`Wk 16`, `101 Sales 2010`.`Wk 17`, `101 Sales 2010`.`Wk 18`, `101 Sales 2010`.`Wk 19`, `101 Sales 2010`.`Wk 20`, `101 Sales 2010`.`Wk 21`, `101 Sales 2010`.`Wk 22`, `101 Sales 2010`.`Wk 23`, `101 Sales 2010`.`Wk 24`, `101 Sales 2010`.`Wk 25`, `101 Sales 2010`.`Wk 26`, `101 Sales 2010`.`Wk 27`, `101 Sales 2010`.`Wk 28`, `101 Sales 2010`.`Wk 29`, `101 Sales 2010`.`Wk 30`, `101 Sales 2010`.`Wk 31`, `101 Sales 2010`.`Wk 32`, `101 Sales 2010`.`Wk 33`, `101 Sales 2010`.`Wk 34`, `101 Sales 2010`.`Wk 35`, `101 Sales 2010`.`Wk 36`, `101 Sales 2010`.`Wk 37`, `101 Sales 2010`.`Wk 38`, `101 Sales 2010`.`Wk 39`, `101 Sales 2010`.`Wk 40`, `101 Sales 2010`.`Wk 41`, `101 Sales 2010`.`Wk 42`, `101 Sales 2010`.`Wk 43`, `101 Sales 2010`.`Wk 44`, `101 Sales 2010`.`Wk 45`, `101 Sales 2010`.`Wk 46`, `101 Sales 2010`.`Wk 47`, `101 Sales 2010`.`Wk 48`, `101 Sales 2010`.`Wk 49`, `101 Sales 2010`.`Wk 50`, `101 Sales 2010`.`Wk 51`, `101 Sales 2010`.`Wk 52`
FROM `F:\Reports\Misc\2010\Access Reports\Flair Reports 2010.accdb`.`101 Sales 2010` `101 Sales 2010`





I have a main report contain all salesperson's sales and inventory data. The main report have two sheets: "sales" and "inventory". I need to create new workbook for each salesperson so i can email individual report to them. The new workbook need to have both 'sales' and 'inventory' sheets

Rows 1-3 are the headings that i need to copy and paste to new individual report.

Column A: salesperson name
Column B: sales or inventory location (one salesperson can have more than one locations)
Column C-F: item description, price, quantity and total sales amount

I'm new to macro and so far the only codes i could write is to create new workbooks for each sales person and copy&paste the entire rows. What i need now is codes to delete data of other salespersons and insert two blank lines between sales location.

Sheets(Array("Sales", "Inventory")).Select
Sheets(Array("Sales", "Inventory")).Copy

'Input box to name new workbook
newfilename = InputBox("Please specify new workbook name", "New Name")

ActiveWorkbook.SaveAs Filename:= _
"I:\" & newfilename & " - Sales Inventory Report.xls", _
FileFormat:=xlExcel8

Many thanks.

Hello everyone!
I know that this has probably been asked before, but I am not sure that the terms I am using to search are accurate for what I need - either way, I am coming up short on answers. Any help to the below would be most appreciated.

Basically the scenario is thus: I have a WB with 4 WS - A "Master" sheet and 3 "Sales-People" sheets. The sales people will be entering data in their respective sheets and afterwards the Sales Manager will hit a "button" to copy over each sales-person's data to the "Master" sheet. So far I have been able to copy and paste each to the master with basic macro skills, but the problem I am encountering is that the data in each of the sales sheets will be variable. Sometimes 5 rows, sometimes 50 rows. Each week it will change for each of the sales people. I have been successful with copying the data and then finding the next blank row to paste on the "Master" sheet but I have not been able to find a way to find the last row of data in the sales sheets and select everything in between. Every sheet, the data starts on A15 and at goes to AHxx. I just need to select and copy the rows.

Here is the macro as it stands:



Sub Macro2()
'
' Macro2 Macro
' Macro recorded 5/18/2010 by Eldanno2004
'

'
Sheets("MASTER Funnel").Select
Range("A15").Select
Sheets("T. Viox Funnel").Select
Range("A15").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A15:AH45").Select
Selection.Copy
Sheets("MASTER Funnel").Select
ActiveSheet.Paste
Range("A15").End(xlDown).Offset(1, 0).Select
Sheets("M. Asbach Funnel").Select
Range("A15").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A15:AH38").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("MASTER Funnel").Select
ActiveSheet.Paste
Range("A15").End(xlDown).Offset(1, 0).Select
Sheets("D. Rettenmaier Funnel").Select
Range("A15").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A15:AH32").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("MASTER Funnel").Select
ActiveSheet.Paste
Range("A15").Select
Sheets("Instructions").Select
Range("B18").Select
End Sub



Hello - I'm new to this forum and I'm hoping to find some help. I know some Excel basics but nothing too detailed for what I need to do. We have a quarterly sales report in my office that is broken up by zip codes in one of the columns and what I need to do is based on the zip code determine which sales rep that line belongs to.

I have a list of which zip codes belong to each sales rep - but is there a way I can enter in one reps list of zip codes to automatically search the workbook and filter all the matches to a new sheet? This report is large and it takes up quite a bit of time to do this manually so I was really hoping there was an easier way to do this.

Thank you so much in advance for any help you can provide.


I hope this makes sense because I have been having a difficult time trying to figure out a solution to this problem.

I have two spreadsheets and what I want is if there is a match between the two spreadsheets, I want a macro or formula to copy the sales column data from the spreadsheet that just has agent number and sales to the spreadsheet that has the agent number and blank cell for sales.

Example

Spreadsheet that has just the agent number and sales:



Spreadsheet that has agent number and sales but no data in the sales column:



Result desired for first row of the second spreadheet:

agent_number
25003

sales
311745.5

I would like this result to happen automatically for the rest of the rows of the second spreadsheet.

Any help with this would be greatly appreciated. Thank you.


Hi there,

I am pretty new to VBA code writing. I'm pretty sure what I am trying to accomplish is simple. I just can't figure it out and haven't been able to find any solutions on the web.

I am attempting to do the following in an Excel workbook, using VBA code:
(1) given a date value in a cell (c8) on one worksheet (named "INPUT")
(2) search a second work sheet ("Sales History") to find the same date. Note - the dates in the second work sheet are arranged in columns, by month. Each column (or month) has a variable number of dates (based on work days), that are listed in the rows down each column. There are 12 columns of dates (i.e. one for each month). The date columns are separated by columns of formulae that return sales data (therefore 24 columns in total).
(3) once the date is matched, I would like the code to move one column to the right and select the cell in the same row (e.g. offset(target date cell,1,0))
(4) now that the code has moved the right, I would like to do a copy, past special value to replace the formula with a hard coded value.

Thanks,
JimCanuck.


I am pulling data from a reporting tool, LogiXML and exporting the report to Excel. The data that is getting pulled is the weekly Sales data for the last 2 weeks.
Eg. This sheet (Weekly Sales Sheet)
Week 18
Week 19 Sales -56
Sales -24 Net Sales - 57
Net Sales - 45 Cancellation -0
Cancellation -2

Now I want to copy the above data to another sheet (Historical Weeks Sheet) at the end of the week. This Historical Week sheet contains the data for all the weeks.

Eg

Week 10
Week11
Week12


Week17 Sales -10
Sales -15
Sales -45


Sales -14 Net Sales - 20
Net Sales - 24
Net Sales - 56


Net Sales - 26 Cancellation -1
Cancellation -1
Cancellation -2


Cancellation -0


Can we automate this process so that the week 18 from Weekly Sales Sheet gets added to the Historical Weeks sheet? The Week 18 should get added to the next column L . Then it should automatically copy the Week 19 by appending the column and copying it on column N. Is this possible in Excel.
The data in the Weekly Sales sheet would be static i.e. only the last 2 weeks data would get displayed whereas the data in Historical Weeks sheet should keep getting added in the next columns.
Is there something that we can specify in Excel to check the Last generated column and then copy the columns in the next free column in the historical Weeks?


I have attached the Workbook to get a clear picture.



Thanks

Hello

I'm trying to write a macro that will insert a specific number of rows and copy & paste a row of data.

For the example below, Row 2 shows 5 car sales, so I want to insert 4 more rows beneath this and paste the same information on each of these rows, so I have each of the 5 sales on a separate line.

Date Store Product Sales
05-Mar-07 London Car 5

I then have other rows of data with different numbers of sales. Can I use a Loop based on the number in cell D2 (in this case 5) minus 1???

So far I can insert a row and paste the above row using:
Range("A2").Select
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert
ActiveCell.Offset(-1, 0).Select
ActiveCell.EntireRow.Copy
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.PasteSpecial

Thanks in advance!


Hi, I am a baby VBAer and could really use some help! Everything in this macro works as it should with one exception. The goal of the macro is to copy certain info from a doc called Sales Copy & paste it into another doc called PAA. In the code below it often refers to "Sales Copy1". In reality, this will often be titled something else like "Sales Copy 4" or "Sales Copy 12". How can I change this code to understand the doc is not always named Sales Copy1? I believe I should be using "dim nam as variant", but I'm not sure how or where. This is a fantastic forum & everyone is wonderful! I appreciate any help that can be offered! Thank you in advance.


Hello, I am almost giving up, I need to build a monthly report.
The WB is:
Tabs for months "Jan09","Feb09"......
Tab for join all "data"
Tab for "graphic"

I receive the monthly report... 10 columns, and 150 lines,

First Name, Middle Name, Last Name, age, rank, sales, work time, days worked.

This monthly report I copy and past to the correct month Tab.

The lines are names, which are different every month, most of then are same names, but sometimes there are new names or deleted names, the columns are sells and personal data of each name.

I build a macro, to copy all monthly data to a WS "Data", but I can't make
excel insert the different data in the right line, means, excel need to find the name and insert the sales, work time, days worked, these 3 columns are the only data changed, but some times a person does not work in the company anymore, or there are new employees, when this happens my macro does not work as needed.


Please let me know if need to provide more details.

Thanks a lot, Manolo


I have a spreadsheet that has 29,643 lines which has about 15 sales reps and the relevant sales data for each merchant. I created a pivot table out of this report that ends up (after I format it) creating the rep's on the left and then the sales # for each sale and the combination of revenue in one column, expenses in one column, and profit etc...... I then have to cut and paste each rep into another spreadsheet so I can send it to the appropriate rep....

My question after all that is......is there a way to do one of two things...Can I either have the pivot table create this report but have each sales rep loaded onto its own tab automatically instead of all the rep's being on one tab....or even better, is there a way I can have the report generated and each sales rep would create its own excel file.

Thanks for your help and if I did not explain something correctly let me know! You guys that answer my questions are a tremendous help so thanks in advance!


I have struggled for a day now searching and tweaking but I cannot figure this out.

Worksheet (Daily Sales) - daily input, copy to the worksheet (Weekly Sales), Monday through Saturday. The ranges copied are not consecutive. I am using If...then...else.

Code:

If Range("G3") = "Monday " Then
    Range("G3").Select
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("C3").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("C5:C13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("C4").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("C3:C4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("C14").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("C18").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("C16").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("C15:C17").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("C22").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("C19:C22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("C25").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("C34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("C19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
Else
    If Range("G3") = "Tuesday" Then
    Range("G3").Select
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("D3").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("c5:c13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("d4").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("C3:C4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("d14").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("C18").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("d16").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("C15:C17").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("d22").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("C19:C22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("d25").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("C34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("d19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Else
    If Range("G3") = "Wednesday" Then
    Range("G3").Select
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("e3").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("c5:c13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("e4").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("C3:C4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("e14").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("C18").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("e16").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("C15:C17").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("e22").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("C19:C22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("e25").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("C34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("e19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Else
    If Range("G3") = "Thursday" Then
    Range("G3").Select
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("f3").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("c5:c13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("f4").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("C3:C4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("f14").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("C18").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("f16").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("C15:C17").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("f22").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("C19:C22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("f25").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("C34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("f19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Else
    If Range("G3") = "Friday" Then
    Range("G3").Select
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("g3").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("c5:c13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("g4").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("C3:C4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("g14").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("C18").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("g16").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("C15:C17").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("g22").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("C19:C22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("g25").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("C34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("g19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Else
    If Range("G3") = "Friday" Then
    Range("G3").Select
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("h3").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("c5:c13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("h4").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("C3:C4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("h14").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("C18").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("h16").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("C15:C17").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("h22").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("C19:C22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("h25").Select
    ActiveSheet.Paste
    Sheets("Daily Sales Record").Select
    Range("C34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Weekly Sales Record").Select
    Range("h19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End If
End Sub


Thanks for any help. I have been away from excel for awhile would really appreciate any help.

Sandy2976


Hi.

I need some VBA code that will Find and then select a cell that has a value of "SALES" (and ignore cells that contain a string containing the word SALES (not whole), like "SALES REPORT").
I recorded a macro that does it ok, BUT, if it detects a cell that contains a string including the word SALES, like "SALES REPORT", it spits out an error message which I don't understand, even if a different cell contains the "SALES": "Run-time error '91': Object variable or With block variable not set"

This is my code -

Cells.Find(What:="SALES", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

Scenario #1 -
Cell A1 = [blank]
Cell A2 = 'SALES'

Here is my current challenge. I am just begining to learn VBA so any help would be appreciated.

Need to pull data from Sheet4 to sheet1 by Sales Person based on a validation list cell on sheet1.

Each Sales person has a different number of accounts listed on Sheet4. The data is setup like this (my apologies for not knowing how to copy and paste the data)

Will Use Jane Doe and John Smith As examples-

A1, Jane Doe, Customer Name, Data, Data, Data, etc.
A2, Jane Doe, Customer Name, Data, Data, Data, etc.
A3, John Smith, Customer Name, Data, Data, Data, etc.

So Jane Doe has 2 customers total, and John Smith has 1 customer.

I am dealing with a total of 300+ Sales People and over 4,000 customers, all with a different number of customers per sales person.

How in the world can I write a code that will pull all of Jane Doe's customers when she is chosen from the validation list, and paste those customers and their coinciding data where I need it to paste?

I have the validation list working, so all I need at this point is help with the copy/paste code.

Thank you in advance.

Shaun


I have an excel sales tracking sheet I use everyday at my business (retail sales). I sell pictures for tours of a state park. These tours leave every 20 mins so I track my sales by each tour time. What im trying to do is search a certain set of cells to look for the name of the seller and his/her sales for the day.


EX:

My table looks something like this:

SELLER | SALES | SELLER | SALES | SELLER | SALES
8:00AM jim 20 bob 45 sue 15

9:00AM sue 15 bob 30

etc etc.

so I have different people selling the product at each time and the dollar amount of their sales in the cell next to their name. Sometimes the names are not always in the same place (diff managers enter different names in diff places throughout the day).
What I would like to do is be able to search my table for the name and sales total for each day, so i could ultimately make a chart of weekely sales for employee to examine performance by my employees (to see who is working hard and who is hardly working )

Any thoughts??


Hello Mr. Excel forum goers,

I have a report that I download on the first of every month organized by sales agent (Agent 101, Agent 102, etc.). Every month I download one excel sheet with sales associated with the specific sales agent and I manually seperate this data into seperate sheets by sales agent. I want to automate the process of seperating the sales agent data into seperate sheets but I dont know how to tell excel to look in the "sales agent" column and find each change in sales agent. Can anyone help me with this part of the automation process?

on a side note, Thank you to everyone on this forum, you guys are awesome and I find myself constantly using this board to help me with my work.

PS I am new to posting. If there is a "best practice" for what type of information a poster needs to give that is not clear in the post above, please let me know so I can get my act straight.

Brian


Hi,

I am looking for a macro which i can assign to a button to copy the data of range A1:W230 of my sheet "sales" and paste them starting from cell A1 in my sheet " Data " in the same book,taking into consideration that sheet "sales" could be deleted and replaced by another sheet with same name, does that affect the macro or will still look for the presence of any sheet named "sales"?

Thank you very much .


What I mean by Field Copies is a report that I send to my field sales reps. I have an agreements spreadsheet that has all information regarding each account of ours. I only need to send my field sales reps certain information off of this spreadsheet (e.i. column A-C, F-H, ect.). I will be sending this spreadsheet to each rep including only the areas that they service. Instead of sorting the list by sales rep and copying/pasting into several different workbooks manually, I am looking for a macro that I could assign to each field sales rep that will automatically copy/paste their info into another workbook. Any help would be appricated. Thank you.


Here is yet another workbook I've been working on where I wrote the macros and they worked and now they don't. More specifically, when I went to add the count to the inventory, it keeps returning an error where the date is pasted from one sheet to the other. I had originally tried to reference the value of the date directly, but because it was being passed to a merged cell, Excel didn't like it too much, so I had it copy and paste it. That worked at first, now it suddenly doesn't.

If anyone could take a look, I've attached the workbook in all it's infuriating glory. Go to the count sheet, click the Add to Inventory button, and see what you get. I'm not great with VBA, but as you can see, I've been messing with it a good bit. Hopefully someone can see where I'm going wrong. Also, if anyone has any ideas to make to code function better, I'd love to hear it!

---Ok, so I've tried to attach the workbook about ten times now and I'm thinking it might just be cursed! Here is the macros that I have written. Hopefully someone can at least see something from this!
Code:

Sub AddInv()
'
'AddInvMacro
' Macro recorded 9/30/2009 by Jason Feilen
'
Dim inv As Worksheet
Dim mas As Worksheet
Dim rec As Integer
Dim item As String
Dim r As Long
Dim mr As Long

Set inv = Worksheets("Invoices")
Set mas = Worksheets("MasterInventory")
r = 2
mr = 2

'Find First Row Not Added to the Inventory
Do While inv.Cells(r, 8) = "Yes"
    r = r + 1
Loop

    'Add row to inventory
    Do While inv.Cells(r, 8) = "No"
        rec = inv.Cells(r, 6)
        item = inv.Cells(r, 4)
    
        'Find  item in master inventory
        Do While mas.Cells(mr, 2) <> inv.Cells(r, 4)
            mr = mr + 1
        Loop
    
        'Update On Hand Column
        mas.Cells(mr, 11).Value = mas.Cells(mr, 11).Value + inv.Cells(r, 6).Value
    
        'Update Added to Inventory Column
        inv.Cells(r, 8).Value = "Yes"
        
        mr = 2
        r = r + 1
    Loop

'

End Sub
Sub countInventory()

Dim r As Long
Dim mr As Long
Dim count As Worksheet
Dim master As Worksheet

r = 2
mr = 0
Set count = Worksheets("Count")
Set master = Worksheets("MasterInventory")

'Check Master Inventory and auto archive if neccessary
Do While r < 1151
    If master.Cells(r, 11) > 0 Then
        mr = mr + 1
        'MsgBox ("r = " & r & " mr = " & mr)
    End If
    r = r + 1
Loop

If mr > 0 Then
    ArchiveInventory
End If

'Copy and paste date
    count.Cells(1, 10).Copy
    master.Activate
    master.Cells(1, 13).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

'Extract data from count and input into Master Inventory
r = 2
mr = 2
Dim ItNo As Integer
Do While count.Cells(r, 1) <> ""
    ItNo = count.Cells(r, 1).Value
    Do While master.Cells(mr, 1) <> ItNo
        master.Cells(mr, 11) = count.Cells(r, 6)
        mr = mr + 1
    Loop
    r = r + 1
Loop
    
End Sub
Sub countSales()
'countSalesMacro

Dim r As Long
Dim mr As Long
Dim count As Worksheet
Dim sales As Worksheet

r = 2
mr = 3
Set count = Worksheets("Count")
Set sales = Worksheets("Sales")

'Check sales sheet and auto archive if neccessary
If sales.Range("D3") <> "" Then
    ArchiveSales
End If

'Find Last Row in Sales
Do While sales.Cells(mr, 4) > 0
    mr = mr + 1
Loop

'Copy and paste date
    count.Cells(1, 10).Copy
    sales.Activate
    sales.Cells(1, 6).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

'Check first row of count
If count.Cells(r, 1) = "" Then
    MsgBox ("There is no record in the first row of the count sheet.  If records exist, re-sort the sheet.")
    count.Activate
    Exit Sub
End If

'Find differences in inventory count and add information to Sales
Do While count.Cells(r, 1) <> ""
    If count.Cells(r, 8) <> "--" Then
        sales.Cells(mr, 4) = count.Cells(r, 1)
        sales.Cells(mr, 5) = count.Cells(r, 8)
        mr = mr + 1
        r = r + 1
    Else
        r = r + 1
    End If
Loop

'

End Sub
Sub ParAddedSort()

'After adding new pars, sorts the count sheet in order
Range("A1:H1150").Select
    Selection.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub
Sub ArchiveSales()

Dim r As Long
r = 3
Sheets("Sales").Activate
'Move historical date over to insert new data
    Columns("J:L").Copy
    Columns("J:L").Insert Shift:=xlToRight
    Application.CutCopyMode = False
'Copy and paste date
    Sheets("Sales").Cells(1, 6).Copy
    Sheets("Sales").Cells(1, 15).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
'Enter headers into historical data
    Sheets("Sales").Cells(1, 13) = Sheets("Sales").Cells(1, 4)
    Sheets("Sales").Cells(2, 13) = Sheets("Sales").Cells(2, 4)
    Sheets("Sales").Cells(2, 14) = Sheets("Sales").Cells(2, 5)
    Sheets("Sales").Cells(2, 15) = Sheets("Sales").Cells(2, 6)
    
'Grab sales total
    tot = Sheets("Sales").Cells(5, 1)
    
'Enter data into history and clear data from report
    Do While Sheets("Sales").Cells(r, 4) > 0
        Sheets("Sales").Cells(r, 13) = Sheets("Sales").Cells(r, 4)
        Sheets("Sales").Cells(r, 14) = Sheets("Sales").Cells(r, 5)
        Sheets("Sales").Cells(r, 15) = Sheets("Sales").Cells(r, 6)
        Sheets("Sales").Cells(r, 4).ClearContents
        Sheets("Sales").Cells(r, 5).ClearContents
        r = r + 1
    Loop
    
'Enter total sales into history and clear data from report
    r = r + 1
    Sheets("Sales").Cells(r, 13) = Sheets("Sales").Cells(4, 1)
    Sheets("Sales").Cells(r, 15) = tot
    
'Clear Date
    Sheets("Sales").Cells(1, 6).ClearContents
End Sub
Sub ArchiveInventory()

Dim cnt As Long
Dim r As Long
r = 2
cnt = 3
Sheets("MasterInventory").Activate
'Move historical date over to insert new data
    Columns("Q:R").Copy
    Columns("Q:R").Insert Shift:=xlToRight
    Application.CutCopyMode = False
'Copy and paste date
    Sheets("MasterInventory").Cells(1, 13).Copy
    Sheets("MasterInventory").Cells(1, 19).Select
    'ActiveSheet.Paste
    Application.CutCopyMode = False
'Enter headers into historical data
    Sheets("MasterInventory").Cells(2, 19) = Sheets("MasterInventory").Cells(1, 1)
    Sheets("MasterInventory").Cells(2, 20) = Sheets("MasterInventory").Cells(1, 11)
    
'Enter data into history and don't clear data from report
    Do While r < 1151
        If Sheets("MasterInventory").Cells(r, 11) > 0 Then
            Sheets("MasterInventory").Cells(cnt, 19) = Sheets("MasterInventory").Cells(r, 1)
            Sheets("MasterInventory").Cells(cnt, 20) = Sheets("MasterInventory").Cells(r, 11)
            'Sheets("MasterInventory").Cells(r, 11).ClearContents
            cnt = cnt + 1
        End If
        r = r + 1
    Loop
    
'Clear Date
    Sheets("MasterInventory").Cells(1, 13).ClearContents
End Sub





I am working on a sheet that will automatically calculate staff commissions based on sales and points. This formula needs to go in the cell which will display earned commission. Commission is only earned if three conditions are met - SalesA must be at least 8, SalesB must be at least 12, and the salesperson must have met Level 1 in points. If all three conditions are met, commission should be calculated based on the Level reached - there are 7 levels. These levels are located on a separate sheet called 'Office Setup'.

I was doing this with =SUM(IF... and nesting my IFs. This works fine when I'm only summing based on levels, but I would like it to also test the three conditions and that makes more than seven IFs.

If you could please help me write this formula... here is my best description of what I'm trying to do:

IF SalesA>=8 AND SalesB>=12 AND Points>='OfficeSetup!'Level1, THEN
-

IF Points>='OfficeSetup'!Level7, THEN (SalesA*Level7)+(SalesB*Level7),
IF Points>='OfficeSetup'!Level6, THEN (SalesA*Level6)+(SalesB*Level6),
IF Points>='OfficeSetup'!Level5, THEN (SalesA*Level5)+(SalesB*Level5)... all the way to Level1.

If Level1 is not met, it should display $0.00 because that is one of the three major conditions above.

To further complicate things, there are SalesC and SalesD categories that are not based on the level. As long as the three conditions are met, SalesC/D are awarded on a flat rate listed in the commission schedule... (SalesC*FlatRateC)+(SalesD*FlatRateD)

I have never worked with VBA and I'm not super experienced with Excel in general, but I have no problem with an advanced formula, especially if you can explain it to me. I've tried to set up the worksheet so that once this formula is in, it can be locked and all of the variables can be changed on a separate sheet without unlocking the formula. As long as it works, I never need to touch it again.

I would be happy to send you the sheet if it helps.

Thank you in advance, and I hope my explanation is clear enough.


Hi, I'm trying to copy and paste sales data from one spreadsheet to another, when I paste the sales it is pasting the formula from the original sheet and not the data, is there any way around this??