Copying from a worksheet with a variable name

[Davoucci]

Ok, i have a workbook that allows the user to create copies of a
master worksheet with a unique name (March'04; 03/04; Mar.04;etc...).
I also have a summary worksheet in the same workbook that will have a
summary of the months on one sheet. What I need is a code for a macro
button to copy a range("a1:d1") from the first worksheet to the 2nd
last worksheet (as the worksheets contain random names), and place
them vertically (a1:d1; a2:d2; a3:d3; a4:d4;, etc...) in the summary
sheet. Thank you in advance.

 

[Nigel]

The following code will go to the first worksheet, and transpose the range
A11 in to the 2nd last worksheet range A1:A4
- is this what you are trying to do, your explanation of the destination and
ranges were hazy?
but hopefully it gives you a template to work from. The key point is the
use of the worksheet count method to identify the sheet you require.

Public Sub Test()
Worksheets(1).Activate
Range("A11").Copy
Worksheets(Sheets.Count - 1).Range("A1").PasteSpecial Paste:=xlPasteAll,
_
Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
End Sub

Cheers
Nigel

 

[Davoucci]

Thanks for your reply, that helped part of my problem, the other part
is that i have multiple sheets (whith erradic names) that i need to
copy the code from (copy from the 1st sheet to the 2nd last sheet),
and paste them into the last worksheet (summary) so that for each
worksheet it copies from the summary sheet will list it vertically.
eg:
Copy from sheet(1) A1:A4; Copy from sheet(2) A1:A4; Copy from sheet(3)
A1:A4.
Paste into summary sheet, A1:A4, B1:B4, C1:C4.

 

[Nigel]

OK, I now understand. But you do not want to transpose, you only want to
copy each row


Public Sub Test()

Dim i As Integer
For i = 1 To Sheets.Count - 1
Worksheets(i).Range("A11").Copy
Sheets(Sheets.Count).Select
Cells(i, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Next i
End Sub

Thanks for your reply, that helped part of my problem, the other part
is that i have multiple sheets (whith erradic names) that i need to
copy the code from (copy from the 1st sheet to the 2nd last sheet),
and paste them into the last worksheet (summary) so that for each
worksheet it copies from the summary sheet will list it vertically.
eg:
Copy from sheet(1) A1:A4; Copy from sheet(2) A1:A4; Copy from sheet(3)
A1:A4.
Paste into summary sheet, A1:A4, B1:B4, C1:C4.



"Nigel" <[email protected]> wrote in message

Encryption =---

 

[Nigel]

On reflection you might want to put the name of the worksheet into column A
and the the data copied into columns B to F, you then have a reference on
the summary sheet. Just a thought......

Public Sub Test()
Dim i As Integer
For i = 1 To Sheets.Count - 1
Worksheets(i).Range("A11").Copy
Sheets(Sheets.Count).Select
Cells(i, 1) = Worksheets(i).Name
Cells(i, 2).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Next i
End Sub


Cheers
Nigel

Thanks for your reply, that helped part of my problem, the other part
is that i have multiple sheets (whith erradic names) that i need to
copy the code from (copy from the 1st sheet to the 2nd last sheet),
and paste them into the last worksheet (summary) so that for each
worksheet it copies from the summary sheet will list it vertically.
eg:
Copy from sheet(1) A1:A4; Copy from sheet(2) A1:A4; Copy from sheet(3)
A1:A4.
Paste into summary sheet, A1:A4, B1:B4, C1:C4.



"Nigel" <[email protected]> wrote in message

Encryption =---