Copying Data

[thomas.szwed]

Hello,

Could anyone help me out on the following.

I would like to design a macro that once run copies four specified columns (A,B,G & H) of data from Sheet 1 and Sheet 2, and then pastes them into one sheet in a new workbook??

Anyone have any thoughts?

Thanks!

[Bob Phillips]

Do it in Excel with the macro recorder on, code automation.

[thomas.szwed]

OK, but two questions for you.

1. The data sizes in the Sheet 1 and Sheet 2 change all the time so how would i be able to paste them all into one new sheet without any spaces between records?

2. I need some code to automatically unprotect the workbook and sheets so i can copy the data as they are both protected at present.

Thanks

[Bob Phillips]

Post what you have got and we will take it from there. And a workbook?

[thomas.szwed]

Here is the code i manually recorded. Unfortuantely although i selected from both sheets it only copied the data from sheet 1 into the new workbook for some reason? I cant choose a row to copy sheet2's data seperately because i dont know how many rows sheet1's data will occupy because it will change regularly.

I also need some code in there to say unlock the workbook and sheets (password is password). Although i have taken off the protection in the attached workbook.

[vba]

Sub Copy()
'
' Copy Macro
' Macro recorded 05/03/2008 by Tom Swed
'
'
Range("A:A,B:B,G:G,H:H").Select
Range("H1").Activate
Sheets("Sheet2").Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Range("C9").Select
Sheets("Sheet2").Select
Range("B48").Select
Sheets("Sheet1").Select
ActiveWindow.SmallScroll Down:=-6

End Sub

[/vba]

Thanks!