VBA Magician required

[mikemcbain]

I use Excel for Office 365 in Windows 10.

I have a spreadsheet called C:/Desktop/tkxl_f.xlsm which can have from two to twenty five sheets each with different and variable names and always located between a sheet called Blank and a sheet called Cover.

I wish to copy and paste the range A1:K133 from each of these sheets onto a worksheet called Today in another spreadsheet called C:/Desktop/20190101_2019.xlsm one below the other.

Is it possible or am I asking too much? My macro skills are limited to recording simple stuff.

In anticipation

Mike
Tasmania.

 

[jolivanes]

This assumes that both workbooks are closed and the code is in a third workbook that is open. (or in your personal.xlsb)
I don't think that your path to the desktop is right, if that is where the workbooks are saved and not in a Folder named "Desktop" on the C Drive.
To get your path to the desktop, you can use this.

Sub ABCDE()
MsgBox Environ("USERPROFILE") & "\Desktop"
End Sub

or you can change the two lines in question to
Set wbFrom = Workbooks.Open(CreateObject("WScript.Shell").specialfolders("Desktop") & "\tkxl_f.xlsm")
Set wbTo = Workbooks.Open(CreateObject("WScript.Shell").specialfolders("Desktop") & "\20190101_2019.xlsm")

Sub Maybe()
Dim wbFrom As Workbook, wbTo As Workbook, sh1 As Worksheet, i As Long
Set wbFrom = Workbooks.Open("C:/Desktop/tkxl_f.xlsm")
Set wbTo = Workbooks.Open("C:/Desktop/20190101_2019.xlsm")
Set sh1 = wbTo.Sheets("Today")
    For i = wbFrom.Sheets("Blank").Index + 1 To wbFrom.Sheets("Cover").Index - 1
        With sh1
            .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(133, 11).Value = wbFrom.Sheets(i).Range("A1:K133").Value
        End With
    Next i
wbFrom.Close False
wbTo.Close True
End Sub

 

[mikemcbain]

jolivanes

You are a true magician!
Once I corrected my Desktop location your simple VBA worked an absolute treat.
I am so grateful and can't wait to exploit your work which will save me much time and angst.

Mike.

 

[jolivanes]

Mike
Thanks for letting us know and good luck