Advanced macro

Slightly complicated question - I'll explain as best as I can.

I have two sheets in a file, one is the weekly sales (all data on one line) 
and the other is the previous weeks sales which have been hard coded (1st 
week on line 1, 2nd week on line two...52nd week on line 52).

I want the weekly sales sheet to remain the same as it has links into 
another report. What I need is a macro to copy the line of data from the 
weekly sheet into the relevant weeks line on the previous weeks sheet.

I can only create macro's that take the data from the weekly sheet and 
enters it into a specified line in the other sheet. So I need something that 
can enter the data in line relevant to the week. I thought about creating a 
Combo Box with the dates and using the output of 1,2,3 etc as a guide for the 
macro to input the data into lines 1,2,3 etc of the previous week sheet?

Any help would be greatly appreciated.
0
Luke (96)
8/31/2005 12:12:04 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
412 Views

Similar Articles

[PageSpeed] 2

Do you have a date on the weekly sales line that can be used to determine the 
destination line in the previous week sales sheet?

"Luke" wrote:

> Slightly complicated question - I'll explain as best as I can.
> 
> I have two sheets in a file, one is the weekly sales (all data on one line) 
> and the other is the previous weeks sales which have been hard coded (1st 
> week on line 1, 2nd week on line two...52nd week on line 52).
> 
> I want the weekly sales sheet to remain the same as it has links into 
> another report. What I need is a macro to copy the line of data from the 
> weekly sheet into the relevant weeks line on the previous weeks sheet.
> 
> I can only create macro's that take the data from the weekly sheet and 
> enters it into a specified line in the other sheet. So I need something that 
> can enter the data in line relevant to the week. I thought about creating a 
> Combo Box with the dates and using the output of 1,2,3 etc as a guide for the 
> macro to input the data into lines 1,2,3 etc of the previous week sheet?
> 
> Any help would be greatly appreciated.
0
8/31/2005 1:06:03 PM
No date at present David, but I can add one in if that will make it work?

"David Hepner" wrote:

> Do you have a date on the weekly sales line that can be used to determine the 
> destination line in the previous week sales sheet?
> 
> "Luke" wrote:
> 
> > Slightly complicated question - I'll explain as best as I can.
> > 
> > I have two sheets in a file, one is the weekly sales (all data on one line) 
> > and the other is the previous weeks sales which have been hard coded (1st 
> > week on line 1, 2nd week on line two...52nd week on line 52).
> > 
> > I want the weekly sales sheet to remain the same as it has links into 
> > another report. What I need is a macro to copy the line of data from the 
> > weekly sheet into the relevant weeks line on the previous weeks sheet.
> > 
> > I can only create macro's that take the data from the weekly sheet and 
> > enters it into a specified line in the other sheet. So I need something that 
> > can enter the data in line relevant to the week. I thought about creating a 
> > Combo Box with the dates and using the output of 1,2,3 etc as a guide for the 
> > macro to input the data into lines 1,2,3 etc of the previous week sheet?
> > 
> > Any help would be greatly appreciated.
0
Luke (96)
8/31/2005 1:32:03 PM
Lets start with this to see if I am on the right track.

Here is some code that will ask you what row to copy the data to:
(You will need to change the sheet names and range refrences)

Sub Macro1()
    Dim RowNum As Integer
    
    'Copies data
    'Change range references here
    Range("A2:M2").Select
    Selection.Copy
    Sheets("PreviousWeekSales").Select
    'Pastes values
    RowNum = InputBox("Enter destination row number.")
   'Change range references here
    Range("A" & RowNum & ":M" & RowNum).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, 
SkipBlanks _
        :=False, Transpose:=False
        
    'Returns to main sheet
    Sheets("WeeklySales").Select
    Range("A3").Select
    Application.CutCopyMode = False
End Sub





"Luke" wrote:

> No date at present David, but I can add one in if that will make it work?
> 
> "David Hepner" wrote:
> 
> > Do you have a date on the weekly sales line that can be used to determine the 
> > destination line in the previous week sales sheet?
> > 
> > "Luke" wrote:
> > 
> > > Slightly complicated question - I'll explain as best as I can.
> > > 
> > > I have two sheets in a file, one is the weekly sales (all data on one line) 
> > > and the other is the previous weeks sales which have been hard coded (1st 
> > > week on line 1, 2nd week on line two...52nd week on line 52).
> > > 
> > > I want the weekly sales sheet to remain the same as it has links into 
> > > another report. What I need is a macro to copy the line of data from the 
> > > weekly sheet into the relevant weeks line on the previous weeks sheet.
> > > 
> > > I can only create macro's that take the data from the weekly sheet and 
> > > enters it into a specified line in the other sheet. So I need something that 
> > > can enter the data in line relevant to the week. I thought about creating a 
> > > Combo Box with the dates and using the output of 1,2,3 etc as a guide for the 
> > > macro to input the data into lines 1,2,3 etc of the previous week sheet?
> > > 
> > > Any help would be greatly appreciated.
0
8/31/2005 1:56:12 PM
David

That is exactly what I need.

Now is there anyway to automate which line the macro enters the data into 
rather than having the options box?

"David Hepner" wrote:

> Lets start with this to see if I am on the right track.
> 
> Here is some code that will ask you what row to copy the data to:
> (You will need to change the sheet names and range refrences)
> 
> Sub Macro1()
>     Dim RowNum As Integer
>     
>     'Copies data
>     'Change range references here
>     Range("A2:M2").Select
>     Selection.Copy
>     Sheets("PreviousWeekSales").Select
>     'Pastes values
>     RowNum = InputBox("Enter destination row number.")
>    'Change range references here
>     Range("A" & RowNum & ":M" & RowNum).Select
>     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, 
> SkipBlanks _
>         :=False, Transpose:=False
>         
>     'Returns to main sheet
>     Sheets("WeeklySales").Select
>     Range("A3").Select
>     Application.CutCopyMode = False
> End Sub
> 
> 
> 
> 
> 
> "Luke" wrote:
> 
> > No date at present David, but I can add one in if that will make it work?
> > 
> > "David Hepner" wrote:
> > 
> > > Do you have a date on the weekly sales line that can be used to determine the 
> > > destination line in the previous week sales sheet?
> > > 
> > > "Luke" wrote:
> > > 
> > > > Slightly complicated question - I'll explain as best as I can.
> > > > 
> > > > I have two sheets in a file, one is the weekly sales (all data on one line) 
> > > > and the other is the previous weeks sales which have been hard coded (1st 
> > > > week on line 1, 2nd week on line two...52nd week on line 52).
> > > > 
> > > > I want the weekly sales sheet to remain the same as it has links into 
> > > > another report. What I need is a macro to copy the line of data from the 
> > > > weekly sheet into the relevant weeks line on the previous weeks sheet.
> > > > 
> > > > I can only create macro's that take the data from the weekly sheet and 
> > > > enters it into a specified line in the other sheet. So I need something that 
> > > > can enter the data in line relevant to the week. I thought about creating a 
> > > > Combo Box with the dates and using the output of 1,2,3 etc as a guide for the 
> > > > macro to input the data into lines 1,2,3 etc of the previous week sheet?
> > > > 
> > > > Any help would be greatly appreciated.
0
Luke (96)
8/31/2005 2:17:12 PM
Try this:

Sub Macro1()
    Dim RowNum As Integer
    
    'Copies data
    Range("A2:M2").Select
    Selection.Copy
    Sheets("PreviousWeekSales").Select
    'Pastes values
   
    Range("A2").End(xlDown).Select
    RowNum = ActiveCell.Row + 1
        Range("A" & RowNum & ":M" & RowNum).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, 
SkipBlanks _
        :=False, Transpose:=False
        
    'Returns to main sheet
    Sheets("WeeklySales").Select
    Range("A3").Select
    Application.CutCopyMode = False
End Sub



"Luke" wrote:

> David
> 
> That is exactly what I need.
> 
> Now is there anyway to automate which line the macro enters the data into 
> rather than having the options box?
> 
> "David Hepner" wrote:
> 
> > Lets start with this to see if I am on the right track.
> > 
> > Here is some code that will ask you what row to copy the data to:
> > (You will need to change the sheet names and range refrences)
> > 
> > Sub Macro1()
> >     Dim RowNum As Integer
> >     
> >     'Copies data
> >     'Change range references here
> >     Range("A2:M2").Select
> >     Selection.Copy
> >     Sheets("PreviousWeekSales").Select
> >     'Pastes values
> >     RowNum = InputBox("Enter destination row number.")
> >    'Change range references here
> >     Range("A" & RowNum & ":M" & RowNum).Select
> >     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, 
> > SkipBlanks _
> >         :=False, Transpose:=False
> >         
> >     'Returns to main sheet
> >     Sheets("WeeklySales").Select
> >     Range("A3").Select
> >     Application.CutCopyMode = False
> > End Sub
> > 
> > 
> > 
> > 
> > 
> > "Luke" wrote:
> > 
> > > No date at present David, but I can add one in if that will make it work?
> > > 
> > > "David Hepner" wrote:
> > > 
> > > > Do you have a date on the weekly sales line that can be used to determine the 
> > > > destination line in the previous week sales sheet?
> > > > 
> > > > "Luke" wrote:
> > > > 
> > > > > Slightly complicated question - I'll explain as best as I can.
> > > > > 
> > > > > I have two sheets in a file, one is the weekly sales (all data on one line) 
> > > > > and the other is the previous weeks sales which have been hard coded (1st 
> > > > > week on line 1, 2nd week on line two...52nd week on line 52).
> > > > > 
> > > > > I want the weekly sales sheet to remain the same as it has links into 
> > > > > another report. What I need is a macro to copy the line of data from the 
> > > > > weekly sheet into the relevant weeks line on the previous weeks sheet.
> > > > > 
> > > > > I can only create macro's that take the data from the weekly sheet and 
> > > > > enters it into a specified line in the other sheet. So I need something that 
> > > > > can enter the data in line relevant to the week. I thought about creating a 
> > > > > Combo Box with the dates and using the output of 1,2,3 etc as a guide for the 
> > > > > macro to input the data into lines 1,2,3 etc of the previous week sheet?
> > > > > 
> > > > > Any help would be greatly appreciated.
0
8/31/2005 2:55:19 PM
Reply:

Similar Artilces:

Macros #35
Hi I run a daily download from another system, via a .txt file into Excel. Each day the WorkSheet has a different name ie A3_12_11_04 then tommorow it will be A3_13_11_04 etc to represent the date it was downloaded. I then have to create a macro and pull off some of of the data on a daily basis...this is where my problem arrisses. How can my Macro recognise the different Worksheet name on a daily basis? My 2 thoughts would be to get data from another open worksheet or be able to put a promt in my Maco to search the name of the Worksheet. Confused...I am!! Any Help would be welcome. ...

Advance Find in Activities Histories
I asked this a while back but the answer did not work, and perhaps it was that I was not clear in my description of the problem, so I will try again. I have just converted activities from 20,000 clients from Goldmine into CRM 1.2. Most of these contacts/accounts have numerous activities which we have converted to historical activities on the Activities/History Tab. When you open an Account, go to Activities, then the History tab, all the activities display. I can click on the Type title to sort them and use the page -> to move between the pages and pages of activities. In some cas...

Macro Question
I'v never had any dealings w/ macro before, so this is all new to me. I have a windows 98 computer that is running office 2000 and there is an excel macro on this pc that was created by someone years ago. I have to get this 98 machine of the network and replace it w/ a 2000/XP platform also running either office 2000/xp. So far i'v copied the macro and am able to run it, but its not gathering the correct info. It is collecting the correct info on the 98 machine, just not the one im replacing it w/. It looks like some of the fields are supposed to be populated w/ info in a text d...

Macro optimizing
How can i stop all the graphical displays while im activating a Macro to speed up the work, especially when it consist of transporting data through multiple workbooks? Hi Joseph, Turn off screen updating and calculation within the macro, and turn back on when finished. See the following two web pages. Proper, and other Text changes -- Use of SpecialCells http://www.mvps.org/dmcritchie/excel/proper.htm Slow Response and Memory Problems http://www.mvps.org/dmcritchie/excel/slowresp.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://...

Advanced Conditional Formatting and Changing Cell Colours
I have three basic (for programmers) conditional formatting problems to solve in Excel for which I require some help. These are the last 3 conditions I need to complete for the worksheet so any help will be really appreciated. They are as follows: 1. If cell Y2 is > 0 and cell Z2 = 0 I need to change the colour of cell Z2 2. If cell Y2 or cell AA2 > 0 and cells V2, W2, or X2 = the value "Z" I need to change the colour of the cell containing "Z" (V2, W2 or X2). If changing the colour of only the single cell containing "Z" cannot be done, I guess changing t...

How to express Between Queries in advanced find
Hi, I need to express queries such as: <entity>.<startDate> <= Today && <entity>.<endDate> >= Today Any ideas? Regards -- Dimitris ------=_NextPart_0001_087C1335 Content-Type: text/plain Content-Transfer-Encoding: 7bit <DFoukas@discussions.microsoft.com> wrote: > I need to express queries such as: > > <entity>.<startDate> <= Today && > <entity>.<endDate> >= Today Queries in Advanced Find are, by default, AND queries unless you change it to OR. So you'd just do this by putting in ...

Opening hyperlinks with macro??
Hi, im not too great at excel so was wondering if anyone out there could help me. I cant really say what this is actually for, but ill give you the main idea. Every day, i generate a long list of hyperlinks in excel which i have to open and check. Obviously as there are so many, it takes a long time to open one, check it, close it, open the next, etc, scrolling down all the time. I was wondering if there was any way, by use of macros or otherwise, that i could open all the links together automatically so i wouldnt have to keep going back to excel. Any help anyone can give me would be ve...

SumProduct not Working in a Macro
Can someone please help - this is driving me nuts. I am wanting to use SumProduct in a Macro but it does not compile. The reason is that I am writing a macro to delete duplicate rows based on all columns of data, in some cases up to 50 rows. A simple example to highlight what I am trying to do - Lets say columns A to G have data in them in and the data is in rows 2 - 10, headings are in row 1. Firstly I concatenate all the data across Columns A to G and place the result in Column H. I do this for all 10 rows (including the heading). The next thing I do is test column H in...

Slow VBA macro
Every time the following code runs and it reaches a blank cell it takes 2-3 seconds to update the cell with a zero: Do Until ActiveCell.Offset(1, -3) = "" ActiveCell.Offset(1, 0).Range("A1").Select Select Case ActiveCell Case Is = "" Selection.FormulaR1C1 = 0 End Select Loop I've tried Application.ScreenUpdating = False but the macro is still very slow. Any suggestions as to ho...

create a macro that finds certain words in doc then pste
i am looking to create a macro that finds certain words in a word doc then cuts and pastes them into an ecxell spreadsheet. What have you come up with so far? ted wrote: >i am looking to create a macro that finds certain words in a word doc then >cuts and pastes them into an ecxell spreadsheet. -- Gerry Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/word-programming/201003/1 ...

Advanced Optimization Options
are 'Advanced Optimization' Options Inherited? like I have a app in which I have set these options and in it I am using 3rd party control of which I don't have source code nor do i know if they are using any of these options. so if I set some advanced optimization options will those also apply to that control? "Abhishek" <user@server.com> wrote in message news:i5j779$n6h$1@speranza.aioe.org... > are 'Advanced Optimization' Options Inherited? No. "Abhishek" <user@server.com> wrote > are 'Advanced O...

Macro to Flag based on criteria
Hey All, I'm looking to write a Macro in Outlook 2003 that will flag messages red when they are 29 days old. I would also like to set something up that will delete messages that have a certain color flag when I hit a custom button. Has anyone done antyhing like this before? I'm pretty familiar with Excel VBA, but this is my first attempt in Outlook. Thanks for any help!! How about a search folder for messages older than 29 days? you could also use one for the color delete one - then select all and delete the messages in it. This is safer than using na automated system and doesn...

Macro to send email
What would the visual basic language be for a user of a form to send email to me from within the form? Please explain yourself more Tanya -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Tanya Towler" <ttowler@thetford.com> wrote in message news:OejgxvZkDHA.2616@TK2MSFTNGP11.phx.gbl... > What would the visual basic language be for a user of a form to send email > to me from within the form? > > > ...

saving macros excel 97
i have a user who is asking if he can save his macros and how? we are upgrading him to office xp 2002 thanks Macros are stored in workbooks. Upgrading doesn't remove any user files. On Thu, 5 Aug 2004 07:21:01 -0700, "Antz" <anonymous@discussions.microsoft.com> wrote: >i have a user who is asking if he can save his macros and >how? we are upgrading him to office xp 2002 > >thanks > ...

CRM should let me decouple conditional statements in Advanced Find
We have set up a number of saved searches in CRM 3.0 that users would like to use Save As to create a copy and then add search terms to. Unfortunately, CRM 3.0 does not allow you to decouple conditional statements once the search has been saved. In order to add an AND condition, this feature is required. -- Thanks, Justin ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the...

Which macro we used in the MessageMap for user defined function
Hi guys, how r u, can you please tell me which macro is to be used in the message map for user defined function, that function ID we declare in the resource.h file, actually we are using ON_COMMAND(USER_DEFINED_FUNC_ID, message handler function) but it is not working .(No any error) please tell me what should i used. I use ON_MESSAGE() with the same parameters and it works fine. The function should be prototyped like: RESULT CMainFrame::OnDisplayDialog(WPARAM wParam, LPARAM lParam); Tom <raghunandan_1081@yahoo.com> wrote in message news:1165583596.594505.307210@l12g2000cwl.google...

Sharing Macros
I have a macro to be released to a number of positions held not by the same person. This is to be run with a New Worksheet with data created from SAP, so therefore cannot be a Macro assigned to a Worksheet? Personal is out and "Assigned to a ToolBar" I do not think is the answer. Is there something I am missing about "How to Assign a Macro", Thanking you A How about using an Add-In. Check out http://www.fontstuff.com/vba/vbatut03.htm -- Jacob "enna49" wrote: > I have a macro to be released to a number of positions held not b...

Advanced Security Headaches
I am having so many problems with this and unfortunately, on version 8, it is almost impossible to setup a lot of classes using the standard GP user class setup without pulling your hair out (and can't get access to certain things like business alert setup etc). I created a brand new user class using user class setup with no restrictions. Assigned user to class. Ran receivables integration with no errors. I then modified the user class using adv security to remove all windows within tools > setup, routines and utilities. Re-ran the integration and now get error "Unknown GP...

Macro Button
I dragged a macro button onto the toolbar in my workbook. I just wante to use this macro in one workbook. When I opend up a new work book, th macro button was in that document as well? How do I get a macro butto on the tool bar to only appear in a specific workbook -- Message posted from http://www.ExcelForum.com You need to assign/unassign the macros to the buttons when the workbook is opened. Sub Auto_open() Toolbars("LogForm").Visible = True Toolbars("LogForm").ToolbarButtons(1).OnAction = "PhoneLogger" Toolbars("LogForm").ToolbarButto...

Christmas lights macro #2
Oops, there shouldn't be a blank in the 5th byte in the output. That i where it wrapped on the input screen -- pmolse ----------------------------------------------------------------------- pmolsen's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1511 View this thread: http://www.excelforum.com/showthread.php?threadid=26744 I think this works. It writes directly to a .txt file (overwriting if it exists!): Option Explicit Sub testme() Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim wks As Worksheet Dim myFileName As Strin...

Advanced Searching
Can you search through Purchase orders by item Can you search by more than just the start of the lookup code? (ie search for all things red?) ...

macro shortcut help
Hello I have a Macro set up in spreadsheets I have made for multiple people. I have set it up so they can paste the info needed in a worksheet and then use the shortcut ctr(a) to run the macro below to delete all rows with lab in it. Sub Find_LAB() Dim rng As Range Dim what As String what = "LAB" Do Set rng = ActiveSheet.UsedRange.Find(what) If rng Is Nothing Then Exit Do Else Rows(rng.Row).Delete End If Loop End Sub This worked well for a while but now I find that the shortcut is n...

Outlook 2000
Here is what i am doing. ' Right click on a mail folder, click "Advanced Find", enter some search words that you know will return results, click the "Find Now" button. It will produce some results. click the "Find Now" button" again. and there are no results. (empties previous results). I am using outlook 2000. ...

Macro giving me grief..
Hi, I am trying to write/record a Macro that at the press of a button ( have inserted a button at the end of each row) will select all th cells in the row before the button, cut them, delete the empty row tha they came from and them paste the selection in the next available blan row in sheet2 (the table in sheet 2 is exactly the same layout as i sheet 1). So far i have got: Sub click_to_complete() ' ' click_to_complete Macro ' Macro recorded 28/05/2004 by Me ' ' Range("B2:E2").Select Range("E2").Activate Selection.Cut Sheets("Sheet2").Select...

excel macros
Suggestions please for good Excel macro resources. Thanks, Clif Hi Clif as a starting pojnt http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Regards Frank Kabel Frankfurt, Germany Clif Davis wrote: > Suggestions please for good Excel macro resources. > > Thanks, Clif ...