Solved: Save Utility

[mdmackillop]

I've put this together to assist backup of multiple workbook data to defined locations. There is no error checking to ensure that files etc. exist, and it is not comprehensive with PasteSpecial options (easily added)
Feedback welcome.
Regards
MD

[WINFS]

the utility does not copy data from the source to target as required.

testing files enclosed

Thank you.

[mdmackillop]

Thanks for the feedback.
You changed the name of the workbook which was used in the code. Replace DoCopy with this section of code which will allow for this.
[VBA]
Sub DoCopy()
Dim CopyRange As Range
Dim Cel As Range, Tgt As Range
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim WB As Workbook

On Error GoTo Exits
Application.EnableEvents = False
Application.ScreenUpdating = False
Set WB = ThisWorkbook
Set CopyRange = Range(Cells(4, 1), Cells(Rows.Count, 1).End(xlUp))
For Each Cel In CopyRange
'Set reference ranges
Set Tgt = Cel.Offset(, 5)
Set SourceRange = Cel.Offset(, 3)
Set TargetRange = Tgt.Offset(, 3)
Set CopyOptions = Cel.Offset(, 9)
Set PasteSpecialOption = Cel.Offset(, 9).Resize(, 4)
'Set Source File & Sheet
OpenBook Cel, Cel.Offset(, 1).Text
Set wsSource = wbSource.Sheets(Cel.Offset(, 2).Text)
'Set Target File & Sheet
OpenBook Tgt, Tgt.Offset(, 1).Text
Set wsTarget = wbTarget.Sheets(Tgt.Offset(, 2).Text)
WB.Activate
CopyData wsSource, wsTarget
'Close books if not required for next process
If StayOpenSource = False Then
wbSource.Close False
Set wbSource = Nothing
End If
If StayOpenTarget = False Then
wbTarget.Close True
Set wbTarget = Nothing
End If
Next Cel
Exits:
If Not wbSource Is Nothing Then wbSource.Close False
If Not wbTarget Is Nothing Then wbTarget.Close True

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

[/VBA]

[WINFS]

Update the codes. It copies the data in first source file to the target file and worksheet, but did not copy anythings from the second source file to the target file. test files enclosed.

[mdmackillop]

Your second workbook has all sheets selected. The copy command copies a range from all twelve sheets and therefor requires a minumum 12 sheet workbook to paste into. Add the extra sheets to Test and the code runs.

It does not seem good practice to save a workbook in this state. I'll need to add a check to detect this and cancel the copy to avoid unwanted changes.

Thanks for this. I would never have thought of this situation.

[maytey]

Hi,

Your file is really great! And it really help me to save lots of time.
By the way, is there any way which you can modify to save the file into different folders? Using the same concept?

Thanks.

[mdmackillop]

I'm not entirely clear what you are after. If you wany to save copies of the whole workbook, you can use SaveCopyAs.

[maytey]

Hi, I tried using your code to paste in the sheets from 2 different workbooks into 2 different tabs in a same workbook.

Apparently, the macro can only paste into the first tab and have problem pasting into my second tab.

Illustration:

Workbook A - Summary Tab
Workbook B - Summary Tab

I need to paste the summary tab from workbook A and B into a main workbook C. But i will predefine 2 tabs within workbook C.

Workbook A - Summary tab ==> Paste into Workbook C - Wkbk A tab
Workbook B - Summary tab ==> Paste into Workbook C - Wkbk B tab

Can help urgently??

[maytey]

Hi, the macro managed to work. However, if i do not wish to update the workbooks, how should i amend the script?

Thanks for your help!!

[maytey]

Hi, there's another problem.. i can't seem to paste value the files...
Can help?