Solved

Copy down cells, rewrite VBA

Posted on 2014-11-12
4
180 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

688 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