Populate data in Excel and organise

I Receive an Excel file which has got multiple tabs

I need to copy data from source file to current Template

Option Explicit

Sub CopyData()  ' for Copy from Source to current workbook
Dim wbOpen As Workbook
Dim wbMe As Workbook

Set wbMe = ThisWorkbook
Set wbOpen = Workbooks.Open("Source")

    wbOpen.Sheets(3).Range("A1:v500").Copy _
    Destination:=wbMe.Sheets(1).Range("A1")
    wbOpen.Close , False
End Sub

Open in new window


The above code asks me for the path and worksheet; as the path and worksheet and static it needs to be hard coded ( include the source workbook path and worksheet in code)

Once it is copied to the current open workbook by adding a sheet before current active sheet

it needs to perform below Rules

1. copy from Range U6: activerows   (source sheet ) to B8 (destination sheet)

2. Update If formula ( formula updated in attached file)

3. Add four rows after each active row in Destination Sheet

4. update cells based on the criteria in Column B and Column C in destination Sheet

5. update amounts in Column K and L

6 Concatenate column M

Please help me I am struck
application-sample-for-EE.xlsm
NirvanamanagerAsked:
Who is Participating?
 
ShumsDistinguished Expert - 2017Commented:
Hi,

Check in attached...
  1. Create a new folder on your desktop.
  2. Paste your source sample file into newly created folder.
  3. Download attached file and paste into that newly created folder.
  4. First click on Import Source Data Button, it will prompt you to select the source sample file, which will update your source file sheet.
  5. Then go to Dest File sheet, and click Update Dest Sheet.
  6. That's all.
Hope this is what you were looking for? instead of splitting macros and raising separate questions.
Kindly note you need to update Dst_Head 16 in Source File sheet, in attached, I have updated in Col X, for which I have got details, rest you need to update before execution.
application-sample-for-EE-Other-Col.xlsm
0
 
Roy CoxGroup Finance ManagerCommented:
Is this a continuation of the question that I have been working on?
0
 
NirvanamanagerAuthor Commented:
"Is this a continuation of the question that I have been working on?"

yes Sir. that is the continuation
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
NirvanamanagerAuthor Commented:
need only solution for "Dst_Head 1" and "Dst_Head 10" of Destination file
0
 
NirvanamanagerAuthor Commented:
I mean Destination sheet  worksheet 2 to be precise
0
 
Roy CoxGroup Finance ManagerCommented:
The example code above that you posted opens a workbook and copies to a different workbook.

I'll look at the rest later.
0
 
NirvanamanagerAuthor Commented:
opening the source file and copying it to the current workbook is resolved. The only change in the below code is while paste it have to paste values and formats

Sub copyData()  ' for Copy from Source to current workbook
Dim wbOpen As Workbook
Dim wbMe As Workbook

Set wbMe = ThisWorkbook
Set wbOpen = Workbooks.Open("source")

    wbOpen.Sheets(3).Range("A1:v500").Copy _
    Destination:=wbMe.Sheets(1).Range("A1")
    wbOpen.Close , False
End Sub

Open in new window

0
 
NirvanamanagerAuthor Commented:
I need more help for "Dst_Head 1" and "Dst_Head 10"
0
 
Roy CoxGroup Finance ManagerCommented:
Does that code work?
0
 
NirvanamanagerAuthor Commented:
yes it is working
0
 
Roy CoxGroup Finance ManagerCommented:
Is this what you mean?

Sub copyData()  ' for Copy from Source to current workbook
    Dim wbOpen As Workbook
    Dim sPath As String


    Set wbOpen = Workbooks.Open("source")

    wbOpen.Sheets(3).Range("A1:v500").Copy
    With ThisWorkbook.Sheets(1).Range("A1")
        .PasteSpecial xlPasteFormats
        .PasteSpecial xlValues
        Application.CutCopyMode = False
    End With
    wbOpen.Close , False
End Sub

Open in new window


I think you need to set the path for the source workbook because I can't see what "source" is referring to.

Wht does this mean?

need only solution for "Dst_Head 1" and "Dst_Head 10" of Destination file
0
 
NirvanamanagerAuthor Commented:
Column "Dst_Head 1"of Destination sheet" of attached file has formulas basically getting data from Source sheet. and the same for Column "Dst_Head 10" Cells Highlighted in Yellow are referring to "Source Sheet" and Green one in the current sheet

example.jpgapplication-sample-for-EE.xlsm
0
 
Roy CoxGroup Finance ManagerCommented:
So when we add rows withe previous code then you want to copy the formulas into the rows.
0
 
Roy CoxGroup Finance ManagerCommented:
"Dst_Head 1" is where the previous code is pasting the text from the array.
0
 
NirvanamanagerAuthor Commented:
Sir as mentioned, I have that array to "Dst_Head 2"
0
 
NirvanamanagerAuthor Commented:
the code that I changed to

Sub AddRows()

    Dim rRng As Range
    Dim lRw As Long
    Dim iX As Integer, iJ As Integer

    lRw = 8
    iJ = Sheets(2).Cells(lRw, 2).End(xlDown).Row - 6

    Set rRng = Sheets(2).Cells(13, 3)
    Do While rRng.Value <> ""
        Set rRng = rRng.Offset(1)

        rRng.Offset(1).Resize(4, 20).ClearFormats
        For iX = 1 To iJ
            rRng.EntireRow.Insert
        Next

        Range(rRng.Offset(-iJ), rRng.Offset(-1)).Value = Application.WorksheetFunction.Transpose(Array("A", "B", "C", "D"))

    Loop
End Sub

Open in new window

0
 
Roy CoxGroup Finance ManagerCommented:
I don't understand why you've changed my code
0
 
NirvanamanagerAuthor Commented:
Sir, the array is actually for Column C
0
 
Roy CoxGroup Finance ManagerCommented:
This puts the text from the array into Column C

Can you indicate where the formulas need to be, use this workbook after running my code.
application-sample-for-EE-1-.xlsm
0
 
NirvanamanagerAuthor Commented:
In the attached file I have updated formulas in "Dst_Head 1" ( Column B), "Dst_Head 10" ( Column K) and "Dst_Head 11" ( Column L)

Thank you so much
application-sample-for-EE-1-.xlsm
0
 
Roy CoxGroup Finance ManagerCommented:
I'll take a look
0
 
NirvanamanagerAuthor Commented:
Thank you very much
0
 
Roy CoxGroup Finance ManagerCommented:
See if this is copying the correct formulas. There are no formulas in Column L so I have copied Column M
application-sample-for-EE-1-.xlsm
0
 
NirvanamanagerAuthor Commented:
may be you have sent the old file cannot see the code
0
 
Roy CoxGroup Finance ManagerCommented:
Must have clicked on the wrong file. I've renamed the correct one.
application-sample-for-EE-2-.xlsm
0
 
NirvanamanagerAuthor Commented:
what is the name of the module or macro?
0
 
Roy CoxGroup Finance ManagerCommented:
It's this code

Sub AddRows()

    Dim rRng As Range
    Dim lRw As Long
    Dim iX As Integer, iJ As Integer

    lRw = 8
    iJ = Sheets("Destination File").Cells(lRw, 2).End(xlDown).Row - 7

    Set rRng = Sheets("Destination File").Cells(8, 2)
    Do While rRng.Value <> ""
        Set rRng = rRng.Offset(1)

        rRng.Offset(1).Resize(4, 20).ClearFormats
        For iX = 1 To iJ
            rRng.EntireRow.Insert
        Next

        Range(rRng.Offset(-iJ, 1), rRng.Offset(-1, 1)).Value = Application.WorksheetFunction.Transpose(Array("A", "B", "C", "D"))
    Loop
    
     iX = Sheets("Destination File").Cells(Rows.Count, 2).End(xlUp).Row + iJ
     Range(Cells(lRw, 2), Cells(iX, 2)).FillDown
     Range(Cells(lRw, 11), Cells(iX, 13)).FillDown
End Sub

Open in new window


The other code is for importing the file data.

I'm going out for a while. I'll check back later
0
 
NirvanamanagerAuthor Commented:
oh ok thank you
0
 
NirvanamanagerAuthor Commented:
Here is the file, with formula in columns B, K , L
application-sample-for-EE-Other-Col.xlsm
0
 
Roy CoxGroup Finance ManagerCommented:
The formula in B are not simply copied down, there is no  way of telling where the formulas are from.

The formulas in K & L are in random cells and there is no pattern to copy
0
 
Roy CoxGroup Finance ManagerCommented:
Shums -We aren't working on the imports code
0
 
ShumsDistinguished Expert - 2017Commented:
Roy - Check the first line of this post. Initially OP wanted to correct existing code then other requirements.
0
 
Roy CoxGroup Finance ManagerCommented:
But he keeps changing his mind,
I asked if the code for opening the source workbook worked in post # 8. Nirvana said it did in the next post, but I doubt that it does. He the goes on to talk about formulas
0
 
NirvanamanagerAuthor Commented:
Just genius of work...
0
 
Roy CoxGroup Finance ManagerCommented:
Nirvana, I have asked several times if you are referring to sheets or files you said sheets. Ask your questions clearly in future.
0
 
NirvanamanagerAuthor Commented:
Sure
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.