macro wrongly naming new sheets and Error the operation is attempting to shift cells

[phillee]

i have recorded a macro so that it inputs the data onto a spreadsheet of customers and creates a new 'contact card'
i have a few issues that i could do with help with
1) the new workbook i want the company name as the sheet name i just does it with the one i copied for the record
2) keeps coming up with a error saying its trying to relocate cells
any help would be appriciated

[TMS]

Not much to go on really .

Suggest that a) you read the forum rules regarding thread titles; b) post the code in the thread so it can be (re)viewed; and c) upload a sample workbook so that potential solutions can be tested.

It sounds as though what you are trying to achieve is to create multiple workbooks, one for each customer contact / contact card. Not really sure of the value of that. Maybe you could describe what you are trying to achieve. I'd have thought a customer data list on one sheet together with a drop down selection box on a second sheet would give you what you need. Then, if you want to print the contact cards, you could have a separate small macro to cycle through the list and print each record. Just my take on it though.

Regards, TMS

[phillee]

Sub test3()
'
' test3 Macro
'

'
Sheets("Template").Select
Sheets("Template").Copy After:=Sheets(3)
Sheets("Customer input").Select
Range("K11:K19").Select
Selection.Copy
Sheets("Template (2)").Select
Range("C1").Select
ActiveSheet.Paste
Range("C1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Template (2)").Select
Sheets("Template (2)").Name = "shaneco"
Sheets("shaneco").Select
Range("C1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Data").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("shaneco").Select
Selection.Copy
Sheets("Data").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("shaneco").Select
Range("C2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Data").Select
Range("B2").Select
ActiveSheet.Paste
Sheets("shaneco").Select
Range("C3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Data").Select
Range("C2").Select
ActiveSheet.Paste
Sheets("shaneco").Select
Range("C4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Data").Select
Range("E2").Select
ActiveSheet.Paste
Sheets("shaneco").Select
Range("C5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Data").Select
Range("F2").Select
ActiveSheet.Paste
Sheets("shaneco").Select
Range("C6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Data").Select
Range("G2").Select
ActiveSheet.Paste
Sheets("shaneco").Select
Range("C7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Data").Select
Range("H2").Select
ActiveSheet.Paste
Sheets("shaneco").Select
Range("C8").Select
Application.CutCopyMode = False
Selection.Copy
Range("B41").Select
Sheets("Data").Select
Range("I2").Select
ActiveSheet.Paste
Sheets("shaneco").Select
Range("C9").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Data").Select
Range("J2").Select
ActiveSheet.Paste
Range("J12").Select
Sheets("Customer input").Select
Range("K11:K21").Select
Range("K21").Activate
Application.CutCopyMode = False
Selection.ClearContents
Range("C2").Select
End Sub
TEST workbook.xlsm

you summed it up pretty well just having a few issues as not had to use excel for about 12 years

[TMS]

Please Login or Register  to view this content.

See the attached updated workbook.


Regards, TMS

[phillee]

That's done the trick thanks very much this forum is a excellent place

your help has been much apriciated

[TMS]

You're welcome. Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[phillee]

thanks was wondering how to mark as solved