MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro selects current worksheet


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

: 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.

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 Brad on October 15, 2001 2:27 PM

You will need to save the data in the current worksheet and use the copy function to place the information to the clipboard and then open the receiving workbook in code and place the information in the appropriate spot. The only tricky part is the dynamic nature ( only because I do this in reverse and the info is placed from cell A1 down/over.

So:
Selection.copy
workbook.("Name_of_Workbook").select
Selection.paste

is the basic idea. You copy from the current worksheet: sheet("Worksheet_Name").select and paste the information to the selected workbook.

Check your user documentation regarding the use of paste to a first blank cell. Or, maybe someone has the bit of code tio make available to you.


HTH...Brad

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

See :- 1437.html