Copy from One Sheet to Another - Create New TAB each time

[gtbear]

Hello.

Thank you all in advance for taking the time to look at this.

I have 2 Workbooks, one called Master and one called Copied.

In the Master Workbook I would Like a Button to copy Column D, C, E and J into the copied workbook Copied.

I only want to copy the Row if the Value in Column C is greater than 0.00.

In the Copied Workbook I would need like the first TAB Copied and a New TAB Created from this Copy with the new TAB to be renamed the same as Column A from the Master Workbook.

Once this TAB is created the data from the Master workbook will be copied.

Column E to Column A
Column D to Column B
Column C to Column C
and
Column J to Column D

Finally - once this has happened Column C in the Copied Workbook will need to be totalled and entered at the bottom of Column C.

To me this sounds too complicated so if this is too much of a big ask let me know.

Thanks Again for taking the time to look.

David

[arlu1201]

You want the tab to be named the same as the data in column A. But column A has multiple rows for Jul-12. Only 1 sheet can have that name.

[gtbear]

Thanks for looking and coming back to me.

The value in column A should all be the same. Basically the TAB should be called the date i.e. Jul 12

I have created this code at the moment. It's the copying and pasting that I'm struggling over.

Please Login or Register  to view this content.

[arlu1201]

So all the data should be copied to that 1 tab?

[gtbear]

Into the new tab created I copied the master table from the first tab to the new tab, now I need the data from the Master workbook to be pasted into this table as below.

Column E to Column A
Column D to Column B
Column C to Column C
and
Column J to Column D

David

[rvasquez]

Hello there,

Attached is your original workbooks updated to include a macro that I believe achieves the results you would like. Before you run it you will need to save the attached workbooks somewhere on your computer as the macro reference the copied workbook.

In addition you will need to open the Master Workbook and press Alt+F8 to bring up the macro window. You will need to select the Sub CopyToCopied macro and select "Step Into" to view the code. Anything that appears in green in the Visual Basic Window is a comment I left to help you understand the code. Please scroll through the code until you see the line:

Please Login or Register  to view this content.

I have provided the entire macro below and highlighted the line green in it to give you an idea where it's at. Once you find this line you'll need to change the filepath to your filepath that you saved the Copied Workbook(1).xls workbook to.

After you have done this you can close out of the Visual Basic Window and then select the button "Copy Information to Copied" and the macro will run.

To view what has been done open the copied workbook and you should see the changes.

Please note that in the copied Workbook I have deleted the PW cells to just one as the code inserts the cells for you.

Let me know if this works.

Please Login or Register  to view this content.

[gtbear]

Thanks, works perfectly.

The only issue is formatting.

I added the lines for each paste as follows

Please Login or Register  to view this content.

This makes the border go around the table column and not the cell. If I move the BorderAround line before the PasteSpecial then I get the borders around each cell as hoped but I then get an error at the PasteSpecial line.

Can you tell me where I'm going wrong.

Thanks

David

[rvasquez]

Hey there,

Below is the original code I provided you updated to include the section that adds the border to the cells. For your convenience I hightlighted the added code in red.

Let me know if this helps!

Thanks!

Please Login or Register  to view this content.

[gtbear]

Perfect, thanks for all your help, especially the Green explanations.

David

[rvasquez]

No problem!

[gtbear]

Sorry, I've had to reopen this Thread.

I've used this code on another workbook, Everything was working perfectly but then I decided to have a different sheet for each calendar month. This now Causes a

Please Login or Register  to view this content.

On looking at the Help option it's saying a Variable is diclared as 'Nothing' and refers me to this line of code

Please Login or Register  to view this content.

Completely foxed but I thought it might be due to more than on Varianle with the same name so I added sheet number to the variable names. This had not effect so now I'm foxed

Any Ideas anyone.

Thank You

Regards

David