copying cells to a new workbook

Is it actually possible to copy the information in any selected cell into a brand new workbook? I just want to store a few key bits of information from lots of different people's spreadsheets in a quick referance guide but dont know how to copy them across to a new worksheet. I guess there is a key command after you select the wanted cells i.e copyto "bla bla" range etc.

Many thanks,
steve

Steve,

Are you wanted to use VBA to accomplish this task. I move data from one spreadsheet to another all the time using VBA.

I also believe you need to give some more details about your problem. Are you just wanting to gather random bits and pieces of information or are you gathering the same information off of different spreadsheets and then creating a type of master spreadsheet. A few more details will help folks better understand exactly what you are trying to accomplish.

Jeff

my apologies for unclear posting. Yes basically I want to write an extra line of code on a macro that operates automatically so that certain key datails are sent to a brand new spreadsheet.

So at the moment the user looking at a blank sheet just presses ctrl+A to initiate the main macro, this runs a dialog box into which lots of details are entered.

These details are then automatically transfered onto a spreadsheet that calculates various formulas. This spredsheet is automatiacally saved in mhtml format and then on deciding whether to print this out or not on a seperate dialog box the user is returned to the blank start page for the next person. I would like to lift some of the cells from the spreadsheet and put them in a new workbook i.e name, age, results etc so that it is possible to compare at a glance some of the details of the different subjects.

Im just looking for that magic line of code where the copy destination into a different workbook is defined.

Thanks,
steve

Steve,

This should give you some information to work with. You basically define two variables as worksheets and then assign the two sheets you are wanting to work with to these varibles. Then you can move data back and forth between the two.

Below is some code that you can incorporate into what you are doing. Of course you will need to replace some of the generic names I am using with whatever you are working with.

Dim wsSheet1 As Worksheet
Dim wsSheet2 As Worksheet

Set wsSheet1 = Workbooks("Original.xls").Sheets("tabname")
Set wsSheet2 = Workbooks("New.xls").Sheets("tabname")

    Variable1 = wsSheet1.Cells(x, x).Value
    
    wsSheett2.Cells(x, x).value = wsSheet1.Cells(x, x).Value

Hope this can get you started.

Jeff

thanks alot for your help mate... appreciated.

Unfortunately no dice, whatever I do I seem to get a run time error 9 message... it cannot find the destination workbook I have created even when I give the full directory... i.e Workbooks("H:\MyDocuments\GFR\log.xls").

I dont understand it just refuses to look outside the actve workbook. Do I need to put this bit of code somewhere special or something?

Cheers,
Steve

Steve,

Is the other workbook open. Although I have heard that you can move information into a workbook that is not open, I have yet to find anyone that knows how to do it. Therefore, I always make sure the workbooks I am needing to use are open.

I use the statement below to see if a spreadsheet has been opened.

If WorkbookIsOpen("Pell Charts.xls") <> True Then
    Workbooks.Open filename:="C:\File Cabinet\Recon\Pell Charts.xls"
End If

The above statement uses the following function to see if a file is open and if it is not, it will open it.

Function WorkbookIsOpen(wbname) As Boolean

' Returns TRUE if the workbook is open
    
Dim x As Workbook
On Error Resume Next
Set x = Workbooks(wbname)
If Err = 0 Then
    WorkbookIsOpen = True
Else
    WorkbookIsOpen = False
End If

End Function

See if that helps at all. To give credit where credit is due, I believe John Walkenback wrote this function.

Jeff