Microsoft Recorder Needs to go on a Diet!

I have been attempting to get code to copy and paste cells, then move them to another sheet, and delete the original file that was copied.

I did this through Record Macro, and I want to cry...

If Microsoft thought about anything they didn't think about how to record macros in their own language. There is so much fat in it that I am having a hard time to figure it out what is actually needed.

Here is the code.

Option Explicit

Sub Test()
'
' Test Macro
'

'
    Selection.Copy
    ActiveCell.Offset(46, 39).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(-43, -39).Range("A1:K1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(43, 40).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(-40, -40).Range("A1:K1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(40, 41).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(-37, -41).Range("A1:K1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(37, 42).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(-34, -42).Range("A1:K5").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(34, 43).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(-46, -28).Range("A1:K1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(46, 29).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(-43, -29).Range("A1:K1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(43, 30).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(-40, -30).Range("A1:K1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(40, 31).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(-37, -31).Range("A1:K1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(37, 32).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(-32, -31).Range("A1:B1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(32, 32).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(-34, -28).Range("A1:F1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(34, 29).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(-32, -29).Range("A1:F1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(32, 30).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(-30, -30).Range("A1:F1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(30, 31).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(-25, -51).Range("A1:Z3").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(25, 52).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(-21, -52).Range("A1:Z3").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(21, 53).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(-17, -53).Range("A1:Z3").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(17, 54).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(-13, -54).Range("A1:Z3").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(13, 55).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(-8, -53).Range("A1:D1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(8, 54).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(-8, -46).Range("A1:E1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(8, 47).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(-8, -37).Range("A1:E1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(8, 38).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, -19).Range("A1:T1").Select
    Application.CutCopyMode = False
    Selection.Cut
    Application.CutCopyMode = False
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Cut
    Sheets("NCMR Data").Select
    ActiveCell.Offset(-7, -2).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(-2, 20).Range("A1").Select
    Selection.Copy
    ActiveCell.Offset(2, -21).Range("A1").Select
    ActiveSheet.Paste
End Sub

Ultimately I will be wanting the pasting to be done via the last row, but for now this will work... if I can figure out what in god's green earth Microsoft actually put together for me...

Can someone shed some light on what is going on? I'm about to re-write this code to actually make sense to a human being...

actually for the macro recorder that's pretty good. no scrolling and so on. it basically just recorded exactly what you did as you did it. do you have a specific issue?

Originally Posted by Cyberpawz

So as you can see the program did what it was meant to, but it never would work the way that it needs to.

that sounds like you're blaming the recorder for doing what it was meant to instead of something else that you hoped it might magically do. I don't see the connection to dieting.

Originally Posted by JosephP

that sounds like you're blaming the recorder for doing what it was meant to instead of something else that you hoped it might magically do. I don't see the connection to dieting.

Selection.Copy
    ActiveCell.Offset(46, 39).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

You are telling me you couldn't write a better line of code to do the same thing?

Originally Posted by Cyberpawz

Selection.Copy
    ActiveCell.Offset(46, 39).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

You are telling me you couldn't write a better line of code to do the same thing?

I'm pretty sure I didn't tell you that but that's not the point. I know what the purpose is, the recorder doesn't. you're basically blaming a cassette recorder for recording you going "erm" instead of just playing the Gettysburg address like you meant.

It records what you do. You select a cell, it records that. You scroll, it records that. It has no way of knowing what your ultimate purpose is so it simply records every step you take. The fact that you have it set to record relative references makes it a little more convoluted than need be but it ain't that bad really. obviously you disagree.

Originally Posted by Mordred

How is a recorder, which is meant to record your actions, know what you would consider to be fluff? If it wasn't for the macro recorder and this site (and other sites that I am not aloud to mention), I would still be useless regarding vba. Your code is understandable but you have recorded a lot of steps, which makes it troublesome to look at. Like I suggested in post #3, re-record smaller amounts of actions and you will have an easier time understanding what is going on. Imo, you have recorded so much at one time that even my eyes want to glaze over the procedure.

I did, and it still leaves a lot to like... Here is the code:

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("B11:L11").Select
    Selection.Copy
    Range("AO57").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

All I want to do is to copy a cell, and paste into a cell, but instead of copy and pasting the formula, I want to copy and paste the result.

Originally Posted by AlvaroSiza

...Continuing from above, it could look something like this:

Sub RngToRngValueExample()

Dim rngSource As Range
Dim lngColCount As Long

   Set rngSource = Range("B11:L11")
   lngColCount = rngSource.Columns.Count

   Range("AO57").Resize(1, lngColCount).Value = rngSource.Value

End Sub

No copying, no pasting...

Ok, but the point is that I want B11 to be the only thing copied over, Or is that what the re size does is take out all the other fields so it only copies over a single cell instead of multiple?

you don't wanna use 0 in a resize call. :-)

    Range("AO57").value = range("b11").value

Originally Posted by JosephP

    Range("AO57").value = range("b11").value

I figured that, but the question comes two fold, 1 can I have it choose multiple cells that don't coincide? and the resize part, this makes it so it pastes only one cell not multiple correct?

Originally Posted by AlvaroSiza

Incorrect. In your original syntax (which you have since rebutted as being incorrect because you only wanted one cell, not a range), you provided a range extending 11 columns within Row 11 ("B11:L11"). You also provided a single cell location for where you wanted the data to go ("AO57"). Joseph was pointing out to you that under a .value = .value approach, it is necessary to Resize (by using the Resize property) your destination range be the same size as your source range. Otherwise, you will error out.

Quickie on resize...

Assume you select Range("A1"). Now assume that you want everything 2 rows to the right, or the ultimate range of Range("A1:C1"). The Resize property allows you resize a base range to whatever dimensions you choose. Thus, the example above would be expressed as:

Range("A1").Resize(1, 3)

I think you are learning a few things from this thread beyond just the syntax.

1. People are here to help as long as you will help yourself. Provide as much detail as possible. Provide a sample workbook when possible.
2. You need to do some research. It is not our job to help you, we do so because we learned (and are learning) here and want to give back. Your original tone was not conducive to a constructive thread (Excel was broke before you ever posted).

I didn't say Excel was broken, I said the Microsoft's attempt at automated script design is broken... big difference.

If you are only assigning to one cell, there is no need to resize. if you want to do several discontiguous cells you'll need a loop. where are the cell values supposed to go?

Originally Posted by JosephP

If you are only assigning to one cell, there is no need to resize. if you want to do several discontiguous cells you'll need a loop. where are the cell values supposed to go?

Ok, here is the document. Or an example of one, this is old data so it's not up to date, nor is it relevant.

In any case If you go to the Internal NCMR, you'll see the JOB# box, it is a pull down menu. Select a job number, from there you will see fields self populate.

Fill in the document, gibberish if you will, the Added By is a pull down menu.

From there, I want all these fields to go into NCMR Data.

If you look at NCMR data, there is a number at the beginning, there is also one at the end on V2. It is an attempt to create a ID system for the list. The concept is that when the script is run, it will copy V2 to the last row and paste it into A#, from there it will copy all the fields in Internal NCMR and paste them in their corresponding columns. Originally I had it where it would paste into a row under the NCMR and then select, delete, and paste into the last row of NCMR Data, but I never got past the point of delete and paste.

If this make any sense to you, I'd appreciate some help.