mardi 15 novembre 2016

VBA Copy and Paste Between Template Worksheet and Variable Worksheet

I'm trying to copy and paste some data between two worksheets. One is a template and will always have the same name and the other varies depending on which one I open. The code I have now keeps throwing up an error. My current code is below! Any help would be greatly appreciated! I would like to manualy open the random workbook store it's name in a variable. Then have the macro open the template file. It then creates a new tab in the template file, switches back to the manually opened file, selects range("A1:B4"), then copies that to the new sheet in the template file. Let me know if there is a better way! I'm super new to this!

Sub Macro3()

Dim template As Workbook ' template macro
Dim workingFile As String ' current working CSCC Quote download
Dim workingSheet As Worksheet ' only sheet in CSCC Quote
Dim tempSheet As Worksheet ' created sheet in CSCC Quote to hold temp data
Dim fileName As String
Dim templateFile As Workbook
Dim workBook As Workbook
Dim nRow As Long
Dim neededInfo As Long
Dim neededRange As Range

Application.ScreenUpdating = False

' Set template file name
Set templateFile = "20161115 SMARTnet Template.xlsx"

Set templateFile = ActiveWorkbook
templateFile.Active
Set tempSheet = Worksheets.Add(after:=ActiveSheet)
tempSheet.Name = "neededInfo"
workingFile = ActiveSheet.Name
Range("A1:B4").Select
Selection.Copy
templateSheet.Activate
Range("A5").Select
ActiveSheet.Paste
workingFile.Activate

End Sub




Aucun commentaire:

Enregistrer un commentaire