Code for copying master worksheet that has refreshable data

[mozzie789]

Hi there

I have a worksheet that has data that changes when refreshed approx every minute. I've searched the forum and found a lot of examples of copying s'sheets within a workbook for "static" data - but can't find any code or examples of copying a s'sheet to another s'sheet within the workbook where the master s'sheet has refreshable data.

Can someone point me to a searchable term or thread within the forum that does the above?

In summary, I just want to keep the master ("master") sheet as is, but copy it to another s'sheet ("duplicate") within the workbook so that this new s'sheet "duplicate" has the refreshable data and in effect is exactly the same as the "master".

I'm doing this so I can run some formulas over the "duplicate" sheet and leave the "master" sheet untouched.

 

[Datsmart]

This code will clear worksheet "Sheet2", then copy "Sheet1" data to it.

Sub TestClearAndCopy()
'Assign variable names to Source and Destination Worksheets
    ss = "Sheet1"
    ds = "Sheet22"
'Clear old data from Destination sheet
    Sheets(ds).Cells.Clear
'Copy Source Sheet to Destination Sheet
    Sheets(ss).Cells.Copy Destination:=Sheets(ds).Range("A1")
End Sub

Change the sheet names in the code as needed.

As far as running "some formulas over the "duplicate" sheet", I would suggest using Advanced Filtering.
Good example of Advanced Filter steps here:
http://www.contextures.com/xladvfilter01.html

AdvancedFilter Video Demo here:
http://www.contextures.com/xlVideos04.html

Another good link with sample VBA code here:
http://www.meadinkent.co.uk/xlfilter.htm

Advanced Filter allows you to preset filter options.
It also allows you to filter by criteria in ways standard autofilter can not do. You can choose an export location in the dialog box or in VBA code.
Once you setup the code and criteria, you can use a macro button to copy specific data from your main sheet to a "Report" sheet. The original data remains unchanged and the data "Filtered" to another page can be used as desired.

 

[Dryver14]

ds = "sheet2" ? additional 2 by maistake.

 

[VoG]

You should stick to your original thread http://www.mrexcel.com/forum/showthread.php?t=533524 (see the forum rules).

 

[mozzie789]

Thanks Datsmart, great links and info.

I most surely am sticking to the original thread and info contained in it VoG. Your help in that thread was most valued and it is the code that you supplied that I will be applying (along with my more simplistic formulas) to the duplicate sheet.

Thanks again for everyone's help.