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

K0st4din · May 5, 2018

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 · May 5, 2018

Unless I'm missing something here. You named the copy from workbook "Base"
But you never gave us the name of the copy too workbook.
You always said The other workbook.
Like here:

and put the information in the second workbook

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

And we would need to look in every sheet in Workbook "Base"
And copy all rows with our chosen month in column A and past in same sheet in the other workbook

We need both complete workbook names. Like Base.xlsm

_{^{<strike>

</strike>}}

K0st4din · May 7, 2018

Hello,
yes, of course this is just an example.
Selecting a month, I want to have a pop up window asking: "What month do you want to copy?" and I write, for example: 04.2018 or 03.2018 or any month or year.
The name of the second workbook, I have two options: The first is to navigate by itself or in the macro itself to write the path to it. I prefer the first option and then put the information.
I upload the two sample workbooks. As in the second I have transferred 02.2018
Thank you very much.
Again, I remain available if there are more questions.
Base workbook - > https://drive.google.com/file/d/1vsGM-QrUrDXEeBp_qhaRGTuK8VybMd9_/view?usp=sharing
Final result (second workbook)- > https://drive.google.com/file/d/1rnL5KkDTcc_mbY2rvZhxlS5P112Z3Byc/view?usp=sharing

My Aswer Is This · May 7, 2018

Glad to see your most recent post:
This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

I have never tried to write a script where I do not know the Workbook name.
And how to write a script where the user may want to pause the script and decide if the script should navigate to the proper workbook or have the user navigate to the proper workbook

But I know there are a lot of others here at Mr. Excel who may have a answer for you.

I'm surely hoping someone else here will have a answer I can learn from.
And I never click on links.

K0st4din · May 7, 2018

Hello,
if there is any way to attach the files, please show me. I do not see this option and for this reason I sent a link to the page from which I can share.

My work book "Base" is open and I work in it.
The second workbook is called "Final Month Result" (after the macro is written, I can change the name with what I want and the place where it is located).
As for the script, here I am afraid you do not understand me.
Let's just say this:
The Base file is open
I press a button and a window appears asking me: - Which month do you want to copy and paste?
Then, the macro goes through each worksheet (I mean Array) and put the information in the same worksheet of "Final Month Result"
I'm in line.
Thank you again

My Aswer Is This · May 7, 2018

OK:
Try this:

Run this script from your workbook named (Base.xlsm) This Workbook must be open.

The script looks at all sheets in this workbook.
And expects to find all these same sheet names in Workbook named ("Master.xlsm") and this workbook must be open.

You will get a Inputbox popup and you need to enter a date for the month you want copied over.
You will see I have todays date entered for you as the default data. You can modify the default date entered. You must enter a date something like this 5/7/2018

Try this and see if this works for you. If not please explain.
So if you enter 5/7/2018 the script looks in all sheets with a date in column A for May
and copy that row to a sheet in workbook "Master"

If this script should not look at all sheets in Workbook Base then I would need to know something like look in sheet 5 to 25 of Workbook "Base"
It is much more difficult but can be done if the sheets are not in some order on Workbook "Base"
In this case you would have to give me all those sheet names.

Code:

Sub Test()
'Modified 5/7/2018 6:50 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim Lastrowa As Long
Dim s As Long
Dim ans As Long
Dim mon As Long
mon = Month(InputBox("Enter Date for this Month", "Hello", Date))
    For s = 1 To Sheets.Count
        
        Lastrow = Workbooks("Base.xlsm").Sheets(s).Cells(Rows.Count, "A").End(xlUp).Row
        Lastrowa = Workbooks("Master.xlsm").Sheets(Sheets(s).Name).Cells(Rows.Count, "A").End(xlUp).Row + 1
            For i = 2 To Lastrow
                ans = Month(Workbooks("Base.xlsm").Sheets(s).Cells(i, 1).Value)
                If ans = mon Then Workbooks("Base.xlsm").Sheets(s).Rows(i).Copy Workbooks("Master.xlsm").Sheets(Sheets(s).Name).Rows(Lastrowa): Lastrowa = Lastrowa + 1
            Next
    Next
Application.ScreenUpdating = True

My Aswer Is This · May 7, 2018

If you want a easy way to change your Workbook name try this:
See my note on how to change workbook name.
Marked in red

Code:

Sub Test()
'Modified 5/7/2018 7:05 PM  EDT
Application.ScreenUpdating = False
Dim i 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
WN = "Master" & ".xlsm" ' [COLOR=#ff0000]If you want to modify workbook named just change Master to what ever you[/COLOR] want here
mon = Month(InputBox("Enter Date for this Month", "Hello", Date))
    For s = 1 To Sheets.Count
        
        Lastrow = Workbooks("Base.xlsm").Sheets(s).Cells(Rows.Count, "A").End(xlUp).Row
        Lastrowa = Workbooks(WN).Sheets(Sheets(s).Name).Cells(Rows.Count, "A").End(xlUp).Row + 1
            For i = 2 To Lastrow
                ans = Month(Workbooks("Base.xlsm").Sheets(s).Cells(i, 1).Value)
                If ans = mon Then Workbooks("Base.xlsm").Sheets(s).Rows(i).Copy Workbooks(WN).Sheets(Sheets(s).Name).Rows(Lastrowa): Lastrowa = Lastrowa + 1
            Next
    Next
Application.ScreenUpdating = True
End Sub

K0st4din · May 7, 2018

Hello,
I'm sure things will get, but they will have to be clarified.
Now: In the "Base" I have 75 worksheets. At 28 (Array ("London", "Paris", "New York," and so on to 28) of them with specific names contains information for many years back to this day.
In column A2: until the end are the months and years (1.1.2013, ......................., 1.11.2017, 1.12.2017, 1.1.2018, 1.2.2018 etc), A1 - is constant (date).

In the "Final result" - I have separated specific worksheets with names: Array ("London", "Paris", "New York," and so on 28).
If you downloaded the files, I gave it an example, but if we can do "Array" (My idea is to specify which worksheets to avoid getting a mistake, and so I can control them), then I will change the names of the worksheets.

In the popup window in the "Base" file, when I write, for example: 1.2.2018 or some other month and year, for example I wish to transfer 1.12.2017 from Array ("London", "Paris", "New York" and so up to 28 "), should be transferred to the "Final result" in the same worksheets (Array (" London "," Paris "," New York, "and so 28).

Now: in the macro, as a beginning, I give error 9 to this line:

Code:

Lastrowa = Workbooks(WN).Sheets(Sheets(s).Name).Cells(Rows.Count, "A").End(xlUp).Row + 1

I am convinced that we are getting to the finish.
You can safely download the files, upload them in a google drive, no viruses
Thank you very much, I stay online.
See you soon

My Aswer Is This · May 7, 2018

It sounds like to me you already has a script written which you have not shown me.
And in my sample script I used the Workbook named "Master" and showed you how to fix that if needed.
And if you have numerous sheets it not the best thing to use and array of names. Putting 65 sheet names into an array is not the way I would do things.

Not sure why your insisting on doing things this way.

Why can you not say Sheets(5) to Sheets(25)

And do you already has all these sheets made in your second workbook where we are copying these row to.

I'm just here to help but for some reason you keep saying we need to use an array.

Like here you said:

In the "Final result" - I have separated specific worksheets with names: Array ("London", "Paris", "New York," and so on 28).

So it sounds like you already have a script written.

My Aswer Is This · May 7, 2018

I'm unable to help you modify a script I believe you have partially written but have not showed me.
And I'm sure you have a safe link and a safe workbook you want me to download but I really never do that.

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

K0st4din

Active Member

Excel Facts

My Aswer Is This

Well-known Member

K0st4din

Active Member

My Aswer Is This

Well-known Member

K0st4din

Active Member

My Aswer Is This

Well-known Member

My Aswer Is This

Well-known Member

K0st4din

Active Member

My Aswer Is This

Well-known Member

My Aswer Is This

Well-known Member

Similar threads

Forum statistics

Share this page

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

Active Member

Excel Facts

Well-known Member

Active Member

Well-known Member

Active Member

Well-known Member

Well-known Member

Active Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock