PDA

View Full Version : [SOLVED] Copy from multiple sheets paste to new Sheet



austenr
06-13-2005, 09:50 AM
I need to copy data from sheet1 then sheet2 then sheet3 then create sheet4 and paste the data from sheets 1,2,3 on sheet4 in the order it was copied. At the end delete sheet4. Can anyone offer a suggestion? The data will not be the same size each time on each sheet.

Also, to keep from starting a new thread, is there a way to save a workbook as a zip file?

Thanks

:help

MWE
06-13-2005, 10:16 AM
I need to copy data from sheet1 then sheet2 then sheet3 then create sheet4 and paste the data from sheets 1,2,3 on sheet4 in the order it was copied. At the end delete sheet4. Can anyone offer a suggestion? The data will not be the same size each time on each sheet.

Also, to keep from starting a new thread, is there a way to save a workbook as a zip file?

Thanks

:help
I must admit that your need is a bit perplexing. You want to copy data from sheets 1, 2, and 3 to sheet 4 and then delete sheet 4? Perhaps you mean you wish to copy the data to sheet 4, perform some operations and then delete sheet 4.

Does not seem very difficult. My approach would be to:



create sheet4
define range for sheet1, copy and paste to sheet4
define range for sheet2, copy and paste to sheet4
define range for sheet3, copy and paste to sheet4
perform other operations
delete sheet4
As long as you can define some logic by which Sheet1, Sheet2 and Sheet3 ranges are defined, and some logic for pasting to sheet4, the macro should be fairly simple.

Have I missed something?


Re your second question about saving as a ZIP file. I do not believe that one can save an Excel file directly as a ZIP file. There are lots of utilities for ZIPping and UnZIPping files.

austenr
06-13-2005, 10:20 AM
As I stated I can do it if the range is the same everytime. However, the range will not be the same on any of the sheets each time. Hence my delima. Do you set a range farther than you need to for each sheet which if you do, you could end up copying and pasting a lot of empty space. Or is it better to try to detect the range progmatically for each sheet. As for question 2, I was fairly certain that there was not a way to do it but thought I would throw it out and see if anyone had an idea.

mdmackillop
06-13-2005, 10:55 AM
Needs some manual input, but you could try something like this attachment.
Re the zip file, a little out of my knowledge, but my guess would be most have a command line programme which you could run from a Shell command. You may need to perform a SaveCopyAs first, as the active file may not zip.

austenr
06-13-2005, 01:38 PM
To clear up any confusion as to what I want to do...Evaluate sheet1. Say the active cells are A1:H52. Copy that with a blank row at the bottom. Evaluate sheet2. Active cells are A1:C5, copy that with a blank row at the bottom. Evaluate sheet3. Range A1:G52. Copy that. Create sheet 4. first paste sheet1, then right under that sheet2 then three. Something I forgot to mention. All of this is in a routine that I found by DRJ in the KB that lets you email sheets and or workbooks from inside EXCEL which I want to modify to work as described. So in reality sheet 4 would end up being the body of the email after all sheets are pasted. HTH

johnske
06-13-2005, 01:55 PM
Hi Austen,

For the combining sheets bit you could also have a look at this kb entry from smozgur http://www.vbaexpress.com/kb/getarticle.php?kb_id=151 which I think is intended to do just that

Regards,
John

mdmackillop
06-13-2005, 01:56 PM
If your sheet bits are fixed try



Private Sub Copies()
Sheets.Add
ActiveSheet.Name = "Sheet4"
MyRanges = Array("Sheet1!A1:H52", _
"Sheet2!A1:C5", _
"Sheet3!A1:G52")
For i = 0 To 2
Range(MyRanges(i)).Copy
ActiveSheet.Paste
Cells(ActiveSheet.UsedRange.Rows.Count + 1, "A").Select
Next
End Sub

austenr
06-13-2005, 03:31 PM
Thanks everyone...Johnske, that is exactly what I wanted. You can mark this solved...