Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Data from a form on a worksheet in new workbook...

  Asked By: Cesar    Date: Sep 21    Category: MS Office    Views: 569
  

I have a form in XL that reads data from 200 cases and displays them
one at a time(on one spreadsheet). I would like to be able to run a
macro that would run thru the case counter, read the data into the
form, copy the data, open a new workbook, and paste the case data on
sequentially numbered tabs.

I'm a newbie at this, but my attempt at this is below. It seems to
work up till the point where it is supposed to paste the data in the
new workbook. Any hits would be greatly appreciated. Thanks!



Sub XportQCReport()


Dim Wk As Workbook
Dim Wk2 As Workbook
Dim n As Integer
Dim y As Integer


Set Wk = ActiveWorkbook 'The orginal workbook with the case data
Set Wk2 = Workbooks.Add 'The destination workbook


Wk.Activate
Sheets("PDI_DataQC").Select

y = InputBox("How Many Cases (i.e. 1-200)?")

For n = 1 To y
Range("B1").Select
ActiveCell.FormulaR1C1 = n

Sheets("PDI_DataQC").Select
Cells.Select
Selection.Copy

Wk2.Activate
ActiveWorkbook.Worksheets.Add.Name = n
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Next n


End Sub

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Nagaraju Iyaner     Answered On: Sep 21

Try:
Sub XportQCReport()

Dim Wk As Workbook
Dim Wk2 As Workbook
Dim n As Integer
Dim y As Integer

Set Wk = ActiveWorkbook 'The orginal workbook  with the case data
Set Wk2 = Workbooks.Add 'The destination workbook

Wk.Activate
Sheets("PDI_DataQC").Select

y = InputBox("How Many Cases (i.e. 1-200)?")

For n = 1 To y
Wk.Activate 'Added by Pascal
Range("B1").Select
ActiveCell.FormulaR1C1 = n

Sheets("PDI_DataQC").Select
Cells.Select
Selection.Copy

Wk2.Activate
ActiveWorkbook.Worksheets.Add.Name = n
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False 'Added by Pascal
Next n
End Sub

 
Answer #2    Answered By: Tyrone Sanchez     Answered On: Sep 21

I gave that a try, but no luck... it still seems to be crashing in
the same spot. It has the data  copied, but won't paste it into the
new workbook.

 
Answer #3    Answered By: Jonathan Brown     Answered On: Sep 21

I figured it out.... I needed to
have "Application.Selection.PasteSpecial" rather than
just "Selection.PasteSpecial".

Working Code:
Sub test1()

Dim Wk As Workbook
Dim Wk2 As Workbook
Dim N As Integer
Dim y As Integer

Set Wk = ActiveWorkbook 'The orginal workbook  with the case data
Set Wk2 = Workbooks.Add 'The destination workbook

Wk.Activate
Sheets("PDI_DataQC").Select

y = InputBox("How Many Cases (i.e. 1-200)?")

For N = 1 To y
Wk.Activate
Range("B1").Select
ActiveCell.FormulaR1C1 = N

Sheets("PDI_DataQC").Select
Cells.Select
Selection.Copy

Wk2.Activate
ActiveWorkbook.Worksheets.Add.Name = N

Application.Selection.PasteSpecial Paste:=xlPasteValues, _'HERE'S
WHERE THE PROBLEM WAS
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Next N


End Sub

 
Answer #4    Answered By: Husani Chalthoum     Answered On: Sep 21

Did you?!

What version of excel are you using?

 
Didn't find what you were looking for? Find more on Data from a form on a worksheet in new workbook... Or get search suggestion and latest updates.




Tagged:        

 
 
 
 

Related Post