PDA

View Full Version : This Should be an easy copy/paste



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

Marcster
02-14-2007, 11:53 AM
If you use the Green VBA tags in the message it displays the code like:
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

Range("D1").Activate
For Count = 1 To NumMutations
ActiveCell.Value = "Mutation " & Count
ActiveCell.HorizontalAlignment = xlCenter
ActiveCell.Offset(0, 1).Activate
Next Count

'END OF PROBLEMATIC CODE


'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


It makes for easier reading.

Marcster.

Marcster
02-14-2007, 11:54 AM
Welcome to VBAX by the way :hi:.

I'm sure they'll be an answer posted soon.

Marcster.

Khailand
02-14-2007, 11:59 AM
Thanks Marcster! :friends:

mdmackillop
02-14-2007, 12:52 PM
And here it is.
'creating cell groups for mutation data
For Count = 1 To NumMutations
CopyRange.Copy CopyRange.Offset(, Count * 3)
Next Count

Khailand
02-14-2007, 12:57 PM
You rock! Thanks.

:beerchug:

Khailand
02-14-2007, 01:01 PM
Is

CopyRange.Copy CopyRange.Offset(, Count * 3)

equivalent to:

CopyRange.Copy CopyRange.Offset(0 , Count * 3)

??

mdmackillop
02-14-2007, 01:07 PM
Yes. You can omit the 0 in Offset, Resize etc.