copying a sheet to another workbook.

[mudstuffin]

can anyone help with this one.

I have a workbook that has a hidden sheet with a large amount of data in it. The user inputs data into a sheet, which pulls the neccarsary data via the vlookup (thanks xlbo). What I want to be able to do, is when the user clicks a command button when they have finished their data input, I want only that sheet to be copied into another workbook which will only be used to back this data up. Is there a way to do it without opening the external workbook....?

If not, how should I go about opening the external workboook and keep it hidden/minimized, and then save & close that workbook...?

it has to just be that sheet, as this will only be a small file as opposed to the whole workbook which is quite large due to the hidden sheet with 72,000 lines in it.

The idea is to keep this out of the users sight & knowledge

n.b. I have had a crack at it, and have managed to copy the relevant sheet to the external file and also get it to rename the sheet to the system date & time when it copies it, but I cant get the external workbook to close, and I wwould also rather do it without opening the workbook, if possible.

Thanks in anticipation. Hope I've explained it clearly...!



mudstuffin

 

[iGrant]

Have you tried the 'Template wizard with data tracking' add-in? I think it would allow you to do what you want - it takes input from the user and, upon saving, sends the data only to either another excel sheet or to an access database.

I've successfully used this on a fairly complex application and worked out how excel structures the sheets (makes it easier than the slightly cumbersome method that excel automatically presents to you for setting up the sheets).

Let me know if you want more assistance.

 

[mudstuffin]

Thanks for your reply.

I've had a quick look at the template wizard as I have not done so previously. I'm not sure if I understood what it was asking for, but it seemed to want to me to select every cell that the data would be coming from. I have 26 cells to each that has data input into it, with up to 400 rows being input at a time. Would this process need me to select each cell, or am I being stoopid & not looking at it properly.?

I tried it with one row, and it ran quite slowly. Any suggestions, as I've never used this feature before (deinitely feels cumbersome) as always try to find the code to do things.

Thanks again,


mudstuffin.

 

[xlbo]

Hi - to stop flickering when opening wbs etc, use
Application.Screenupdating = false
(set it back to true at the ned of the sub)
To close and save changes to the wb you opened, use
Workbooks("Workbookname.xls&quot.close SaveChanges:= True
You won't be able to copy data to a workbook without opening it first I'm afraid
HTH Rgds
~Geoff~

 

[mudstuffin]

Thanks for your reply geoff.

I have now managed to run a sub which firstly renames the sheet to the date, time & username, then copies that sheet to a backup workbook, saves it & then closes that workbook. However, I cant seem to return the control to the original workbook to continue. Any code typed in afer this doesnt seem to do anything:-

Worksheets(bob).Copy Before:=Workbooks("DataBackUp.xls&quot.Sheets(1)

How can I regain control of the original workbook after the sheet is copies to the other WB...?


mudstuffin.

 

[xlbo]

Windows("Workbookname.xls&quot.activate
should do the trick
Rgds
~Geoff~

 

[mudstuffin]

Thanks Geoff,

That's what I thought. I have tried running this after the code to copy the sheet over, and nothing happens (seems to ignore anything after that). I have then tried placing it in the backup WB to run after it has saved the file straight after the sheet has been copied over from the original WB, but it errors with 'subscript out of range'.

Where should the 'Windows("Workbookname.xls&quot.activate' be placed to activate the original workbook..?

All I am really trying to do is to return the control to the original WB so that I can then maximize the window for the user to then continue working.

Here's the sub that copies over the sheet to the back-up WB. I thought i would be able to activate the original WB in this sub after the copy was done:-



Sub PasteItOver()

Workbooks.Open FileName:="DataBackUp.xls"
'opens up the backup file

ActiveWindow.WindowState = xlMinimized
' then minimizes it

Windows("Data.xls&quot.Activate
ActiveWindow.WindowState = xlMaximized
' then reactivates & maximizes the original. ok so far...

'
' the below selects the data range, pastes just
' the values to the next sheet, and then renames that
' sheet to the system date time & username.

Range("A2&quot.Select
Application.Goto Reference:="R2C1:R500C26"
Application.CutCopyMode = False
Selection.Copy
Worksheets(3).Select
Range("A2&quot.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A2&quot.Select
Sheets("InputSheet&quot.Select
Range("A3&quot.Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("A2&quot.Select
Worksheets(3).Select
Range("A2&quot.Select

Dim bob As String
Dim usern As String
usern = Application.UserName
bob = Range("a600&quot.Text
bob = bob + " " + usern
Worksheets(3).Select
Worksheets(3).Name = bob

' This copies the sheet to the backup WB.
Worksheets(bob).Copy Before:=Workbooks("DataBackUp.xls&quot.Sheets(1)

' nothing after the above works.....

Windows("Data.xls&quot.Activate
ActiveWindow.WindowState = xlMaximized

End Sub


Any ideas.? Forgive me if the code is a bit messy as I'm still learnin..!

Thanks


mudstuffin

 

[xlbo]

Very strange - I thought your code looked ok, so I recorded a copy sheet and got this:
Sheets("ptn full list&quot.Copy Before:=Workbooks( _
"MLR delegate list August 2001 to August 2002.xls&quot.Sheets(1)
Windows("Sharelist.xls&quot.Activate
Which is copying the sheet and then returning focus to the original window...and is the same as your code - are you sure nothing happens - copying worksheets can take a little time..... it may be that you just need to wait a short while.....
Rgds
~Geoff~

 

[mudstuffin]

Thanks Geoff.

Mine appears to be working now. Saying that, it still doesnt seem to acknowledge anything after the copying part. Just to test, I stuck in a quick MsgBox straight after it to see if it is running the code after that, and that didnt work. I did put some code in the backup workbook, but not sure whether this is triggering the original WB.

This is what I put:-


Private Sub Workbook_WindowActivate(ByVal Wn As Excel.Window)

ScreenUpdating = False

ActiveWindow.WindowState = xlMinimized
ThisWorkbook.Save

ThisWorkbook.Close

ActiveWindow.WindowState = xlMaximized

End Sub


I dont know whether the last proc triggered it or not...?

Thanks again,


mudstuffin.