Solved Recording macro in excel 2007

June 9, 2014 at 14:29:13
Specs: Windows 7
I am having a problem I cannot resolve when recording macro in Excel 2007. I am trying to record macro in a file which has the following:
1. Sheet one has several columns headed as suppliers code, nominal a/c code date, gross amount, tax and net amount, etc. In each row below there are the actual codes, dates values etc for each transaction.

2. Sheet 2 has several columns but the main ones for my purposes are headed "supplier code" and "supplier name". Below in each row is the supplier code no. and the respective supplier name in the adjacent cell

3. Sheet 3 is similar to Sheet 2 but it has details of all the nominal accounts instead of supplier accounts.

The data in Sheets 1, 2 and 3 have been copied from 3 separate files and included in 1 file mentioned above with 3 worksheets.

I now start to record macro and I tidy up sheet 1 and insert new columns where required and unmerge cells and do other stuff and by using the VLOOKUP Function I am able to state the respective Supplier name and Nominal account name in each row which is what I want to achieve. I save this file as a macro enabled Excel file. My problem is when I clear the contents in Sheet 1 and run the Macro again I can see the cursor moving everywhere but most of the original data that was there gets lost somehow.

Also I want to run this routine regularly every month by updating the data in sheet 1. How can I get the macros to give me the correct layout that I have achieved previously. The number of rows in subsequent month(s) could be more or less than the previous month so how can I record a macro that will sort the layout of the whole sheet irrespective of the number of rows in Sheet 1. Sorry for long question but I hope I have explained my problem clearly. I will greatly appreciate any help and guidance.


See More: Recording macro in excel 2007

Report •

✔ Best Answer
June 20, 2014 at 04:24:47
The bottom line is that the recorder is simply going to record the exact steps that you perform while the recorder is running. If you start the recorder before you copy the data from another file, it may help, it may not.

If the data is not in the exact same location, with the exact same format each time you run the macro, you may not get the same layout when it is pasted into your new sheet. Then, when the macro starts Cutting/Copying/Pasting, it simply going to perform those operations on the exact ranges it saw when you recorded it. If there is different data or a different layout in those ranges, you will not get the results you expect.

There are ways to have the macro find the exact data you want to Cut, etc. but that takes manual code writing. You wouldn't be able to record that.

One way to understand what the recorded code is doing is to single-step through it, watching your sheet as each instruction is executed. I suggest that you read the Single Stepping portion of this How-To. I think it will help you understand what I'm talking about.

http://www.computing.net/howtos/sho...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
June 11, 2014 at 10:06:15
First, it's hard for us to offer macro related help without actually seeing the macro you are using. Before posting the code, please click on the blue line at the end of this post and read the instructions on how to post VBA code so that it retains the formatting from the VBA editor.

Second, you should be aware that the macro recorder produces very bloated code with no built-in "intelligence". A recorded macro will, by definition, do nothing more than play back the exact steps you took while recording it. That is why you are seeing “the cursor moving everywhere”. The code is simply repeating the exact steps you performed while recording, regardless of whether or not there is any data to work on or if it’s the wrong data, etc. If the sheet you are running it against is not set up exactly the same as the original sheet, the steps performed by the macro will be exactly the same, but the results may be very different.

Typically, a recorded macro needs to be cleaned-up, customized, etc. in order to be used over and over again with consistent results.

I’m confused by this statement:

“…when I clear the contents in Sheet 1 and run the Macro again I can see the cursor moving everywhere but most of the original data that was there gets lost somehow.”

If you cleared the sheet, what “original data” is left to be lost?

First, you’ll need to post your code and then you’ll need to explain what steps you are taking in a little more detail.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
June 17, 2014 at 08:55:29
I started a new thread by mistake (not quite know how to reply and follow up thread)
but below is the Code for the Macro I have recorded. Also to clear a point in my initial thread: I have saved the file as a macro enabled Excel file. When I load the file again it will load correctly, in the exact layout as I had saved with all the data. Just to test it I will clear the contents in sheet 1 and run the macro again. This is when most of the data that was there when I had loaded the file is lost. I hope the macro codes/steps below is what you wanted to enable you to help me.
Thanks
xel-learner

Sub PurchaseDayBook()
'
' PurchaseDayBook Macro
' DETAILED LISTING OF PURCHASE DAY BOOK
'
' Keyboard Shortcut: Ctrl+Shift+P
'
    Range("A1:T117").Select
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("M31").Select
    Columns("P:P").EntireColumn.AutoFit
    Columns("Q:Q").EntireColumn.AutoFit
    Columns("B:B").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Columns("E:E").EntireColumn.AutoFit
    ActiveWindow.SmallScroll Down:=-6
    Range("E4:E9").Select
    Selection.Cut
    Range("D4").Select
    ActiveSheet.Paste
    Range("I9").Select
    ActiveWindow.SmallScroll Down:=-12
    Columns("E:E").Select
    Selection.Delete Shift:=xlToLeft
    Range("J7").Select
    Columns("F:F").ColumnWidth = 6.43
    Columns("D:D").EntireColumn.AutoFit
    Columns("E:E").EntireColumn.AutoFit
    Columns("F:F").EntireColumn.AutoFit
    Range("F10").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveCell.FormulaR1C1 = "Supplier Name"
    With ActiveCell.Characters(Start:=1, Length:=13).Font
        .Name = "Times New Roman"
        .FontStyle = "Bold"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleSingle
        .ColorIndex = 1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Range("E10:F10").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("H:H").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("J6").Select
    Columns("H:H").ColumnWidth = 10.29
    Range("H10").Select
    ActiveCell.FormulaR1C1 = "A/C Name"
    Range("H10").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("F11").Select
    ActiveWindow.SmallScroll Down:=-6
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-1]:R[90]C[-1],Sheet1!R[-5]C[-5]:R[410]C[-4],2,FALSE)"
    ActiveWindow.SmallScroll Down:=-18
    Range("F11").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-1]:R[90]C[-1],Sheet1!R6C1:R421C2,2,FALSE)"
    Range("F11").Select
    Selection.Copy
    Range("F11:F95").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("H11").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-1]:R[85]C[-1],Sheet2!R[-5]C[-7]:R[166]C[-6],2,FALSE)"
    ActiveWindow.SmallScroll Down:=-18
    Range("H11").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-1]:R[85]C[-1],Sheet2!R6C1:R177C2,2,FALSE)"
    Range("H11").Select
    Selection.Copy
    Range("H12:H95").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("F24").Select
    ActiveWindow.SmallScroll Down:=-30
    Columns("Q:Q").Select
    Range("L7").Select
    ActiveWindow.SmallScroll Down:=-48
    Range("Q1:Q7").Select
    Selection.Cut
    Range("P1").Select
    ActiveSheet.Paste
    Columns("R:R").Select
    Selection.Cut
    Columns("Q:Q").Select
    ActiveSheet.Paste
    Columns("S:S").Select
    Selection.Delete Shift:=xlToLeft
    Range("S9").Select
    ActiveWindow.SmallScroll Down:=0
    Columns("R:R").Select
    Selection.Delete Shift:=xlToLeft
    Range("S7").Select
    ActiveWindow.SmallScroll Down:=-30
    Range("A1").Select
End Sub


Report •

#3
June 18, 2014 at 19:37:04
Thank you for the code. As I noted in my previous post, recorded code is bloated and messy, with all sorts of extraneous instructions. For example, this section basically does nothing but Select some cells and scroll around the screen:

    Range("F24").Select
    ActiveWindow.SmallScroll Down:=-30
    Columns("Q:Q").Select
    Range("L7").Select
    ActiveWindow.SmallScroll Down:=-48
    

Absolutely useless from a macro point of view, since it accomplishes nothing. There are lots of instructions in your code that do similar things, i.e. nothing useful. Obviously, that's not your fault, the recorder simply recorded everything you did, and when you re-run it, it simply does it all again.

As for your issue, I need to begin with this disclaimer: Without seeing your actual workbook the best I can do is guess at what your problem is.

That said, here is what I believe to be the root cause of your problem:

"Just to test it I will clear the contents in sheet 1 and run the macro again."

There seems to be a lot of Cutting, Copying and Pasting going on in your code. If you've cleared the data, what is left for the code to Cut or Copy? What you are probably seeing when you say "I can see the cursor moving everywhere" is the code Cutting, Copying and Pasting a bunch of empty cells.

The code doesn't save any data and it doesn't know anything about data that used to be someplace. All it does is mimic the Selection of cells and mimic the Cut, Copy and Paste instructions that you performed when you recorded it. It will also resize/delete/insert the columns as instructed, but since they are empty, it won't look like it is really doing anything. Now, since you entered data in certain cells (e.g. A/C Name), and a VLOOKUP formula in other cells, you will see them after the macro has been run because that data entry was recorded when you recorded the code:

  Range("H10").Select
    ActiveCell.FormulaR1C1 = "A/C Name"

********* Side Note Begins *********

BTW, as an example of what I meant by the "bloated code" that the recorder produces, if you were to write that instruction "manually" it would simply be:

  Range("H10") = "A/C Name"

A Selection/Cut/Paste instruction that records like this...

    Range("F11").Select
      Selection.Copy
    Range("F11:F95").Select
      ActiveSheet.Paste

...can be reduced to this:

Range("F11").Copy Range("F11:F95")

There is rarely a need to actually Select a Range/Cell/Column/etc. in order to do something with it in VBA.

Imagine how much easier it would be to follow your code if wasn't as bloated as what the Recorder produces, but again, that's not your fault.


********* Side Note Ends *********

Anyway, as far as I can tell, your issue is that your code has no data to work on because you cleared the sheet. All that the code is doing is formatting a bunch of empty columns and Copying/Cutting/Pasting a bunch of empty cells.

You'll have to tell me if you think I'm right, because, as I implied earlier, I can't see your worksheet from where I'm sitting.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
June 19, 2014 at 03:25:42
Thanks once again for your prompt response, DerbyDad03. I have read your reply and I think I know what you mean and why I am having the problem. When I clear Sheet 1 there is no data to be formatted/sorted etc. which is why "I am losing it". I will try out a few other routines and will let you know if I have managed to resolve the problem. One of routines, I think, is to start recording the macro at the point of data being copied into Sheet 1 from another file instead of starting to record after the data is already in Sheet 1.
Would this help in anyway?

xel-learner


Report •

#5
June 20, 2014 at 04:24:47
✔ Best Answer
The bottom line is that the recorder is simply going to record the exact steps that you perform while the recorder is running. If you start the recorder before you copy the data from another file, it may help, it may not.

If the data is not in the exact same location, with the exact same format each time you run the macro, you may not get the same layout when it is pasted into your new sheet. Then, when the macro starts Cutting/Copying/Pasting, it simply going to perform those operations on the exact ranges it saw when you recorded it. If there is different data or a different layout in those ranges, you will not get the results you expect.

There are ways to have the macro find the exact data you want to Cut, etc. but that takes manual code writing. You wouldn't be able to record that.

One way to understand what the recorded code is doing is to single-step through it, watching your sheet as each instruction is executed. I suggest that you read the Single Stepping portion of this How-To. I think it will help you understand what I'm talking about.

http://www.computing.net/howtos/sho...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question