Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


copy range to another sheet problem

Posted on 2016-11-10
Medium Priority
Last Modified: 2016-11-10
I have been using the below to copy data from one sheet to another but in my current spreadsheet it is not only copying the data in the range but is going right to the end of the spreadsheet. i.e. my range might be AG2 – AL40 but it copies from AG2 – AL1048576

Obviously the range will change daily so it will be AG2 – AL??

Sheets("Turnover").Range(Range("AG2"), Cells.SpecialCells(xlCellTypeLastCell)).Copy
Sheets("Consolidated").Range("D" & Cells.Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

Can an expert assist me on this please
Question by:Jagwarman
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
LVL 34

Accepted Solution

Norie earned 1000 total points
ID: 41882197
With Sheets("Turnover")
    .Range(.Range("AG2"), .Range("AL" & Rows.Count).End(xlUp)).Copy
End With

Sheets("Consolidated").Range("D" & Cells.Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

Open in new window

LVL 52

Assisted Solution

Rgonzo1971 earned 1000 total points
ID: 41882198

pls try
 Sheets("Turnover").Range(Range("AG2"), Cells(Rows.Count, "AL").End(xlUp)).Copy
 Sheets("Consolidated").Range("D" & Cells.Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
 Sheets("Turnover").Range(Range("AG2"), Cells(Rows.Count, "AG").End(xlUp).Offset(,5).Copy
 Sheets("Consolidated").Range("D" & Cells.Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

Open in new window


Author Closing Comment

ID: 41882219
Brilliant many thanks. Both equal But I am forced to select one as Best.
LVL 33

Expert Comment

by:Rob Henson
ID: 41882223
It looks like your used range has got messed up. This can happen for a number of reasons but is usually easily solved.

The above amendments to your code will ensure the correct area is copied but it is still worth clearing the Used Range setting otherwise it could affect file size and performance.

On the sheet where you are encountering the issue press End then Home. The cursor should go to the last cell, ie bottom right of the data. If it goes to row 1048576 or beyond the row expected that shows the used range is corrupt (strong word but not drastic really).

Manually put the cursor in column A and the row below where the data ends. Press and hold Shift and press End then Home. This will highlight the surplus rows below the data. Remove those rows by right clicking and choose Delete, select Option for Delete Entire row and click OK. Go back to the top of the sheet, Save and Close the file. Re-open and test to see if End then Home now goes to the last expected row.

Rob H

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

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