Hello,

Is there an other solution to copy different columns from 1 sheet ("test")
to 2 sheet ("copy_sheet"). I thought to search just columns which have some
data and than copy them to the other sheet ("copy_sheet").

I have some worksheets (30) and I need to go through them and copy it to the
"copy_sheet" just columns which have some data some of them are empty.

I have done some work witch the code below and it works fine but it is not
flexibel if I change something in the sheet so I have to chang the numbers
also in the makro.

Sub CopyFromOtherSheet()
Sheets("test").Activate

Range("A3:M5").Copy Worksheets("copy_sheet").Range("A3")
Range("A9:M9").Copy Worksheets("copy_sheet").Range("A6")
Range("A12:M12").Copy Worksheets("copy_sheet").Range("A7")
Range("A25:M25").Copy Worksheets("copy_sheet").Range("A8")
Range("A28:M28").Copy Worksheets("copy_sheet").Range("A9")
Range("A33:M33").Copy Worksheets("copy_sheet").Range("A10")
Range("A38:M38").Copy Worksheets("copy_sheet").Range("A11")
Range("A43:M43").Copy Worksheets("copy_sheet").Range("A12")
Range("A48:M48").Copy Worksheets("copy_sheet").Range("A13")
Range("A53:M53").Copy Worksheets("copy_sheet").Range("A14")
Range("A58:M58").Copy Worksheets("copy_sheet").Range("A15")
Range("A63:M63").Copy Worksheets("copy_sheet").Range("A16")

End Sub

Thanks in advance!!

You could give each of those ranges a nice name (insert|Name|define).

Then if you insert/delete columns or rows, the names will still point at the
ranges (as long as you don't destroy that named range).

So

Sub CopyFromOtherSheet()
with Sheets("test")
.Range("copy_001").Copy _
destination:=Worksheets("copy_sheet").Range("paste _001")
....






"p. panter" wrote:

Hello,

Is there an other solution to copy different columns from 1 sheet ("test")
to 2 sheet ("copy_sheet"). I thought to search just columns which have some
data and than copy them to the other sheet ("copy_sheet").

I have some worksheets (30) and I need to go through them and copy it to the
"copy_sheet" just columns which have some data some of them are empty.

I have done some work witch the code below and it works fine but it is not
flexibel if I change something in the sheet so I have to chang the numbers
also in the makro.

Sub CopyFromOtherSheet()
Sheets("test").Activate

Range("A3:M5").Copy Worksheets("copy_sheet").Range("A3")
Range("A9:M9").Copy Worksheets("copy_sheet").Range("A6")
Range("A12:M12").Copy Worksheets("copy_sheet").Range("A7")
Range("A25:M25").Copy Worksheets("copy_sheet").Range("A8")
Range("A28:M28").Copy Worksheets("copy_sheet").Range("A9")
Range("A33:M33").Copy Worksheets("copy_sheet").Range("A10")
Range("A38:M38").Copy Worksheets("copy_sheet").Range("A11")
Range("A43:M43").Copy Worksheets("copy_sheet").Range("A12")
Range("A48:M48").Copy Worksheets("copy_sheet").Range("A13")
Range("A53:M53").Copy Worksheets("copy_sheet").Range("A14")
Range("A58:M58").Copy Worksheets("copy_sheet").Range("A15")
Range("A63:M63").Copy Worksheets("copy_sheet").Range("A16")

End Sub

Thanks in advance!!

--

Dave Peterson