Pasting from one workbook with multiple sheets to various workbooks

[motopdx]

I'm trying to paste from one workbook with multiple sheets to several different workbooks. Example: Workbook "X" has 50 sheets labeled 1-50. I need each sheet copied to the respective workbook "Y". Each sheet in workbook "X" can have a different range of active cells that need to be copied over.

Examples:
workbook "X" sheet "1" copied to workbook "Y" sheet "Jan" (cell A2 to cell Z50)
workbook "X" sheet "2" copied to workbook "Y" sheet "Jan" (cell A2 to cell Z125)

Here is what I was trying with no luck and probably not even close.

Sub Data_Feb()
Dim x As Workbook
Dim y As Workbook

Set x = Workbooks.Open("workbook file path")
Set y = Workbooks.Open("workbook file path")

'Copy from workbook "X" & specific sheet
x.Sheets("584").Range ("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

'Paste to workbook "Y"
y.Sheets("Feb").Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'Close & Save workbooks
x.Close
y.Save
y.Close

 

[yinkajewole]

do you want to copy the cells in each of the worksheet X to be replaced into one sheet in worksheet Y?

 

[motopdx]

Each sheet in worksheet X will be copied to a different workbook. i.e sheet 584 will be copied into workbook 584; sheet 589 will be copied into workbook 589 and so forth.

 

[yinkajewole]

Each sheet in worksheet X will be copied to a different workbook. i.e sheet 584 will be copied into workbook 584; sheet 589 will be copied into workbook 589 and so forth.

it seems you are saying different things... why is workbook Y have months as sheet names unlike Workbook X that has figures as sheet names?

 

[motopdx]

Workbook X relates to business locations which is represented by numbers. Each business location have their own workbook and within their workbook is each month to track data by month.

 

[yinkajewole]

Ok.
1. does the business location workbooks have the same file names as the sheets names in workbook X?
2. Are you copying the whole sheet content to "Jan" sheet in workbook Y alone?

 

[motopdx]

Yes, all the sheet names in workbook X have the same names as workbook Y. However, I run this file each month to populate the new data and is why there are the 12 months in workbook Y

At the end of the year I will have 12 workbooks X but only 1 workbook per branch which include the data from all 12 months. Sorry for the sloppy screenshot but hopefully it helps.

 

[yinkajewole]

try this on a sample data

Sub copySheetData()
Dim w1 As Workbook, w2 As Workbook, sel As Boolean, folder$, fol As FileDialog, ws As Worksheet
Dim sourceD As String, sMonth As String, adDr As Variant
If MsgBox("Please, Click Yes to choose the source file", vbYesNo) = vbYes Then

    With Application.FileDialog(msoFileDialogFilePicker)
        ' show the file picker dialog box
        If .Show <> 0 Then sourceD = .SelectedItems(1)
    End With
Else
Exit Sub
End If

MsgBox "Please, select the folder of the Business Location"
Set fol = Application.FileDialog(msoFileDialogFolderPicker)
fol.AllowMultiSelect = False
sel = fol.Show
If sel Then folder = fol.SelectedItems(1) & "\" Else Exit Sub
sMonth = InputBox("Input the month")
Set w1 = Workbooks.Open(sourceD)
For Each ws In w1.Worksheets
    If FileThere(folder & ws.Name & " " & Format(Date, "yyyy") & ".xlsx") Then
    Set w2 = Workbooks.Open(folder & ws.Name & " " & Format(Date, "yyyy"))
    a = Split(ws.UsedRange.Address, "$")
        For i = 0 To UBound(a)
        If adDr = "" Then adDr = a(i) Else adDr = adDr & a(i)
        Next i
        On Error GoTo nFound
    w2.Worksheets(sMonth).Range(adDr).Value = ws.UsedRange.Value
    w2.Save
    w2.Close
    n = n + 1
    End If
    adDr = ""
Next ws
w1.Close
MsgBox IIf(n > 0, n & " files copied", "Files not found")
MsgBox n & " files copied"
Exit Sub
nFound:
MsgBox "Month not found"
End Sub
Function FileThere(filename As String) As Boolean
     FileThere = (Dir(filename) > "")
End Function

 

[motopdx]

The above macro didn't work but it also didn't pop-up an error. The macro is more complicated than I understand so here is some additional screen shots below.

This is the source data with a limited # of tabs (5). I think the full file has about 75 tabs. I would copy the data from A1 to W1 and then use to copy all the data in the tab.

x.Sheets("8").Range ("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

This is the destination folder with the files label as the tab names from the source data.

This is what the destination files looks like with the 12 months.

Lets say the source data file is Jan and we want to paste values into cell A1 on the Jan tab for each respective workbook

Also I tried to upload the workbooks but it didn't let me with the file extensions.

Thank you for all the help.

 

[yinkajewole]

I thought the year "2020" was added to file names e.g. 64 2020.xlsx, 56 2020.xlsx.
Because if the file names are not in that format, it won't work