MrExcel.com - Your One Stop for Excel Tips & Solutions

Macro selects current worksheet

Posted by Donald Marshall III on October 15, 2001 6:04 AM

I am trying to write a macro that will pull data from the worksheet I am currently in and post it to a specific worksheet. I am running into problems when I record the macro because it registers the specific name of the sheet I am using, not the general case. Here is part of what I have (I want to change the first line from "45701-1" to "whatever goes in here to indicate the sheet that is currently being used"):

Sheets("45701-1").Select
Range("G8,G13,G18").Select
Range("G18").Activate
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Data (2)").Select
Range("I429").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Thanks.

-Donald

Posted by Dan on October 15, 2001 7:29 AM

The first line of the code selects the worksheet named "45701-1". If you delete that line, it should run the macro on the current sheet you are on. Try deleting the line and see if it works the way you want it to. HTH.

Posted by Donald on October 15, 2001 12:41 PM

Thanks Dan. That seemed to work, but I forgot to mention the rest of the problem. I am pulling data from 6 columns and placing it into the "Data (2)" worksheet. Excel will not let me copy from multiple selections and paste into an area that doesn't look the same on another sheet.

Consequently, I am forced to copy one column, paste into the data sheet, then go back to the original worksheet and do the same for the next three cells. Simply removing the sheet selection caused the last 5 sets of data to be copied from "Data (2)", not the current worksheet.

Additionally, I want to paste the data at the end, not specifically in the 429, 430, and 431 cells of each column. I will be adding new 'current' worksheets and want to use this macro to copy the data into one comprehensive list. My current macro will paste over the previous data. How can I make it paste in the first open cell?

Thank you to anyone that can help!

-Donald

Posted by Gyula Lorant on October 15, 2001 2:42 PM

Q1 :-
Dim ws As Worksheet
Set ws = ActiveSheet

Q2 :-
You can locate the next available blank cell in column A with :-
Range("A65536").End(xlUp).Offset(1, 0)