Copy Data to another workbook

Topic

New Reply

Hi

I have a workbook contains formulas and links with 2 Sheets named “Template” and “Data”
The following macro copy everything on Sheet “Template” to a new workbook

The worksheet being copied has VBA codes associated with it which
I do not want copied to the new workbook. How can I copy just the sheet
without the formulas , VBA code & links; i.e. copy only the Values and Formatting

Private Sub CommandButton1_Click()

Dim FN As String
FN = Application.GetSaveAsFilename(fileFilter:=”Excel Files (*.xls),*.xls”)
If FN = “False” Then
MsgBox “File Save Cancelled by User”
Else
Sheets(“Template”).Copy
ActiveSheet.UsedRange.Formula = ActiveSheet.UsedRange.Value
ActiveWorkbook.SaveAs Filename:=FN, _
FileFormat:=xlNormal, Password:=””, _
WriteResPassword:=””, _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close SaveChanges:=False
End If
End Sub

Thanks

Reply | Quote

Replies

Does the following help?

Code:

Sub PasteOnlyValuesAndFormat()
   '
	   Cells.Select
	   Selection.Copy
	   Windows("Target").Activate
	   Cells.Select
	   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
			SkipBlanks:=False, Transpose:=False
	   Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
		   SkipBlanks:=False, Transpose:=False
   End Sub

Reply | Quote

Does the following help?

Code:

Sub PasteOnlyValuesAndFormat()
   '
	   Cells.Select
	   Selection.Copy
	   Windows("Target").Activate
	   Cells.Select
	   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
			SkipBlanks:=False, Transpose:=False
	   Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
		   SkipBlanks:=False, Transpose:=False
   End Sub

Thanks for looking into this.

There’s an error at this line
Windows(“Target”).Activate

What does this line suppose to do?

thanks

Reply | Quote

Thanks for looking into this.

There’s an error at this line
Windows(“Target”).Activate

What does this line suppose to do?

thanks

Assuming that you wish to paste the data into a file whose name is stored in the variable “FN” (without the quotes), the line will change to:

Windows(FN).Activate

The line of code activates the window where you will paste the data. You will also need to ensure that the appropriate sheet is active.

Reply | Quote

Assuming that you wish to paste the data into a file whose name is stored in the variable “FN” (without the quotes), the line will change to:

Windows(FN).Activate

The line of code activates the window where you will paste the data. You will also need to ensure that the appropriate sheet is active.

How could the variables be known or come from?

What if we need open a new workbook?
How could we change this to

Reply | Quote

How could the variables be known or come from?

What if we need open a new workbook?
How could we change this to

The variables would be those assigned by you earlier in the project.

If you need to open an existing workbook the code would change to:

Code:

Sub PasteOnlyValuesAndFormat()
	'
	 Cells.Select
	 Selection.Copy
	 Workbooks.Open Filename:="filespec"
	 Sheets("Sheet1").Select
	 Cells.Select
	 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
		  SkipBlanks:=False, Transpose:=False
	 Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
		 SkipBlanks:=False, Transpose:=False
	End Sub

If you need to open a New workbook the line
Workbooks.Open Filename:=”filespec”

would be replaced with
Workbooks.Add Template:=”Workbook”

Reply | Quote

The variables would be those assigned by you earlier in the project.

If you need to open an existing workbook the code would change to:

Code:

Sub PasteOnlyValuesAndFormat()
	'
	 Cells.Select
	 Selection.Copy
	 Workbooks.Open Filename:="filespec"
	 Sheets("Sheet1").Select
	 Cells.Select
	 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
		  SkipBlanks:=False, Transpose:=False
	 Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
		 SkipBlanks:=False, Transpose:=False
	End Sub

If you need to open a New workbook the line
Workbooks.Open Filename:=”filespec”

would be replaced with
Workbooks.Add Template:=”Workbook”

Thanks, this works.
In addition, would I use this line at the end to
end the copy mode in “Template”

Application.CutCopyMode = False

How could I save the new workbook with reference to the name in C5?

Again thanks for your guide.

Reply | Quote

Thanks, this works.
In addition, would I use this line at the end to
end the copy mode in “Template”

Application.CutCopyMode = False

How could I save the new workbook with reference to the name in C5?

Again thanks for your guide.

You could use code like:

Code:

Application.CutCopyMode = False
Range("A1").Select

To exit CutCopyMode, and re-select the first cell in the worksheet.

You could use code like this to save the new workbook with a reference to a name in C5:

Code:

   Dim NewWbk As Workbook
   Dim strFileName As String
   strFileName = Range("C5").Text
   Set NewWbk = Workbooks.Add(Template:="Workbook")
   NewWbk.SaveAs Filename:=strFileName
   Set NewWbk = Nothing

Gary

Reply | Quote

You could use code like:

Code:

Application.CutCopyMode = False
Range("A1").Select

To exit CutCopyMode, and re-select the first cell in the worksheet.

You could use code like this to save the new workbook with a reference to a name in C5:

Code:

   Dim NewWbk As Workbook
   Dim strFileName As String
   strFileName = Range("C5").Text
   Set NewWbk = Workbooks.Add(Template:="Workbook")
   NewWbk.SaveAs Filename:=strFileName
   Set NewWbk = Nothing

Gary

Thanks.

Reply | Quote

Thanks.

Is there a different between running the macro from a commandbutton
and from the workbook’s macro

The error happen at Cells.Select when I run it from a commandbutton
but its work when using the workbook’s macro.

Code:

Sub PasteOnlyValuesAndFormat()
	'
	 Cells.Select
	 Selection.Copy
	 Workbooks.Open Filename:="filespec"
	 Sheets("Sheet1").Select
	 Cells.Select
	 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
		  SkipBlanks:=False, Transpose:=False
	 Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
		 SkipBlanks:=False, Transpose:=False
	End Sub

TIA

Reply | Quote

Is there a different between running the macro from a commandbutton
and from the workbook’s macro

The error happen at Cells.Select when I run it from a commandbutton
but its work when using the workbook’s macro.

Code:

Sub PasteOnlyValuesAndFormat()
	   '
		Cells.Select
		Selection.Copy
		Workbooks.Open Filename:="filespec"
		Sheets("Sheet1").Select
		Cells.Select
		Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
			 SkipBlanks:=False, Transpose:=False
		Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
			SkipBlanks:=False, Transpose:=False
	   End Sub

TIA

The only difference of which I am aware is the possibility of running different macros.

[*]Which Cells.Select is highlighted when the code fails?
[*]Where is the code stored which you run successfully?
[*]When the code fails; what message do you receive?
[*]When the code fails and you click “Debug”; what is displayed in the title bar of the Visual Basic Editor?

Reply | Quote