Solved

how to move data from one range to another without lossing values

Posted on 2012-03-25
10
377 Views
Last Modified: 2012-04-02
First of all, I am tracking data via a SQL database that loads data every minute.  I query this data and then create worksheets in Excel.  The data is then formulated to give me information in an alarm type setup.  A report must be drawn from this data every 24 hours.  I have the formulas on each sheet and then I copy them into a report sheet.  How do I move the updated data down the worksheet as each interval is updated.?  How do I maintain the current values of each cell as the data is moved?  Please see attached file.
test.xls
0
Comment
Question by:Angiehncock
  • 5
  • 4
10 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 37762556
If you want to copy the values from a range of data and paste it below existing data you can use paste special.

Worksheets("Sheet1").Range("A2:H20").Copy

Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).PasteSpecial xlPasteValues

Open in new window


If you want to paste in a specific location.
Worksheets("Sheet1").Range("A2:H20").Copy

Worksheets("Sheet2").Range("A2").PasteSpecial xlPasteValues

Open in new window


I know this doesn't really fit in with your workbook but it's hard to determine which range/worksheets to copy from/paste to.
0
 
LVL 6

Expert Comment

by:Tomislavj
ID: 37762562
well there is 'paste special' option in excel to maintain values, formulas, formatting, etc from original cell(s) when pasting. is this what you need?
0
 

Author Comment

by:Angiehncock
ID: 37762693
Thanks for your input.  The data that is copies and pasted into the worksheet labeled Bun Report, is copies from the other sheets.   The data in these cells is updated every minute.  I want to set up an if statement in vba to copy and paste this informtion if the formulas are true.  If they are true, what is currently in the first 20rows of the bun report sheet will move  down to the next 20 rows and the new data will take the first 20 rows.  The data will always be different, if I program it correctly.  Right now the data being moved to the next 2nd 20 rows is being repeated from what is currently there because the formulas are moving rather than the value of each cell.  This process will go on for a 24 hour period. I am created a report to so alarms at specific times.  I hope this will give you more information.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 33

Expert Comment

by:Norie
ID: 37762739
If what formulas are true?

None of the formulas in the other worksheets actually return Truw (or False).

Also what data would you copy, there's thousands of rows on the other worksheets.

Is it actually the data in columns E and F from each worksheet you want to copy rather than the 'raw' data in the first 4 columns?
0
 

Author Comment

by:Angiehncock
ID: 37764690
That is correct,, columns E and F contain the formulas to determine whether there is a condition or not.  I have copied over this data to the Bun Report sheet.  The raw data contains the date that is needed for a timestamp.  As the raw data updates, the formulated columns E & F change according.   If The cells in column F are true, showing information , then this is to be copied over to the Bun Report sheet.  Each time a condition comes true, I want to move the current data on the Bun Sheet down and then copy the new data at the top of this sheet.  When I tried to do this the current data  on the Bun sheet didn't maintain the values,  instead it took on the values of the new data.  I need to copy the values of the columns E and F rather than the formulas, over to Bun Sheet.  As stated above, using special paste will fix this.  Thank you for your help.
0
 

Author Comment

by:Angiehncock
ID: 37792901
imnorie,

This has been very helpful.  The  1st example of pasting  works well, other than I need to have the most current data on the top of the list.  As data is updated the most current needs to be placed on top.  The data that is in this spot will shift to the next spot down,  and so on.

Is there a simple code structure that will enable this to happen, or is it best to set up copy and past from the bottom up?
0
 
LVL 33

Expert Comment

by:Norie
ID: 37793043
To place the new data at the top you would need to insert rows/cells to accommodate it.

Then you can use the code I posted to copy the data to the appropriate place.
0
 

Author Comment

by:Angiehncock
ID: 37793047
Prior to the copy code, insertion code would be used to create white space?
0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 37793078
You always know where the data is going to be placed, in row 2, so you just use that (with the column name) with the code I posted earlier for copying to a specific location.

Since there's already data there you want to push it down by inserting cells before you copy/paste.

You'll also have to copy the formatting.

This is just an example for copying from the Bun Oven worksheet.

I've based the no of rows to copy and the range to copy on the data that's already in the Bun Oven, not sure if that's correct.

Anyway, here's the code.
    Worksheets("Bun Report").Range("A2:B19").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    
    Worksheets("Bun Oven").Range("E1:F8").Copy
    
    Worksheets("Bun Report").Range("A2").PasteSpecial xlPasteValues
    
    Worksheets("Bun Report").Range("A20:B37").Copy
    
    Worksheets("Bun Report").Range("A2:B19").PasteSpecial xlPasteFormats

Open in new window

0
 

Author Closing Comment

by:Angiehncock
ID: 37796062
Thanks you for your help.   This code works well for this application.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

832 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