Khailand
02-14-2007, 11:49 AM
OK, I'm writing a macro to copy a few columns in an existing file based on the number of data sets that a user will be entering manually. Here's how it goes: you open the file, and there are three columns that are already populated. The first two cells in this range are merged across. Underneathe are labels for three types of data that the user will paste in after the macro has formatted the sheet properly. If the user says that they will be entering 4 data sets, the intent of the macro is to take the first three columns of the sheet that are already formatted and copy it three times for a total of 4 data groups. The problem I have is that my macro only does this properly in the first execution of the copy/paste loop. The rest of the time, it only pastes the first cell. I've seprated out the section of code below with a comment.
Here's the code:
Sub CopyStuff()
Dim Count As Integer
Dim NumMutations As Integer
Dim CopyRange As Range
Dim PasteRange As Range
NumMutations = InputBox("Please enter the number of mutations to analyze:", "Enter number of mutations.")
Range("D1").Activate
With Worksheets("Sheet1")
Set CopyRange = .Columns("D:F")
Set PasteRange = .Range("G1")
End With
'HERE'S THE PROBLEMATIC CODE
CopyRange.Select
Selection.Copy
'creating cell groups for mutation data
For Count = 1 To NumMutations
PasteRange.Activate
ActiveSheet.PasteSpecial
PasteRange = PasteRange.Offset(0, 3)
Next Count
'END OF PROBLEMATIC CODE
Range("D1").Activate
For Count = 1 To NumMutations
ActiveCell.Value = "Mutation " & Count
ActiveCell.HorizontalAlignment = xlCenter
ActiveCell.Offset(0, 1).Activate
Next Count
'labelling mutation sites
Range("d2").Activate
For Count = 1 To NumMutations
ActiveCell.Value = InputBox("Please enter the name of mutation" & " " & Count & ".")
ActiveCell.HorizontalAlignment = xlCenter
ActiveCell.Font.Bold = True
ActiveCell.Offset(0, 1).Activate
Next Count
'labelling mutation genotypes
Range("d3").Activate
For Count = 1 To NumMutations
ActiveCell.Value = "WT"
ActiveCell.HorizontalAlignment = xlCenter
ActiveCell.Offset(0, 1).Value = "Het."
ActiveCell.Offset(0, 1).HorizontalAlignment = xlCenter
ActiveCell.Offset(0, 2).Value = "Homo."
ActiveCell.Offset(0, 2).HorizontalAlignment = xlCenter
ActiveCell.Offset(0, 3).Activate
Next Count
End Sub
thanks in advance for any help!!!!
Kyle
Here's the code:
Sub CopyStuff()
Dim Count As Integer
Dim NumMutations As Integer
Dim CopyRange As Range
Dim PasteRange As Range
NumMutations = InputBox("Please enter the number of mutations to analyze:", "Enter number of mutations.")
Range("D1").Activate
With Worksheets("Sheet1")
Set CopyRange = .Columns("D:F")
Set PasteRange = .Range("G1")
End With
'HERE'S THE PROBLEMATIC CODE
CopyRange.Select
Selection.Copy
'creating cell groups for mutation data
For Count = 1 To NumMutations
PasteRange.Activate
ActiveSheet.PasteSpecial
PasteRange = PasteRange.Offset(0, 3)
Next Count
'END OF PROBLEMATIC CODE
Range("D1").Activate
For Count = 1 To NumMutations
ActiveCell.Value = "Mutation " & Count
ActiveCell.HorizontalAlignment = xlCenter
ActiveCell.Offset(0, 1).Activate
Next Count
'labelling mutation sites
Range("d2").Activate
For Count = 1 To NumMutations
ActiveCell.Value = InputBox("Please enter the name of mutation" & " " & Count & ".")
ActiveCell.HorizontalAlignment = xlCenter
ActiveCell.Font.Bold = True
ActiveCell.Offset(0, 1).Activate
Next Count
'labelling mutation genotypes
Range("d3").Activate
For Count = 1 To NumMutations
ActiveCell.Value = "WT"
ActiveCell.HorizontalAlignment = xlCenter
ActiveCell.Offset(0, 1).Value = "Het."
ActiveCell.Offset(0, 1).HorizontalAlignment = xlCenter
ActiveCell.Offset(0, 2).Value = "Homo."
ActiveCell.Offset(0, 2).HorizontalAlignment = xlCenter
ActiveCell.Offset(0, 3).Activate
Next Count
End Sub
thanks in advance for any help!!!!
Kyle