VBA problem: Adding successive sheets and paste special error (code provided)

fwasmann

New Member
Joined
Aug 26, 2014
Messages
5
Hi all,

I am trying to make a macro code which enables me to copy every column (with specific range) in one sheet of the active wb to a new wb, whereby each column is copied to a new sheet.

First I tried the following code:

Public Sub Transfer()
Application.ScreenUpdating = False
'-----------------------------------------
'DEFINE AND SET VARIABLES
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws1 As Worksheet
Dim rng As Range
Set wb1 = ThisWorkbook
Set ws1 = wb1.Worksheets("raw data")
LastCol = ActiveSheet.Cells(265, Application.Columns.Count).End(xlToLeft).Column
Set wb2 = Workbooks.Open("e:\27082014_GARCH_execution_destination.xlsm")
'-----------------------------------------
'COPY SUCESSIVE COLUMNS IN SOURCE WORKBOOK
With wb2
For I = 1 To LastCol - 46
If Worksheets.Count < I Then Sheets.Add After:=Sheets(Sheets.Count)
wb1.Activate
ws1.Activate
Range(Cells(265, 46 + I), Cells(515, 46 + I)).Select
Selection.Copy
'----------------------------------------
'PASTE TO SUCCESSIVE SHEETS IN DESTINATION BOOK
wb2.Worksheets(I).Activate
[b5].Select
ActiveSheet.Paste
Next I
'----------------------------------------
'CLEANUP
Set wb1 = Nothing
Set wb2 = Nothing
Set ws1 = Nothing
End With
Application.ScreenUpdating = True
End Sub

Which worked, except that it copied formula's instead of values and new sheets were blank sheets, while I would prefer new sheets to be the same as existing sheets in wb2.

I have no idea how to make new sheets in wb2 the same as existing sheets (so same content, formula's etc instead of just new blank sheets) and my adjustment (see below) gave me the following error: "PasteSpecial method of Worksheet class failed"

Public Sub Transfer()
Application.ScreenUpdating = False
'-----------------------------------------
'DEFINE AND SET VARIABLES
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws1 As Worksheet
Dim rng As Range
Set wb1 = ThisWorkbook
Set ws1 = wb1.Worksheets("raw data")
LastCol = ActiveSheet.Cells(265, Application.Columns.Count).End(xlToLeft).Column
Set wb2 = Workbooks.Open("e:\27082014_GARCH_execution_destin ation.xlsm")
'-----------------------------------------
'COPY SUCESSIVE COLUMNS IN SOURCE WORKBOOK
With wb2
For I = 1 To LastCol - 46
If Worksheets.Count < I Then Sheets.Add After:=Sheets(Sheets.Count)
wb1.Activate
ws1.Activate
Range(Cells(265, 46 + I), Cells(515, 46 + I)).Select
Selection.Copy
'----------------------------------------
'PASTE TO SUCCESSIVE SHEETS IN DESTINATION BOOK
wb2.Worksheets(I).Activate
[b5].Select
ActiveSheet.PasteSpecial xlPasteValues
Next I
'----------------------------------------
'CLEANUP
Set wb1 = Nothing
Set wb2 = Nothing
Set ws1 = Nothing
End With
Application.ScreenUpdating = True
End Sub

Thanks in advance!

Frans
 
Last edited:

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Hi,

You want to add a sheet(s) to wb2, named the same as the sheet(s) in wb1?
And copy one and one column into new sheets?

PS: GARCH(1,1) analysis or what? :D Time series are awesome.
 
Upvote 0
Thanks Andrew,

That worked indeed.

To help my Copy existing sheets instead of creating new sheets problem I guess I could just count the number of sheets I need and use the following Code:

Sub Copier ()
Dim x As Integer
x = InputBox("Enter number of times to copy Sheet1")
For numtimes = 1 To x
ActiveWorkbook.Sheets("Sheet1").Copy _
After:=ActiveWorkbook.Sheets("Sheet1")
Next
End Sub

Thanks again!
 
Upvote 0
Hi Sturla,

Its GARCH (1,1) indeed!

I want to copy successive columns (the share prices) from the source workbook to successive sheets in wb2 (with the GARCH formula) using the ranges that are specified.
If there are more columns to be copied then there are sheets in wb2, new sheets will be created, but I want new sheets in wb2 to have the same formula's as the sheets that are already present in wb2 (these sheets all have the same format)

Regards,

Frans
 
Last edited:
Upvote 0
Hi Sturla,

Its GARCH (1,1) indeed!

I want to copy successive columns (the share prices) from the source workbook to successive sheets in wb2 (with the GARCH formula) using the ranges that are specified.
If there are more columns to be copied then there are sheets in wb2, new sheets will be created, but I want new sheets in wb2 to have the same formula's as the sheets that are already present in wb2 (these sheets all have the same format)

Regards,

Frans

Indeed, Wrote my masters degree using Garch, fun times =)

However, back to topic.
If I want to add sheets when necessary, I use an error handling term, show example in my code.

Code:
Sub addingSheetsOnError()

'Create errorhandling before the selection of new sheet
'If (selection have information) then, find a way to make this If statement true if you still have more columns to paste
***
Copy, and then try to move to the sheet to paste, then if error, sheet will be created, then it will be resumed
***

On Error GoTo createsheet
sheets("select your sheet to paste in").Select
On Error GoTo 0
End if

**** 
Pasting and such here
****



Exit Sub

'sheet names can be looped in
createsheet:
    Worksheets("whatever").Copy After:=Worksheets(ActiveWorkbook.Worksheets.Count) 'names can be dynamic 'as you loop through
    ActiveSheet.Name = "newname" 'this name dynamic aswell
    Resume Next

End Sub

This is just a way to approach it, implement on demand. It will copy formulas. You might want to run this before you run your main code, its up to you how you want to solve it. :)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,846
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top