vb code help needed
 

Background:
I have 4 sheets in the workbook. One sheet is named TOTALS. The other 3
sheets will have different names that change each month.

Scenario:
On the three sheets that are not named totals there is data in cell C2. I
need some VB code that will goto the first sheet, copy the data from cell
C2, go back to the totals sheet, paste the data in cell A20, then goto the
second sheet, copy the data from C2, goto the totals sheet, paste the data
into A21, then goto the third sheet, copy the data from C2, goto the totals
sheet, paste the data into A22.
If the sheet names were always the same I could get it, but since the sheet
names will change each month, I'm stumped. I tried this but it includes the
totals sheet and therefore doesn't work. I dont know how to get it to just
get the data from the three sheets and not the totals sheet.

Dim objWorksheet As Worksheet
For Each objWorksheet In ActiveWorkbook.Worksheets
Range("C2").Select
Selection.Copy
Sheets("TOTALS").Select
If Range("A20") = "" Then
Range("A20").Select
ActiveSheet.Paste
ElseIf Range("A21") = "" Then
Range("A22").Select
ActiveSheet.Paste
ElseIf Range("A22") = "" Then
Range("A21").Select
ActiveSheet.Paste
End If
Next objWorksheet

Thanks,
Phil

vb code help needed
 

Opps typo in code, here is what I'm using that isn't working:

Dim objWorksheet As Worksheet
For Each objWorksheet In ActiveWorkbook.Worksheets
Range("C2").Select
Selection.Copy
Sheets("TOTALS").Select
If Range("A20") = "" Then
Range("A20").Select
ActiveSheet.Paste
ElseIf Range("A21") = "" Then
Range("A21").Select
ActiveSheet.Paste
ElseIf Range("A22") = "" Then
Range("A22").Select
ActiveSheet.Paste
End If
Next objWorksheet


"Phil" wrote in message
...
Background:
I have 4 sheets in the workbook. One sheet is named TOTALS. The other 3
sheets will have different names that change each month.

Scenario:
On the three sheets that are not named totals there is data in cell C2. I
need some VB code that will goto the first sheet, copy the data from cell
C2, go back to the totals sheet, paste the data in cell A20, then goto the
second sheet, copy the data from C2, goto the totals sheet, paste the data
into A21, then goto the third sheet, copy the data from C2, goto the
totals sheet, paste the data into A22.
If the sheet names were always the same I could get it, but since the
sheet names will change each month, I'm stumped. I tried this but it
includes the totals sheet and therefore doesn't work. I dont know how to
get it to just get the data from the three sheets and not the totals
sheet.

Dim objWorksheet As Worksheet
For Each objWorksheet In ActiveWorkbook.Worksheets
Range("C2").Select
Selection.Copy
Sheets("TOTALS").Select
If Range("A20") = "" Then
Range("A20").Select
ActiveSheet.Paste
ElseIf Range("A21") = "" Then
Range("A22").Select
ActiveSheet.Paste
ElseIf Range("A22") = "" Then
Range("A21").Select
ActiveSheet.Paste
End If
Next objWorksheet

Thanks,
Phil

vb code help needed
 

Dim iCtr as long
dim DestCell as Range

Set DestCell = worksheets("totals").range("A20")

for ictr = 1 to worksheets.count
if lcase(worksheets(ictr).name) = lcase("totals") then
'do nothing
else
destcell.value = worksheets(Ictr).range("c2").value
set destcell = destcell.offset(1,0)
end if
next ictr



Phil wrote:

Background:
I have 4 sheets in the workbook. One sheet is named TOTALS. The other 3
sheets will have different names that change each month.

Scenario:
On the three sheets that are not named totals there is data in cell C2. I
need some VB code that will goto the first sheet, copy the data from cell
C2, go back to the totals sheet, paste the data in cell A20, then goto the
second sheet, copy the data from C2, goto the totals sheet, paste the data
into A21, then goto the third sheet, copy the data from C2, goto the totals
sheet, paste the data into A22.
If the sheet names were always the same I could get it, but since the sheet
names will change each month, I'm stumped. I tried this but it includes the
totals sheet and therefore doesn't work. I dont know how to get it to just
get the data from the three sheets and not the totals sheet.

Dim objWorksheet As Worksheet
For Each objWorksheet In ActiveWorkbook.Worksheets
Range("C2").Select
Selection.Copy
Sheets("TOTALS").Select
If Range("A20") = "" Then
Range("A20").Select
ActiveSheet.Paste
ElseIf Range("A21") = "" Then
Range("A22").Select
ActiveSheet.Paste
ElseIf Range("A22") = "" Then
Range("A21").Select
ActiveSheet.Paste
End If
Next objWorksheet

Thanks,
Phil

--

Dave Peterson

vb code help needed
 

Thanks alot. Looks like what I need. I'll give this a try.
Phil

"Dave Peterson" wrote in message
...
Dim iCtr as long
dim DestCell as Range

Set DestCell = worksheets("totals").range("A20")

for ictr = 1 to worksheets.count
if lcase(worksheets(ictr).name) = lcase("totals") then
'do nothing
else
destcell.value = worksheets(Ictr).range("c2").value
set destcell = destcell.offset(1,0)
end if
next ictr



Phil wrote:

Background:
I have 4 sheets in the workbook. One sheet is named TOTALS. The other 3
sheets will have different names that change each month.

Scenario:
On the three sheets that are not named totals there is data in cell C2. I
need some VB code that will goto the first sheet, copy the data from cell
C2, go back to the totals sheet, paste the data in cell A20, then goto
the
second sheet, copy the data from C2, goto the totals sheet, paste the
data
into A21, then goto the third sheet, copy the data from C2, goto the
totals
sheet, paste the data into A22.
If the sheet names were always the same I could get it, but since the
sheet
names will change each month, I'm stumped. I tried this but it includes
the
totals sheet and therefore doesn't work. I dont know how to get it to
just
get the data from the three sheets and not the totals sheet.

Dim objWorksheet As Worksheet
For Each objWorksheet In ActiveWorkbook.Worksheets
Range("C2").Select
Selection.Copy
Sheets("TOTALS").Select
If Range("A20") = "" Then
Range("A20").Select
ActiveSheet.Paste
ElseIf Range("A21") = "" Then
Range("A22").Select
ActiveSheet.Paste
ElseIf Range("A22") = "" Then
Range("A21").Select
ActiveSheet.Paste
End If
Next objWorksheet

Thanks,
Phil

--

Dave Peterson

vb code help needed
 

Worked perfect. Thanks alot.
Phil

"Phil" wrote in message
...
Thanks alot. Looks like what I need. I'll give this a try.
Phil

"Dave Peterson" wrote in message
...
Dim iCtr as long
dim DestCell as Range

Set DestCell = worksheets("totals").range("A20")

for ictr = 1 to worksheets.count
if lcase(worksheets(ictr).name) = lcase("totals") then
'do nothing
else
destcell.value = worksheets(Ictr).range("c2").value
set destcell = destcell.offset(1,0)
end if
next ictr



Phil wrote:

Background:
I have 4 sheets in the workbook. One sheet is named TOTALS. The other 3
sheets will have different names that change each month.

Scenario:
On the three sheets that are not named totals there is data in cell C2.
I
need some VB code that will goto the first sheet, copy the data from
cell
C2, go back to the totals sheet, paste the data in cell A20, then goto
the
second sheet, copy the data from C2, goto the totals sheet, paste the
data
into A21, then goto the third sheet, copy the data from C2, goto the
totals
sheet, paste the data into A22.
If the sheet names were always the same I could get it, but since the
sheet
names will change each month, I'm stumped. I tried this but it includes
the
totals sheet and therefore doesn't work. I dont know how to get it to
just
get the data from the three sheets and not the totals sheet.

Dim objWorksheet As Worksheet
For Each objWorksheet In ActiveWorkbook.Worksheets
Range("C2").Select
Selection.Copy
Sheets("TOTALS").Select
If Range("A20") = "" Then
Range("A20").Select
ActiveSheet.Paste
ElseIf Range("A21") = "" Then
Range("A22").Select
ActiveSheet.Paste
ElseIf Range("A22") = "" Then
Range("A21").Select
ActiveSheet.Paste
End If
Next objWorksheet

Thanks,
Phil

--

Dave Peterson