Heading for a range?

[Jimmasterton]

Hi experts
I wonder if you can help me with this, it’s reslly frustrating.
I have 3 sheets
Sheet 1 has 2 command buttons, button 1 generates a non typical range of cells info on sheet 2. Button 2 copies the info from sheet 2 onto a summary page on sheet 3.

I want to use this button 5 times (mon to fri) and I’ve written the macro to copy everything, but I can’t figure out a way to simply put the words Monday, Tuesday etc above each copied range!

 

[jproffer]

Without knowing your ranges, etc all we can really do is speak generally, but generally this will enter the day of the week in Cell A1. You would want to be more specific...such as adding the worksheet name, maybe the workbook name. But this should get you started.

Range("A1").Value = Format(Weekday(Now()), "dddd")

 

[Jimmasterton]

Hi jproffer
Thank you for your reply, the problem I have is that I don’t know the range size that copies over after the first day.

So let’s say I have a button on the first page that will generate 5 different ranges mon to fri, I hit button the button once, that’s fine, the word Monday can go in cell A1. But because I don’t always know the size of the ranges I can’t put ‘Tuesday’ in a fixed cell.

 

[jproffer]

Ok. Are you saying that sometimes you wont have all the days of the week...I.E.-maybe just Monday thru Thursday? Or do you mean that sometimes Monday will be more than one column wide?

If it's just Mon thru Thurs sometimes, for example, you could check for data in each column, row 2 (or 3 or whatever) and if it's empty, then don't put a day in row 1, otherwise do...maybe:

If Range("B2").Value <> "" Then Range("B1").Value = Format(Weekday(Now()), "dddd")

and so on for C2/C1, D2/D1, and E2/E1.

Are we headed the right direction or am I way off on what you need?

 

[Jimmasterton]

If I call the button ‘summary’ . So I hit summary button once, it copies a range from sheet 2 to sheet 3 . The range is A2:H16. I want ‘Monday’ in A1. Now I go back to summary button, click it again and it copies a range of A2:H10 to sheet 3. I want this range headed ‘Tuesday’. The problem is I don’t know what range Monday will be and so on for the next days. Monday’s range maybe 5 rows or it maybe 15 as will all the days. Apologies for my terrible explanations.

 

[jproffer]

So you want something like this in your summary sheet?

Book1
	A	B	C	D	E	F	G	H
1	Monday
2	mon data	mon data	mon data	mon data	mon data	mon data	mon data	mon data
3	mon data	mon data	mon data	mon data	mon data	mon data	mon data	mon data
4	mon data	mon data	mon data	mon data	mon data	mon data	mon data	mon data
5	mon data	mon data	mon data	mon data	mon data	mon data	mon data	mon data
6	mon data	mon data	mon data	mon data	mon data	mon data	mon data	mon data
7	mon data	mon data	mon data	mon data	mon data	mon data	mon data	mon data
8	mon data	mon data	mon data	mon data	mon data	mon data	mon data	mon data
9	mon data	mon data	mon data	mon data	mon data	mon data	mon data	mon data
10	mon data	mon data	mon data	mon data	mon data	mon data	mon data	mon data
11	mon data	mon data	mon data	mon data	mon data	mon data	mon data	mon data
12	Tuesday
13	tues data	tues data	tues data	tues data	tues data	tues data	tues data	tues data
14	tues data	tues data	tues data	tues data	tues data	tues data	tues data	tues data
15	tues data	tues data	tues data	tues data	tues data	tues data	tues data	tues data
16	tues data	tues data	tues data	tues data	tues data	tues data	tues data	tues data
17	Wednesday
18	wed data	wed data	wed data	wed data	wed data	wed data	wed data	wed data
19	wed data	wed data	wed data	wed data	wed data	wed data	wed data	wed data
20	wed data	wed data	wed data	wed data	wed data	wed data	wed data	wed data
21	wed data	wed data	wed data	wed data	wed data	wed data	wed data	wed data
22	wed data	wed data	wed data	wed data	wed data	wed data	wed data	wed data
23	wed data	wed data	wed data	wed data	wed data	wed data	wed data	wed data
24	wed data	wed data	wed data	wed data	wed data	wed data	wed data	wed data
25	wed data	wed data	wed data	wed data	wed data	wed data	wed data	wed data
Sheet1

How does your button know what day to copy if you're using the same button? Now()?? Can you post the code you have so far so we can continue where you started rather than re-inventing the wheel?

 

[jproffer]

This seems to do what I think you're wanting. See what you think. I was using "Summary" as the WS name for your summary sheet, and the full name of the day of the week for the other sheets..."Monday", "Tuesday", "Wednesday", etc.

Sub test()
Dim LRS As Long
Dim LR As Long
Dim LC As Long
Dim Hdr As String
Dim MySh As String

MySh = Format(Weekday(Now()), "dddd")

LRS = Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Row

With Worksheets(MySh)
    Hdr = .Name
    LR = .Range("A" & Rows.Count).End(xlUp).Row
        If LRS = 1 Then
         .Range("A1:H" & LR).Copy Sheets("Summary").Range("A" & LRS + 1)
            Worksheets("Summary").Range("A" & LRS).Value = Hdr
        Else
         .Range("A1:H" & LR).Copy Sheets("Summary").Range("A" & LRS + 2)
            Worksheets("Summary").Range("A" & LRS + 1).Value = Hdr
        End If
        
End With


End Sub

 

[Jimmasterton]

Thank you so much jproffer, I’m going to try this tomorrow. Here is what I’m using which I should have just put up first

Worksheets(“sheet2”).select
Range(A2:H20).select

(Although I wanted a more ‘active range’ rather than defining an actual range)

ApplicationCutCopyMode = False
Selection.Copy
Sheets(“Sheet3”).Select
Range(“A”& Rows.Count).End(xlUp).Offset(3).Select
ActiveSheet.Paate

 

[jproffer]

Ok, I kinda got ahead of you. If what I posted in post 7 doesn't work for you, we'll regroup and try again/start over or whatever it takes. In notice my sheet names are different than yours (which I kinda figured would be the case), but that's an easy fix if that's the only problem we have.

 

[Jimmasterton]

Hello jproffer, I’m having a bit of difficulty with this and getting a runtime error 9 at line;
With Worksheets(MySh)

Subscript out of range

I think it’s to do with my sheet names