Copying desired information from one workbook to another workbook with the same worksheet names

[K0st4din]

Hello, everyone.
Please for your help, I just do not know how to do it with a macro and right now I do it by hand.
I have two working books.
The first: is "Base"
The second - I transfer information about what I want from me (usually last month).
At the base I have worksheets with names (London, Paris, New York, China, etc.) and many more worksheets.
In the second workbook - I have the same worksheets: (London, Paris, New York, China, etc.).
In the workbook "Base" in the above mentioned worksheets I report information every month. Once I have applied it, she -> the information accumulates for each month back and the file becomes very large.
For each worksheet (London, Paris, New York, China, etc.), I filter the past month, for example 4.2018 (in the Base) and put the information in the second workbook only the selected month.
Please for your help, do it somehow with a macro.
The date is in column A2:to the end. Once I've chosen the date, I copy all the rows to the end. In each of the worksheets the rows are different.
A button that Somehow asked me: "What month do you want to copy and apply from Base in the other Workbook?"
Please, if there is any ambiguity, ask to help

 

[My Aswer Is This]

I understand what your image is showing.

The biggest problem I see is your not giving me exact sheet names.

I need you to post the two exact sheet names

Is it base or Base or exactly what.

And is it FinalResult or what exactly.

And give me the exact names of three of the 28 sheet names.

Do not say city1 city2

We cannot test things like this if you just want to give me sample names

 

[K0st4din]

Ok,
the name of the original file is: "All Sales by Month" - I have been working for seven years and never changed my name
The file name in which each month we copy the information for the last past month is: "Sales by cities and districts"
The names of the worksheets in the original and in the second workbook (only those that I copy) are exactly the same.
Array ("Sofia", "Plovdiv", "Stara Zagora", "Veliko Tarnovo", "Varna", "London", "Paris area") - I wrote only a few, they are 28 districts.
We can once make the macro work, then I will change the names.

P.S. - I just want to share with you that I'm doing the tests on the test file. I can not afford to punch it on the original because I'm not sure if it will work properly. When I'm sure, then I will put it in the original file.

 

[My Aswer Is This]

Well I have redone this to have three of your sheet names in the array
You will see where I have marked in red the two Workbook names
Change these two Workbook names yo what you need
You only have to change them in this one place.

I have one show as copy from Workbook and other marked as copy to Workbook.

Sub Test()
'Modified 5/9/2018 3:20 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim b As Long
Dim Lastrow As Long
Dim Lastrowa As Long
'Dim s As Long
Dim ans As Long
Dim mon As Long
Dim WN As String
Dim WNN As String
Dim s As Variant
Dim x As Long
s = Array("Sofia", "Plovdiv", "Stara Zagora")
x = UBound(s)
WNN = "Base" & ".xlsm" '[COLOR=#ff0000]This is copy from workbook[/COLOR]
WN = "FinalResult" & ".xlsm" ' [COLOR=#ff0000]This is copy to workbook[/COLOR]
mon = Month(InputBox("Enter Date for this Month", "Hello", Date))
    For b = 1 To x + 1
        
        Lastrow = Workbooks(WNN).Sheets(s(b - 1)).Cells(Rows.Count, "A").End(xlUp).Row
        Lastrowa = Workbooks(WN).Sheets(Sheets(s(b - 1)).Name).Cells(Rows.Count, "A").End(xlUp).Row + 1
            For i = 2 To Lastrow
                ans = Month(Workbooks(WNN).Sheets(s(b - 1)).Cells(i, 1).Value)
                If ans = mon Then Workbooks(WNN).Sheets(s(b - 1)).Rows(i).Copy Workbooks(WN).Sheets(s(b - 1)).Rows(Lastrowa): Lastrowa = Lastrowa + 1
            Next
    Next
Application.ScreenUpdating = True
End Sub

 

[K0st4din]

SOLVED Re: Copying desired information from one workbook to another workbook with the same worksheet names

Hello,
you are an incredible man.
In this case, I have missed, somewhere between the lines, that both files should be open.
There was no problem with the name of the workbook or worksheets, I'm a fool because I did not open the second workbook.
Everything works impeccably and without any mistakes, and now my work will become a pleasant pleasure.
If I could, I would give you 10000 points and endless gratitude to you and all those who help us at times where we can not cope.
Be alive and healthy, and continue to help us.
Thank you heartly.

 

[My Aswer Is This]

Re: SOLVED Re: Copying desired information from one workbook to another workbook with the same worksheet names

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.


I was starting to wonder why I could not understand what I was doing wrong.
I always test my scripts. And it worked for me.

 

[K0st4din]

Hello,
I'm here again.
The macro works, but I have a problem when I put it in the original file.
When I write my desired month and year, for example: 1.4.2018, the macro transfers the information, but also transfers the old dates, starting all that is the 4th month it transfers: 1.4.2013, 1.4.2014, 1.4.2015, 1.4.2016 , 1.4.2017, 1.4.2018.
I beg you, look at the macro, why it's so. Why does not he just transfer me, the month and year I want?
If you wish, I'll upload photos before and after the macro.
Thank you very much!

 

[K0st4din]

Before macro:


I write mounth for example: 1.2.2018



After macro:

 

[My Aswer Is This]

It's because you said in your original post:

A button that Somehow asked me: "​

What month​

do you want to copy and apply from Base in the other Workbook?"

You never said anything about Year.
​

 

[My Aswer Is This]

Try this:

Sub Test()
'Modified 5/13/2018 5:40 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim b As Long
Dim Lastrow As Long
Dim Lastrowa As Long
Dim ans As Long
Dim anss As Long
Dim mon As Long
Dim Yea As Long
Dim aa As Variant
Dim WN As String
Dim WNN As String
Dim s As Variant
Dim x As Long
s = Array("Sofia", "Plovdiv", "Stara Zagora")
x = UBound(s)
WNN = "Base" & ".xlsm" 'This is copy from workbook
WN = "FinalResult" & ".xlsm" ' This is copy to workbook
aa = InputBox("Enter Date for this Month", "Hello", Date)
mon = Month(aa)
Yea = Year(aa)

    For b = 1 To x + 1
        
        Lastrow = Workbooks(WNN).Sheets(s(b - 1)).Cells(Rows.Count, "A").End(xlUp).Row
        Lastrowa = Workbooks(WN).Sheets(Sheets(s(b - 1)).Name).Cells(Rows.Count, "A").End(xlUp).Row + 1
            For i = 2 To Lastrow
                ans = Month(Workbooks(WNN).Sheets(s(b - 1)).Cells(i, 1).Value)
                anss = Year(Workbooks(WNN).Sheets(s(b - 1)).Cells(i, 1).Value)
                
                If ans = mon And anss = Yea Then Workbooks(WNN).Sheets(s(b - 1)).Rows(i).Copy Workbooks(WN).Sheets(s(b - 1)).Rows(Lastrowa): Lastrowa = Lastrowa + 1
            Next
    Next
Application.ScreenUpdating = True
End Sub

 

[K0st4din]

Everything is alright now.
You are right, I really wrote only for a month without mentioning the desired year.
But, you're perfect and helped me again.
Thank you again cordially.
I think the topic is solved.
Have a nice weekend