Reputation: 2693
The below Macro opens an external workbook, unhides a particular sheet and reads a couple of text values into variables, creates a new sheet in the current workbook based on those variable values, copies the contents of a worksheet in that workbook, then pastes it into the new one.
However, when I use .Paste
it works fine, but doesn't retain the formatting and only pastes in the text.
If I try to correct this and use
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
It fails.
Why is this?
Sub addsheet()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
filename = Sheets("Instructions").Range("C13").Value
report = "report.xlsx"
report_filepath = "\\xxx\xxx\xxx\report.xlsx"
Dim report_name As String
Workbooks.Open Filename:=(report_filepath)
Windows(report).Activate
Sheets("Info").Visible = True
Sheets("Info").Activate
report_month = Range("B5").Text
report_year = Range("B4").Text
Sheets("Report").Range("A1:AJ498").Copy
Windows(filename).Activate
Windows(report).Close
Set newsheet = Sheets.Add(After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet)
report_name = (report_month & " " & report_year)
newsheet.Name = (report_name)
Sheets("Instructions").Range("C15").Value = (report_name)
Sheets(report_name).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Instructions").Activate
End Sub
Upvotes: 0
Views: 9099
Reputation: 149335
Like I mentioned in my comments, you should avoid the use of .Select/.Activate
. You may want to see THIS
If I change your code and use objects then your code may look like this.
Also move the copy code before the paste and include DoEvents
between then so that Excel gets enough time to place the data onto the clipboard.
Try this
UNTESTED
Sub addsheet()
Dim report_name As String
Dim Filename As String, report_filepath As String, report As String
Dim thisWb As Workbook, thatWb As Workbook
Dim thatWs As Worksheet, NewSheet As Worksheet
Dim report_month As String, report_year As String, report_name As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set thisWb = ThisWorkbook
With thisWb
Set NewSheet = .Sheets.Add(After:=.Sheets(.Worksheets.Count), Count:=1, Type:=xlWorksheet)
End With
report_filepath = "\\xxx\xxx\xxx\report.xlsx"
Set thatWb = Workbooks.Open(report_filepath)
Set thatWs = thatWb.Sheets("Info")
With thatWs
report_month = .Range("B5").Value
report_year = .Range("B4").Value
End With
report_name = report_month & " " & report_year
NewSheet.Name = report_name
thisWb.Sheets("Instructions").Range("C15").Value = report_name
thatWb.Sheets("Report").Range("A1:AJ498").Copy
DoEvents
NewSheet.Range("A1").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
NewSheet.Range("A1").PasteSpecial Paste:=xlPasteFormats, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
thisWb.Sheets("Instructions").Activate
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Upvotes: 1
Reputation: 7282
According to the Office help file, when you use the Worksheet.PasteSpecial
method "You must select the destination range before you use this method.".
If you change
Sheets(report_name).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
to
Sheets(report_name).Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
then it will work.
It would appear that the .Paste
method will default to "A1" if no range is specified.
Further investigation shows that if you use .PasteSpecial
without any parameters, i.e. Sheets(report_name).PasteSpecial
then it will work without specifying a range. But if you include the parameters then it appears that you must specify the range
Upvotes: 0