Some copying questions

[rcbricker]

Hi guys have a new one for you. I have to create a spreadsheet that I will then email to appropriate firms in an attempt to have them show me the outstanding invoices. I have a copy of the sheet attached.

First worksheet is the summary page. There will be links of some sort in the A column that will jump you to the appropriate sheet in the workbook. (the first sheet has the link to show what i mean, i used a hyperlink). Column B is nothing important. Columns C - F is a copy of a totaled formula off each corresponding sheet. On the Summary sheet I changed the background color to matach that of the cells in the A.B. Larson sheet that need to be copied over.

What I need:

I need a macro that will let me insert the command to copy cells B5 - E5 on the corresponding worksheet to the C - F rows on the summary. So B5 - E5 on sheet 1(2) will copy into cells C - F that correspond to Arnett, Draper & Hagood row. Sheet 1 (3) will copy into cells in columns C - F for the next law firm and so on and so forth. easy copy and paste macro that i made reads:

Sub copycells3()
' copycells3 Macro
' Macro recorded 8/12/2004 by RBricker
Range("B5:E5").Select
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Summary").Select
    Range("C1").Select
    Selection.End(xlDown).Select
    Range("C6").Select
    ActiveSheet.Paste
End Sub

Obviously it will always paste in cell "C6" how do i get it to lower with the next row.

Next would be is it possible to take the names in Column A of the summary sheet and have them name each worksheet in succession? Sheet 1(2) would be named Arnett, Draper & Hagood then sheet would be Baluch, Gianfrancesco, Mathieu & Szerlag so on and so forth?

[andy_uk]

Once the sheet names match the list in column A of Summary, you won't need code ; you can use formulas, e.g.

=INDIRECT("'"&$A2&"'!B5")
=INDIRECT("'"&$A2&"'!C5")
=INDIRECT("'"&$A2&"'!D5")
=INDIRECT("'"&$A2&"'!E5")

in C2:F2 respectively, then copied down.

The real problem is you'll need to rethink - sheet names cannot exceed 31 characters.

Rgds,
Andy

[andy_uk]

Back again. OK, you'll have to work out some system of abbreviation for your sheet names. So you won't be able to ref the sheet names in the code, if you want to keep the Summary!A:A list as is (which I suspect you do).

In the first place, if the sheets are in the exact same order as the list, then

Sub Name_B2s()
'Dim Count As Integer?
Count = 1
For Each Sheet In ActiveWorkbook.Sheets
If Sheet.Name <> "Summary" Then
Sheet.Cells(2, 2) = Range("Summary!A1").Offset(Count, 0)
Count = Count + 1
End If
Next Sheet
End Sub

ought to poke the list values into B2 of each sheet, sequentially.

Then on the same principle,

Sub Copy_to_summary()
For Each Sheet In ActiveWorkbook.Sheets
If Sheet.Name <> "Summary" Then
Sheet.Cells(5, 2).Resize(1, 4).Copy _
Range("Summary!C65536").End(xlUp).Offset(1, 0)
End If
Next Sheet
End Sub

should copy each B5:E5 to C:F on Summary.

Test on a copy. These are pretty basic, I'm only a "proper VBA" beginner.

HTH,
Andy

[rcbricker]

Good work. I take it there is no code to have the value in B2 of each sheet to become the name of the sheet? I understand that there is a character limit. Is there no way to have the code automatically shorten the name so it can be used?

[andy_uk]

>> Is there no way to have the code automatically
>> shorten the name so it can be used?

Sure, you can set a crop limit with e.g.

Sub B2_is_SheetName()
ActiveSheet.Name = Left(Range("B2"), 31)
End Sub

But I doubt this will suit your needs. & I expect it's been superceded by your subsequent post. I'll go check.

Rgds,
Andy

[rcbricker]

nope worked great thanks for the help!

[rcbricker]

Found a problem in the copying code. I run the macro and it does everything that I want it to do with the exception of:

Instead of copying the date found in the worksheet "List" B2 into the M column from the first copied row down, it is copying the value into the M column from the first copied row and UP which over writes the dates that were previously copied. here is the code and I will attach the ractice sheet.

Sub CopyIt()
    Dim NextRow As Long
    Dim LastRow As Long
    NextRow = Sheets("Paid").Range("C65536").End(xlUp).Row + 1
    LastRow = Sheets("List").Range("C65536").End(xlUp).Row
Sheets("List").Range("B8:L" & LastRow).Copy
    With Sheets("Paid").Range("B" & NextRow)
        .PasteSpecial Paste:=xlPasteValues
    End With
    With Sheets("Paid")
        .Range("M" & NextRow & ":M" & Range("L65536").End(xlUp).Row).Value = _
        Sheets("List").Range("B2").Value
    End With
End Sub