copy different columns from sheet to other sheet

[p. panter]

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]

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