Solved Copy and paste template code needed, dependent on content

June 18, 2013 at 01:58:16
Specs: Windows 7
Ok so I am new to VBA (about 8 hours experience self teaching), I am trying to automate our annual reports at work and so far I have got a master worksheet with all data populated. Being new I am using the record macro function to write my code and so far I have got Excel to create and name each worksheet and for Excel to populate the annual report template - however what I cannot do with the record macro function is get Excel to look at what template I need and copy and paste the template to the correct worksheet.
I have been googling for about 4 hours to try and look for a code but have not come across one.

The info you might need is ....

Colum B of 'master' worksheet contains which template is required (I have a drop down selection box for the user to select correct template) The templates are worksheets within the same Excel workbook and are named LP1, LP2, LP3 etc up to LP 10 (and hidden from view to th user). So if cell B1 on the 'master' states LP 1 and Column A (which is what the current macro creates a blank work sheet names with the cell content of column A) I want Excel to copy and paste the template LP1 and paste it in the correct worksheet.

I really fear the above will not make sense as I said above I have all of about 8 hours experience self teaching to get this far and I know what I want in my head but not sure anyone else will!!!! I am pleading for anyone to assist with this as I am literally being driven insane by this brilliant and powerful system because I don't have an idea how to understand the ridiculous language it speaks!! Oh and it's Excel 2010.


See More: Copy and paste template code needed, dependent on content

Report •

✔ Best Answer
June 18, 2013 at 11:03:55
Don't take my response to mean you've wasted my time.

I simply wanted to reiterate that unless you give us something to work with, it's hard for us to offer specific suggestions that will solve your issue on the first try. The only thing we have to work with is what you post. We can't see your spreadsheet from where we're sitting nor can we read minds.

Let me try this a different way...

If you use the techniques outlined in this tutorial, you'll be able to Single Step through your code and watch as it executes each instruction one by one:

http://www.computing.net/howtos/sho...

First off, you'll be able to delete the instructions that don't do anything useful, such as the instructions that were recorded by your "clicking back and forth and scrolling whilst using the macro recorder". Each time an instruction does something useless, highlight the line and delete so it won't be there the next time you run the code. That is typically the first thing you'll want to do whenever you want to clean up the bloated code that the recorder produces.

As for your specific question about using the contents of a cell to choose the sheet into which the data will be pasted, let me reword what I said in Response # 1 and tie it together with the Single Stepping technique from the tutorial.

As you Single Step through the code via F8 (Read the tutorial!) I assume that you will eventually come to the instruction where you Select the sheet into which you are doing the paste. It might look something like this where "Sheet2" is the hardcoded name of the sheet (LP1, LP2, etc.)

Sheets("Sheet2").Select
or
Sheets("LP1").Select

I sounds to me like instead of using the hardcoded name, you want the code to use the name of a sheet that was chosen from a drop down list. If you replace the line with the hardcoded name with this line, it will use the value in A1 as the sheet name:

Worksheets(Range("A1").Value).Select

In other words, if A1 contains LP1, the code will Select sheet LP1.

Obviously, this assumes that your drop down is in A1 of the ActiveSheet. If not, change the A1 to be the correct cell reference. You may need to use the name of the sheet where the drop down is located in that instruction. If so, it'll look something like this:

Worksheets(Sheets("Master").Range("A1").Value).Select

This will tell VBA to select the Sheet whose name appears in cell A1 of the Sheet named "Master".

I really, seriously, truly believe that you will gain a much deeper understand of the VBA language if you use the techniques in the tutorial I've written (link above) as you Single Step through your code and watch what each instruction does each time you press F8.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
June 18, 2013 at 07:25:34
Since you didn't post any code for us to review, we can't offer any suggestions specific to your macro.

In general, if you want to use a cell's content as a sheet name, you would replace a hardcoded sheet name with a reference to the cell.

For example, if your recorded code has this:

Sheets("Sheet2").Select

You could replace it with this:

Worksheets(Range("A1").Value).Select

Where Sheet1!A1 contains the text string Sheet2.

Note: You need to use the Worksheets collection as opposed to the Sheets collection when referencing a cell in this manner.

A few other points:

1 - The macro recorder creates very bloated code with a lot of instructions that can be condensed to make the code more efficient and readable.

For example, you rarely have to actually Select an object in VBA to perform an operation on it. The recorder will create code that looks like this:

Sub Macro4()
'
' Macro4 Macro
'

'
    Range("A5").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A5").Select
    ActiveSheet.Paste

End Sub

The same thing can be accomplish within VBA without any Sheets or Cells being Selected:

Sub BetterCode()
'
    Range("A5").Copy _
      Destination:=Sheets("Sheet2").Range("A5")

End Sub

2 - This How-To may help you troubleshoot your code. Using these debugging techniques can help you understand recorded code as well as code you find in forums such as computing.net:

http://www.computing.net/howtos/sho...

3 - Before posting code or example data in this forum, please click on the following line and read the instructions found via that link.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
June 18, 2013 at 07:52:16
Thanks Derby dad 03 for taking the time to respond, I didn't even think to copy the code. I have pasted below the code which I have used to create new worksheet for each row populated in the 'master' worksheet

Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("Master").Range("A2")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
Next MyCell
End Sub

I guess I just need to know how to now say if rown 1 column B says (for example) LP1 it copies and pastes the template in worksheet LP1 in a new sheet.

You have probably answered that above but if I am honest I do not understand enough about VBA to understand what you have put (although I do undersrtand what you say about the Macro recorder making the code larger), which I feel really bad about, as you have taken the time to write the response, which I am very thankful for.

I'm sure one day that the code language will just click for me but at the moment i'm just struggling!


Report •

#3
June 18, 2013 at 08:36:11
I'm really confused.

First you say:

"Being new I am using the record macro function to write my code "

The code I see above was not created by the macro recorder. The recorder cannot create loops nor write instructions that use the Set instruction. That code was either manually written, copied from someplace or is a major modification of recorded code.

Then you say:

"so far I have got Excel to create and name each worksheet and for Excel to populate the annual report template "

Nothing in your code populates any sheets. It does nothing more than add sheets and rename them. Therefore there is nothing for me to suggest related to using a value in a cell to choose a sheet to populate, other than what I posted in my previous post. If you have some other code that you have not shared, perhaps this would be a good time to do that.

Finally, did you not see item #3 in my previous post related to reading the instructions on how to post code in this forum?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
June 18, 2013 at 08:51:10
Really sorry to have confused you, yes I have been using the macro recorder for certain things but things I can't do on the recorder I have been googling forums like this and using the code suggested!!

I think this thread shows that I am no where near ready with VBA to be tackling what I am trying to do as I just do not understand enough.

I am going to go and read up on VBA a little before I try and google my way to automate a process.

I am sorry to have wasted your time I think I was just trying to use the forum as a shortcut to get the code without putting the time to study VBA and understand it.

By the way, there is no way I would post the recorded macro that I have set up for populating the spreadsheet as it is about 12 foot long with my clicking back and forth and scrolling whilst using the macro recorder. You would all be in fits of laughter!! Thanks again for your time


Report •

#5
June 18, 2013 at 11:03:55
✔ Best Answer
Don't take my response to mean you've wasted my time.

I simply wanted to reiterate that unless you give us something to work with, it's hard for us to offer specific suggestions that will solve your issue on the first try. The only thing we have to work with is what you post. We can't see your spreadsheet from where we're sitting nor can we read minds.

Let me try this a different way...

If you use the techniques outlined in this tutorial, you'll be able to Single Step through your code and watch as it executes each instruction one by one:

http://www.computing.net/howtos/sho...

First off, you'll be able to delete the instructions that don't do anything useful, such as the instructions that were recorded by your "clicking back and forth and scrolling whilst using the macro recorder". Each time an instruction does something useless, highlight the line and delete so it won't be there the next time you run the code. That is typically the first thing you'll want to do whenever you want to clean up the bloated code that the recorder produces.

As for your specific question about using the contents of a cell to choose the sheet into which the data will be pasted, let me reword what I said in Response # 1 and tie it together with the Single Stepping technique from the tutorial.

As you Single Step through the code via F8 (Read the tutorial!) I assume that you will eventually come to the instruction where you Select the sheet into which you are doing the paste. It might look something like this where "Sheet2" is the hardcoded name of the sheet (LP1, LP2, etc.)

Sheets("Sheet2").Select
or
Sheets("LP1").Select

I sounds to me like instead of using the hardcoded name, you want the code to use the name of a sheet that was chosen from a drop down list. If you replace the line with the hardcoded name with this line, it will use the value in A1 as the sheet name:

Worksheets(Range("A1").Value).Select

In other words, if A1 contains LP1, the code will Select sheet LP1.

Obviously, this assumes that your drop down is in A1 of the ActiveSheet. If not, change the A1 to be the correct cell reference. You may need to use the name of the sheet where the drop down is located in that instruction. If so, it'll look something like this:

Worksheets(Sheets("Master").Range("A1").Value).Select

This will tell VBA to select the Sheet whose name appears in cell A1 of the Sheet named "Master".

I really, seriously, truly believe that you will gain a much deeper understand of the VBA language if you use the techniques in the tutorial I've written (link above) as you Single Step through your code and watch what each instruction does each time you press F8.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
June 19, 2013 at 01:35:25
Thank you I have just been having a quick play following looking at your tuturiol and this is so helpful.

I will ne an expert in no time.

Thanks again.


Report •

Ask Question