PDA

View Full Version : Copy WS to a Named WS values only



mike31z
07-25-2011, 06:43 PM
I need a code to copy the values from cell A1:GJ1 (this is the header row) the bottom right. The number of rows changes numerous times daily.
The source file is names :SSco.xls, the only tab in the file is named; SSco

The destination file is named: evt11.xls the worksheet tab is names ssco already created.

The formating of the destination tab is not a issue except the header row should be bold.

Once I get the source data into a new file/workbook tab I can then sort as needed.

If i just " if(iserror(vlookup xxx" then I get unnecessary lines that causes sorting errors.

The SSco.xls file is created by a paradox file generator that the original source is called : ssco.DB

I can attach a small sample file of the ssco.xls if needed.

Kenneth Hobs
07-25-2011, 07:29 PM
Yes, an example of the xls and the DB file is the best way to help you.

mike31z
07-26-2011, 05:31 AM
I have attached a small version of SSco.xls with columns out to GJ and 15 rows of data. all cell content is data no formulas. Some times the row count gets into the 800 must time its under 100.

what I need is to copy the Worksheet SSco in the SSco.xls into a new xls file named: Evt1.xls, with the worksheet named SSco

Thanks for looking

Mike In wisconsin

CatDaddy
07-26-2011, 10:27 AM
Dim wb As Workbook

Worksheets("SSco").Copy
Set wb = ActiveWorkbook
wb.SaveAs "Evt1.xls"
wb.Close

mike31z
07-26-2011, 06:16 PM
CatDaddy I can get your code to run if I have both SSco.xls and Evt1.xls open it create anew (3rd) workbook.

with the following code I can import the ssco worksheet from the ssco.xls file to a new work sheet within workbook Evt1.xls
Windows("SSco.xls").Activate
Sheets("SSco").Select
Sheets("SSco").Copy Before:=Workbooks("EVT1.xls").Sheets(1)
Cells.Select
Selection.Interior.ColorIndex = xlNone
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("B3").Select

The color items removes all the color from the copy.

The problem with my code is IF I run it more then once a new SSco worksheet is created. I need the current ssco worksheet(tab) to be over written.

Thanks for looking

Mike In Wisconsin

CatDaddy
07-27-2011, 10:01 AM
why not just overwrite the file?

mike31z
07-27-2011, 11:30 AM
I don't know how to over write the file.

Mike in Wisconsin

CatDaddy
07-27-2011, 12:01 PM
Applications.Workbooks("EVT1.xlsx").Close True
True resaves to same workbook, overwriting it

mike31z
07-28-2011, 05:02 PM
Where do I insert Your 1 line application code at?

Mike In wisconsin