VBA project of copy data from one excel to another template

[ely32]

Hello all,

Could you please support me in creating a copy-paste range of data from one sheet of an excel file to a new template sheet that needs to have the header above?
I'm not very expert on the MACROS.
I neer to copy thruogh an automated macro from the cell DG;DH;DI;DJ in the TEMP sheet in my excel (only the data that will have input) and to paste it automatically when a macro button will be pushed to a new excel template that will have the defined headers named RPA PnL Offer Template. This information coppied should be inputed to the respective cell starting at :a2;b2;c2;d2 populating the data coppied from the other excel template.

Appritiate your help please.

Thank you!
Ely

 

[OaklandJim]

I cannot understand what you want. I suggest using the XL2BB add-in to post "minisheets" which are portions of a worksheet. That way we can see what is copied where. Maybe restate the need by referring to the minisheets. Check here to get XL2BB addin: XL2BB - Excel Range to BBCode

Are all worksheets in the same workbook? Is the template in a separate workbook? Can you set up the template so it already has headers?

 

[ely32]

Hello OaklandJim, Thank you for replying

I have already tried to install XL2BB - Excel Range to BBCode but due to the company policies, it does not allow me to install it.
Relating to your questions:

Are all worksheets in the same workbook? No, I need that VBA code to create a predefined excel template as shared above that should have the above headers:​

Customer Account Number

Group Name

MSISDN

New Tariff Plan

Fixed monthly payment for Plan (VAT Inc.)

The values copied for the four first columns should start from A2, B2,C2,D2 and paste the values copied from the first template.​

This is a VBA code that I have tried to create for the process but I have an error every time I run it.

Sub Agreement()
'
' Agreement Macro
'

'
    Range("K51").Select
End Sub
Sub AG()
'
' AG Macro
'

'
    Sheets("Temp").Select
    ActiveWindow.ScrollColumn = 36
    ActiveWindow.ScrollColumn = 37
    ActiveWindow.ScrollColumn = 38
    ActiveWindow.ScrollColumn = 39
    ActiveWindow.ScrollColumn = 40
    ActiveWindow.ScrollColumn = 41
    ActiveWindow.ScrollColumn = 42
    ActiveWindow.ScrollColumn = 43
    ActiveWindow.ScrollColumn = 44
    ActiveWindow.ScrollColumn = 45
    ActiveWindow.ScrollColumn = 46
    ActiveWindow.ScrollColumn = 47
    ActiveWindow.ScrollColumn = 48
    ActiveWindow.ScrollColumn = 49
    ActiveWindow.ScrollColumn = 50
    ActiveWindow.ScrollColumn = 51
    ActiveWindow.ScrollColumn = 52
    ActiveWindow.ScrollColumn = 53
    ActiveWindow.ScrollColumn = 54
    ActiveWindow.ScrollColumn = 55
    ActiveWindow.ScrollColumn = 56
    ActiveWindow.ScrollColumn = 57
    ActiveWindow.ScrollColumn = 58
    ActiveWindow.ScrollColumn = 59
    ActiveWindow.ScrollColumn = 60
    ActiveWindow.ScrollColumn = 61
    ActiveWindow.ScrollColumn = 62
    ActiveWindow.ScrollColumn = 63
    ActiveWindow.ScrollColumn = 64
    ActiveWindow.ScrollColumn = 65
    ActiveWindow.ScrollColumn = 66
    ActiveWindow.ScrollColumn = 67
    ActiveWindow.ScrollColumn = 68
    ActiveWindow.ScrollColumn = 69
    ActiveWindow.ScrollColumn = 70
    ActiveWindow.ScrollColumn = 71
    ActiveWindow.ScrollColumn = 72
    ActiveWindow.ScrollColumn = 73
    ActiveWindow.ScrollColumn = 74
    ActiveWindow.ScrollColumn = 79
    ActiveWindow.ScrollColumn = 86
    ActiveWindow.ScrollColumn = 102
    ActiveWindow.ScrollColumn = 103
    ActiveWindow.ScrollColumn = 102
    ActiveWindow.ScrollColumn = 101
    ActiveWindow.ScrollColumn = 99
    ActiveWindow.ScrollColumn = 97
    ActiveWindow.ScrollColumn = 95
    ActiveWindow.ScrollColumn = 83
    ActiveWindow.ScrollColumn = 84
    ActiveWindow.ScrollColumn = 85
    ActiveWindow.ScrollColumn = 86
    ActiveWindow.ScrollColumn = 87
    ActiveWindow.ScrollColumn = 88
    ActiveWindow.ScrollColumn = 89
    ActiveWindow.ScrollColumn = 90
    ActiveWindow.ScrollColumn = 91
    ActiveWindow.ScrollColumn = 92
    ActiveWindow.ScrollColumn = 93
    ActiveWindow.ScrollColumn = 95
    ActiveWindow.ScrollColumn = 100
    ActiveWindow.ScrollColumn = 101
    ActiveWindow.ScrollColumn = 102
    ActiveWindow.SmallScroll Down:=-112
    Range("DG4:DJ4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ActiveWindow.ScrollRow = 1048542
    ActiveWindow.ScrollRow = 1046625
    ActiveWindow.ScrollRow = 1042791
    ActiveWindow.ScrollRow = 668998
    ActiveWindow.ScrollRow = 253033
    ActiveWindow.ScrollRow = 193609
    ActiveWindow.ScrollRow = 115017
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollColumn = 101
    ActiveWindow.ScrollColumn = 102
    ActiveWindow.ScrollColumn = 103
    ActiveWindow.ScrollColumn = 104
    ActiveWindow.ScrollColumn = 108
    ActiveWindow.ScrollColumn = 122
    ActiveWindow.ScrollColumn = 123
    ActiveWindow.ScrollColumn = 124
    ActiveWindow.ScrollColumn = 125
    ActiveWindow.ScrollColumn = 126
    ActiveWindow.ScrollColumn = 127
    ActiveWindow.ScrollColumn = 128
    ActiveWindow.ScrollColumn = 129
    ActiveWindow.ScrollColumn = 130
    ActiveWindow.ScrollColumn = 131
    Range("EP2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll ToRight:=-28
    Range("DG4:DK4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.ScrollColumn = 100
    ActiveWindow.ScrollColumn = 101
    ActiveWindow.ScrollColumn = 107
    ActiveWindow.ScrollColumn = 113
    ActiveWindow.ScrollColumn = 118
    ActiveWindow.ScrollColumn = 132
    ActiveWindow.SmallScroll Down:=-154
    Range("A4").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 77
    ActiveWindow.ScrollColumn = 97
    ActiveWindow.ScrollColumn = 132
    ActiveWindow.SmallScroll Down:=-84
    Range("EP2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Workbooks.Add
    Windows("Profit_and_Loss_V117.xlsm").Activate
    Range("EP1:ET3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Book1").Activate
    ActiveSheet.Paste
    Selection.ColumnWidth = 30.56
    Selection.ColumnWidth = 24
    Selection.ColumnWidth = 19.67
    Windows("Profit_and_Loss_V117.xlsm").Activate
    Sheets("Offer Overview").Select
End Sub

Is the template in a separate workbook? Can you set up the template so it already has headers? I have uploaded it as an image on the first post ​

 

[OaklandJim]

I am still not quite sure what you need. Is the first picture of the worksheet containing data or is that the template? Are there two workbooks or just one?

FYI: often a "template" sheet is created then hidden. Then when you want to use that template worksheet you make a copy and put it into a new workbook. Otherwise code must do all the labeling and formatting.

So, better to set up the template sheet the way you want it. Then code can make a copy of it then copy data into it.

Might you try again to describe the process then post a link to your workbook so I do not have to recreate it here. Ideally it has some data in it, presumably fake but realistic.

 

[ely32]

Hello OaklandJim,

In the first template "Data that need to be copied" there should be input that will be copied. Actually, I have set a blank table but it should have input.
These 4 columns are in the Data that need to be copied from the template to the RPA PnL Offer Template that should be an automatic draft template that will be generated with the headers defined above every time this macro is being run. The input in the columns Data that need to be copied will be pasted automatically in this predefined new draft template as book 1 or whatever should have this header.

I hope I have made a clear picture to you.

Thank you

 

[OaklandJim]

We may have a language problem: I am having a difficult time understanding exactly what is needed.

From your picture what seems like the data worksheet is named Temp and is in workbook named Active Subscribers. The headers in the worksheet are 1. Customer ID, 2. Account Name, 3. MSISDN, 4. Tariff Plan Name and 5. Monthly. Fee.

The worksheet that appears to be the template is named Requests and is in the workbook named RPA PnL Offer Template. In that template worksheet the headers are 1. Customer Account Number, 2. Group Name, 3. MSISDN, 4. New Tariff Plan and 5. Fixed Monthly Payment. Only the first four columns are filled in.

Which data items in the data worksheet are copied into the template worksheet? The header names are different. Maybe provide the response like this.

Source => Template
Customer ID => Customer Account #
Account Name => Group Name
MSISDN => MSISDN
Tariff Plan Name => New Tariff Plan

Is that the correct?

If you want me to work on this I'd like to get some data and I'd like to see the workbooks. Maybe post data using XL2BB at home? Consider posting links to the two workbooks involved.