Solved

Copy down cells, rewrite VBA

Posted on 2014-11-12
4
171 Views
Last Modified: 2014-11-12
I finally got this to work, based on some examples,  How do I rewrite this to remove the sheet tab reference?  I know that these lines can be consolidated into a couple lines and do the same thing.  Please advise and thanks.

'Populate column B, copies value in B2 of sheet declared down B
'For as many rows that are in columnG, then copy/paste special values
    Range("B2").Select
    With Sheets("Kentwood Order Entry Sheet") 'the sheet tab name in SALESORDERDOWNLOAD.XML
        .Range("B3:B" & .Cells(.Rows.Count, "G").End(xlUp).Row).Formula = "=(B2)"
    End With
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

Open in new window

0
Comment
Question by:RWayneH
  • 2
4 Comments
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 200 total points
ID: 40437806
Assuming that the sheet that you want the code to work in is your current one, delete lines 4 and 6 and rewrite line 5 as follows:

Range("B3:B" & Cells(activesheet.Rows.Count, "G").End(xlUp).Row).Formula = "=(B2)"
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40437809
You also don't need line 3.
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 300 total points
ID: 40437824
Here's an edited version of your code.  I presumed you were on the "Kentwood" sheet the entire time:
'Populate column B, copies value in B2 of sheet declared down B
'For as many rows that are in columnG, then copy/paste special values
    Sheets("Kentwood Order Entry Sheet").Activate
    Range("B3:B" & Cells(Rows.Count, "G").End(xlUp).Row).Formula = "=B2"

    Range("B2", Range("B2").End(xlDown)).Copy
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats

Open in new window


Regards,
-Glenn
0
 

Author Closing Comment

by:RWayneH
ID: 40437889
Thanks for the help.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Help with Journey Roadmap Graphic 11 48
Checkbox fires off a Statement 7 22
Macro 3 20
Macro Excel - Multiple If conditions 2 0
A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now