Copying multiple different selections of data from one workbook to another in excel VBA

Question

I'm trying to figure out how to copy data from one excel workbook to another. The end goal is to automate creation of a sign in sheet — I have information on students in one workbook, and I want to create a new workbook that will have the relevant columns in a different order. I've tried to figure out how to use various methods to copy from one sheet to another but I keep ending up with error messages. Here is the code I'm using:

Option Explicit
Sub signinsheet()
Dim newbook As Workbook
Dim ldate As Date
Dim center As Variant
Dim title As Variant
Dim original As Variant
Dim pastebook As Workbook
Dim students As Integer
Dim wbTarget            As Workbook 'workbook where the data is to be pasted
Dim wbThis              As Workbook 'workbook from where the data is to copied


original = ActiveWorkbook.Name



center = InputBox("What is the center name?")
students = InputBox("How many students?")

ldate = Date
Set pastebook = Workbooks.Add
    With pastebook
        .title = center & ldate
        .Subject = "signup sheet"
        .SaveAs Filename:=center & ldate
    End With

title = center & ldate



Workbooks(original).Activate

Workbooks.Open (title)

Workbooks(title).ActiveSheet.Range("F5", Range("F5").Offset(students, 0)).Value = Workbooks(original).Sheets("Sheet1").Range("B2", Range("B2").Offset(students, 0)).Value

   'set to the current active workbook (the source book)
Set wbThis = ActiveWorkbook


   Set wbTarget = Workbooks.Open(title)

 wbTarget.Sheets("sheet1").Range("F5").Select

 'activate the source book
   wbThis.Activate

   'clear any thing on clipboard to maximize available memory
  Application.CutCopyMode = False

   'copy the range from source book
   wbThis.Sheets("sheet1").Range("B2", Range("B2").Offset(students, 0)).Copy

   'paste the data on the target book
   wbTarget.Sheets("sheet1").Range("F5").PasteSpecial

   'clear any thing on clipboard to maximize available memory
   Application.CutCopyMode = False

   'save the target book
   wbTarget.Save

   'close the workbook
   wbTarget.Close

   'activate the source book again
   wbThis.Activate

   'set to the current active workbook (the source book)
   Set wbThis = ActiveWorkbook

   'open a workbook that has same name as the sheet name
   Set wbTarget = Workbooks.Open(title)

   wbTarget.Sheets("sheet1").Range("A5").Select

   'activate the source book
   wbThis.Activate

   'clear any thing on clipboard to maximize available memory
   Application.CutCopyMode = False

   'copy the range from source book
   wbThis.Sheets("sheet1").Range("C2", Range("C2").Offset(students, 0)).Copy

   'paste the data on the target book
  wbTarget.Sheets("sheet1").Range("F5").PasteSpecial

   'clear any thing on clipboard to maximize available memory
   Application.CutCopyMode = False

   'save the target book
   wbTarget.Save

   'close the workbook
   wbTarget.Close

   'activate the source book again
   wbThis.Activate

   'set to the current active workbook (the source book)
   Set wbThis = ActiveWorkbook

   'open a workbook that has same name as the sheet name
   Set wbTarget = Workbooks.Open(title)

   wbTarget.Sheets("sheet1").Range("B5").Select

   'activate the source book
   wbThis.Activate

   'clear any thing on clipboard to maximize available memory
   Application.CutCopyMode = False

   'copy the range from source book
  wbThis.Sheets("sheet1").Range("D2", Range("D2").Offset(students, 0)).Copy

   'paste the data on the target book
   wbTarget.Sheets("sheet1").Range("F5").PasteSpecial

   'clear any thing on clipboard to maximize available memory
   Application.CutCopyMode = False

   'save the target book
   wbTarget.Save

   'close the workbook
   wbTarget.Close

   'activate the source book again
   wbThis.Activate


End Sub

Basically I want to copy B2 to the number of students from the original workbook to F5 on down on the new workbook. I also want C2 on down to go to A5, and I want D2 on down to go to B5. It'll work occasionally on the first copying over, but after that it breaks down and stops working. (I'm using a mac, and right now referring to workbooks as workbook(original) or workbook(title) works for me. Title is the name of the new workbook. I have some other formatting I'm doing on the sign in sheet but I've removed it from the code to maintain confidentiality (and because it's working).

How do you know "it breaks down and stops working"? Does it give an error? If so, what is the error and which line throws it? — Tim, Jul 22 '16 at 18:49
Huh. It was runtime error 1004 on the second time wbTarget.Sheets("sheet1").Range("A5").Select was popping up, and then I was also getting run time error 91. But it works now for some reason. — Royalelk, Jul 22 '16 at 19:25
Is there a better way of copying from one workbook to another though? This method seems bulky and in particular having to repeatedly close the target workbook (to make the workbook.open function work) seems needlessly complicated and redundant. — Royalelk, Jul 22 '16 at 19:26
Most likely you were `Select`ing something that wasn't available for selection at the moment: http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros — Tim, Jul 22 '16 at 19:27
A better way? I think so. :) Dim a variable as a `Range`. `Set` that range = to the range you want to copy. `Open` the target workbook, then set the target range = to your range variable. Avoid selects and activates and you'll avoid a lot of headaches. — Tim, Jul 22 '16 at 19:31
Do I use the set again? I tried writing new code that would make this work Set names = Sheets("sheet1").Range("B2", Range("B2").Offset(students, 0)) Set am = Sheets("sheet1").Range("C2", Range("C2").Offset(students, 0)) Set pm = Sheets("sheet1").Range("D2", Range("D2").Offset(students, 0)) Workbooks.Open (title) Set Sheets("sheet1").Range("F5", Range("F5").Offset(students, 0)) = names Set Sheets("sheet1").Range("A5", Range("A5").Offset(students, 0)) = am Set Sheets("sheet1").Range("B5", Range("B5").Offset(students, 0)) = pm but for some reason I get a 438 error — Royalelk, Jul 22 '16 at 21:23

score 0 · Answer 1 · answered Jul 23 '16 at 06:44

I believe you will have error on below code:

1) You did not close the workbook after save, so no need to open it again

Set pastebook = Workbooks.Add
    With pastebook
        .title = center & ldate
        .Subject = "signup sheet"
        .SaveAs Filename:=center & ldate
    End With

title = center & ldate

Workbooks(original).Activate

Workbooks.Open (title)  'The title workbook is still open
'beside to open a workbook need full file path, title is only file name

2) wrong use of ActiveSheet

Workbooks(title).ActiveSheet.Range("F5", Range("F5").Offset(students, 0)).Value = .....

because you calling Workbooks(title).ActiveSheet, this is wrong.

Sorry, this is not a direct answer, but you should able to solve it. If you follow below method as best practice.

With below method, you will have no error even you do not activate (wbThis.Activate) the workbook or worksheet while you try to copy & paste

1) Setting Workbook or Worksheet to variable

'Set Worksheet you need to variable
Set pastebook = Workbooks.Add
Set pastesheet = pastebook.sheets("PastToSheetName")

set wsThis = wbThis.sheets("CopyFromSheetName")

2) Reference to the variable when you want to copy & paste

wsThis.Range("B2", Range("B2").Offset(students, 0)).Copy pastesheet.Range("F5")

Copying multiple different selections of data from one workbook to another in excel VBA

1 Answers1