Adjust VBA code to paste in a specific area, depending on the sheet that called it
Posted by Lori on November 07, 2001 4:53 AM
I have a macro that filters a lot of data, then copies the filtered data and pastes it into a different worksheet in the same workbook. Right now, it does the Monday worksheet, then Tuesday, Wednesday and so on. Each day's data is pasted into the corresponding row (Monday, Tuesday, ...) of a "Totals" worksheet.
Is there a way to say, "copy the data in the current worksheet and copy it into row 7 if the data is coming from the Monday worksheet, row 8 if it's from the Tuesday sheet, 9 if the Wednesday worksheet, etc."
I can send the code I'm using now if necessary, but it's very long. Thanks in advance to anyone that can help me.
Posted by Barrie Davidson on November 07, 2001 5:23 AM
Assuming your sheets are sorted in order (Monday first, Tuesday second, etc.) and Monday is the first sheet in the workbook, you could use something like:
RowNumber = ActiveSheet.Index + 6
ActiveCell.EntireRow.Copy Destination:=sheet("Totals").Range("A" & RowNumber)
Does this help you out?
BarrieBarrie Davidson
Posted by Barrie Davidson on November 07, 2001 5:25 AM
Sorry, make that
RowNumber = ActiveSheet.Index + 6
ActiveCell.EntireRow.Copy Destination:=Sheets("Sheet3").Range("A" & RowNumber)
Destination should be "Sheets" and not "Sheet".
Barrie
Barrie Davidson
Posted by Lori on November 07, 2001 11:03 AM
I'm OK up to a point
I'm at the point that the data is copied into the Summary worksheet, but how do I say "go back to the worksheet which started the macro and continue with the rest of the filtering, copying and pasting?"
Posted by Barrie Davidson on November 07, 2001 11:29 AM
Re: I'm OK up to a point
Lori, the way I wrote the code you never leave the worksheet you are in. The line that states
ActiveCell.EntireRow.Copy Destination:=sheet("Totals").Range("A" & RowNumber)
tells Excel to copy the entire row and paste it in the worksheet named "Totals" at the appropriate row number. You never leave the sheet you were in when you activated the code.
Does this help you out?
BarrieBarrie Davidson
