MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

copy a range of cells to another sheet

  Asked By: Tye    Date: Mar 04    Category: MS Office    Views: 2247

I'm trying to copy a range of cells to another sheet which i have got
working with this code.

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False

'Holiday Monthly Detail'

With Sheets("April")
End With
Sheets("Print Balance").[b1].End(xlUp)(3).PasteSpecial

End Sub

The cells only contain characters but i want to copy specified values

So Range B7:AF7 may contain these values H,S,HD,DS,TR,ST,M,C,V but i
only want to copy H,S,HD.

Can anyone help me on this one please.



6 Answers Found

Answer #1    Answered By: Freya Brown     Answered On: Mar 04

How do you know which values  you want to copy?

Answer #2    Answered By: Dep Tran     Answered On: Mar 04

Anybody got any thoughts on this or can improve the code  i already

What i'm trying to do is collect the info so that it can be printed

It's a holiday/schedule control program but i only want to give
certain info to people when i print off.

I have all the months on individual sheets and on each sheet  there
are 20 people.

So i need to get info from the monthly sheets b7:af7 and paste them
into a new sheet for printing.

So copy  April B7:AF7 to Print Balance B3 but only copy "H","HD"
or "S".

Then May B7:AF7 to Print Balance B4
Then June B7:AF7 to Print Balance B5
Then July B7:AF7 to Print Balance B6
Then August B7:AF7 to Print Balance B7

and so on.

The way i have it now is to copy all the info but conditionally
format the info out by turning the Cell/Font white.

Answer #3    Answered By: Cesara Fernandez     Answered On: Mar 04

OK, first of all, turning the cell & font white will NOT keep it from printing.
Your printer doesn't print WHITE, so it'll default to black.

Second... It's VERY possible to just copy  certain cells.

You said "but only copy "H","HD" or "S""..
that has absolutely NO meaning for me.
Are you saying that you're wanting to copy rows from each of the sheets,
but only if they have a specific value in a specific column?
Is there a limit to the number of rows in each sheet  (I suspect that there is,
It's really fairly simple, but I would NOT recommend using copy/paste.
It's VERY CPU intensive.
If you use something like:
sheets("Balance").cells(ROWNUM,ColNo) = sheets(MonthNo).cells(r,c)
in a series of nested loops, it would run MUCH faster.

If you can give me some more info, or even send me the file, I can see what I
can do.

Answer #4    Answered By: Daimon Jones     Answered On: Mar 04

The cells  I'm copying from contain text only but i need to filter out certain

"H" signifies Holiday
"HD" signifies Half Day Holiday
"S" signifies Sickness

this is the only info i need to print but the other cells in the row may contain
other designations.

So any cell from B7:AF7 may contain other info but i need to copy  only H,HD,S.

At the moment this is done with command buttons.

Hope this makes it clearer.

If you need the file i will mail it to you.

Answer #5    Answered By: Aabirah Khan     Answered On: Mar 04

Also as an update

Changing the Cell Color and Font Color White works for me.

Prints out just fine.

Answer #6    Answered By: Utsav Shah     Answered On: Mar 04

ok... So, my guess is that since B-AF is 31 cells, they represent the day of the
your rows must represent... people?
so, say for January, you have a tab/sheet called "January"
with all of the people's names in column A, and someone places a H,HD,or S in
appropriate Column for that person/day.

Now, for the Summary page, you're wanting to copy  the cells  containing the H,HD,
or S to that
page... what else? Name? Month?
How many rows are on each monthly sheet? Variable number?

We do something similar, but instead of the sheets for each month, we use
sheets for each employee. that way, "my" sheet  is the annual attendance report.
We also use it for scheduling the vacation, then enter the hours taken once they
are used.
The summary page then shows how many people are scheduled to be off on a given

Didn't find what you were looking for? Find more on copy a range of cells to another sheet Or get search suggestion and latest updates.