paste special??

[rwitte]

I am relatively new to VBA and recently took (was forced) on a project (excel 2016) that will take a list of machines and subcomponents to create a separate workbook for each machine (saved by machine name) with tabs for each subcomponent (saved by component name) needed.

On the list, each row will have a machine name and various subcomponents, these cells are named the same as my "master" sheets located in a separate folder.

I have been able to create a new workbook for each machine and get the names to work out dynamically to the correct naming convention, and I can open the correct master sheet that I want to copy onto the first tab, but I have not been able to get the formatting to paste correctly.

I have been reading about "PasteSpecial", but I am getting "excel pastespecial method of range class failed" on that part of the code.

Below is the code I currently have, I realize that it is clunky and it shows my inexperience. Any help to clean it up and get me over this hurdle will be appreciated.

Thanks

Sub makeFiles2()
Dim lCol As Long
Dim lRow As Long
Dim wbName As String
Dim MachName As String 'name of machine
Dim MastLoc As String 'Master Ledger Location
Dim ListLoc As String 'List Location
Dim ListNm As String 'List file Name
Dim filePath As String 'path used to store/open ledger file
Dim filePath1 As String 'path used to open master file
Dim myNewFileName As String 'tab name
Dim strMyDest As String 'holding register for destination file
Dim mySourceWB As Workbook
Dim myDestWB As Workbook 'New workbook name
Dim wkb As Workbook
Dim mySourceSheet As Worksheet
Dim myDestSheet As Worksheet
Dim ws1 As Worksheet
lRow = Cells(Rows.Count, 1).End(xlUp).Row
ListLoc = "C:\Users\T1738RW\Desktop"
ListNm = "machine ledger test.xlsm"
MastLoc = "E:\master ledgers"
'Adding New Workbook
For i = 3 To 8
MachName = Cells(i, 11).Value & "_" & Cells(i, 6).Value
wbName = "E:\testRobotLedgers" & MachName
filePath = wbName & ".xlsm"
'Saving the Workbook
' First capture current workbook and worksheet
Set mySourceWB = ActiveWorkbook
' Build new file name based
myNewFileName = wbName

' Add new workbook and save with name of sheet from other file
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=wbName, FileFormat:=52
Set myDestWB = ActiveWorkbook

ActiveWorkbook.Close 'Close new workbook

lCol = Cells(i, Columns.Count).End(xlToLeft).Column
For a = 17 To lCol
filePath1 = MastLoc & Cells(i, a).Value & ".xlsx"
Workbooks.Open (filePath1)
Sheets(1).Activate
Sheets(1).Cells.Copy

Application.DisplayAlerts = False
ActiveWorkbook.Close 'Close Source Workbook
Application.DisplayAlerts = True
Workbooks.Open myNewFileName & ".xlsm"

Range("A1").Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'ActiveSheet.Paste
Application.CutCopyMode = False


' Resave new workbook
ActiveWorkbook.Save
Next a

ActiveWorkbook.Close
Next i

End Sub

 

[Jerry Sullivan]

Hi rwitte,

Try rearranging the sequence so that both the source and destination workbooks are open when the copy and paste are performed.

btw, there's no benefit of using PasteSpecial if you are using xlPasteAll with default options. Instead you can use the simpler Range.Copy (Source Range, Destination Range)