worksheet references for a macro and VBA

[Guest]

I have a workbook with about 60 worksheets. I want to write a macro that
copies a column from each of these sheets and pastes it into a new sheet.
The only problem is the names of the worksheets will be changing frequently.
I know when I write macros they reference the actual sheet name and if this
changes the macro will no longer work. Is there anyway to reference the
sheet position, 1st sheet, 2nd sheet, etc. instead of the acual sheet names?

Thanks

Adam Bush

 

[Guest]

Assuming the last sheet is called "summary":


Sub adam_b()
For i = 1 To Sheets.Count - 1
Set r1 = Sheets(i).Range("A:A")
Set r2 = Sheets("summary").Cells(1, i)
r1.Copy r2
Next
End Sub

Of course you can pick any column.

 

[Guest]

How do I get the macro to paste the values over and not the formulae?

Thanks

Adam Bush

 

[Guest]

Gary,

I just realized I need to do this procedure with two columns. For every
sheet they will be colmuns B and J. They need to be pasted into the summary
sheet alternating. So the summary sheet would have B from the 1st sheet in
column 1, J from the 1st sheet in column 2, B from the second sheet in column
3, J from the second sheet in column 4, and so on. I would also need just
the values pasted over, not the formulae. Anything you can do to help would
be much appreciated.

Thanks

Adam Bush

 

[Guest]

Sub adam_b()
j = 1
For i = 1 To Sheets.Count - 1
Set r1 = Sheets(i).Range("B:B")
Set r2 = Sheets("summary").Cells(1, j)
r1.Copy
r2.PasteSpecial Paste:=xlPasteValues
j = j + 1
Set r1 = Sheets(i).Range("J:J")
Set r2 = Sheets("summary").Cells(1, j)
r1.Copy
r2.PasteSpecial Paste:=xlPasteValues
j = j + 1
Next
End Sub

New version
1. two columns
2. values, not formulas

 

[Guest]

Gary,

Thank you very much for all your help. Everything works perfectly.

Thanks Again

Adam Bush

 

[Max]

can i have the same kind of code updated but my requirement is as below

But i have the following requirement

I have say 12 worksheets for 12 months. Each worksheet has say 4 columns
"Activity"(in col A), "Start"(in col b), "Finish"(in col c), "Status"(in col
d)
each worksheet has a definite number of rows for data entry and cant extent
beyond these values(ex:A7 to A39 for "activity" similarly b7 to b39 for
"start"...)
I want to have a summary sheet with the same 4 columns "Activity"(col A),
"Start"(col b), "Finish"(col c), "Status"(col d)

I have 2 questions

1) whenever a data entry is made in any of the 12 worksheets (between rows 7
to 39 for A, B, C, D colms), the same values should automatically be copied
onto the summary sheet.

2) say for example 1st sheet has a project name "xyz CR" under "Activity",
and again to track for next month in 2nd sheet i enter the same project name
"xyz CR" under "Activity". This duplicate should not be entered in the
summary sheet.

Thanks in advance

Maxim