put data from cells into other cells

[Keith Crooks]

Hi Guys,
I have posted a request and recieved an answer but it does not do what I
require although it does exactly what I asked :-(

I'll try and re-phrase

I have 5 cells per day
Mon B1:B5
Tues B6:B10
Wed B11:B15 etc etc.

Now on Monday I might only complete 2 jobs and Tuesday I might only complete
1 job and Wednesday I may complete 4 jobs, thus utilising cells
B1,B2,B6,B11,B12,B13,B14

Now I have to fill out another part of the spreadsheet E75:E100 with the
info in cells B1:B25 but with no spaces except one space between days

so E75,E76 would contain mondays stuff
E78 Tuesdays
E80,E81,E82,E83 Wednesday etc

But because jobs completed in anyone day varies between 1 and 5 i'm not sure
what I need to do.

many thanks in advance and thanks to DMEXCEL for the rapid response.

Hopefully this is clearer

Keith

 

[L. Howard Kittle]

Hi Keith,

Try something like this maybe. You will need to name the days five cells
mon, tue, wed etc. Assign to a button named "Week Update" or whatever.
Change F100 to whatever column suits you.

Sub ListJobs()
Dim mon As Range
Dim tue As Range
Dim wed As Range
Dim thur As Range
Dim fri As Range

Range("mon").Copy Range("F100").End(xlUp).Offset(1, 0)
Range("tue").Copy Range("F100").End(xlUp).Offset(2, 0)
Range("wed").Copy Range("F100").End(xlUp).Offset(2, 0)
Range("thur").Copy Range("F100").End(xlUp).Offset(2, 0)
Range("fri").Copy Range("F100").End(xlUp).Offset(2, 0)

End Sub

HTH
Regards,
Howard

 

[Keith Crooks]

Hi Thanks for the quick response, could you tell me how to name the days 5
cells please,

regards

Keith

 

[L. Howard Kittle]

Hi Keith,

Select the five cells for Monday. Click in the Name Box (the white space
just above column A, will have the cell address of the 'white' cell of your
selection). When clicked on, you will see the box go into an edit mode (the
address will be blue highlighted and moved to the left). Type in your name
and hit Enter. Do the same for all the other 5 day groups.

Now, if you click on the down arrow just to the right of the box, you get a
drop down of all "named ranges" which these are. Click on one and it will
select that range on the sheet. Perhaps do each one this way and verify it
is the correct range.

If you did not know how to name a range, I will assume you are fairly new to
Excel so will offer what to do with the code I offered.

Copy the macro and then right click the sheet tab. Select 'view code'.
Paste the code in the large white screen. Click on the Excel logo in the
upper left of the tool bar to return to the worksheet, of do an ALT--TAB to
do the same thing. Alt-Tab will 'scroll across any other programs you have
open, so it may take 2 or 3 to get to the sheet icon.

To run the macro, you can assign it to a button from the Forms tool bar or
Under Tools > Macro > Select that macro(if only one it will be the default
and already selected) > Run.

HTH
Regards,
Howard