crthompson1
08-29-2018, 10:59 AM
Hello,
I am trying to copy the data (C6:C78) from an excel spreadsheet Sheet1 and copy it to (C6:C78) in Sheet3. I then want to copy the data from (C6:C78) Sheet2 and copy it to the range (D6:D78) on Sheet3. I then want to copy (D6:D78) from Sheet1 to (E6:E78) on Sheet3. I would then like to copy (D6:D78) from Sheet2 to (F6:F78) on Sheet3 and the pattern continues. How would I increment only the column letter (sometimes the increment is by 1 and sometimes it is by 2) in SourceRange1, SourceRange2, & DestinationRange? The column letters would both need to be incremented such as (C6:C78) becomes (D6:D78) and (C6:C78) becomes (E6:E78). I hope my question makes sense...
Thanks in advance!! My code is listed below:
Dim N As Integer
Dim SourceRange1 As Range
Dim SourceRange2 As Range
Dim DestinationRange As Range
N = 0
'Set the starting copy ranges for sheet1 & sheet2
'Set the starting paste range for sheet3
SourceRange1 = "C6:C78"
SourceRange2 = "C6:C78"
DestinationRange = "C6:C78"
Do Until N = 13
'Cut the first column of data from sheet1 and paste it into the first column of data in sheet3
'Cut 1
'Set0,6,12,18,24,30,36,42,48,54,60,66,72
Sheets("Sheet1").Select
Range(SourceRange1).Select
Selection.Cut
SourceRange1 = SourceRange1 + 2
'Paste 1
Sheets("Sheet7").Select
Range(DestinationRange).Select
ActiveSheet.Paste
DestinationRange = DestinationRange + 1
'Cut the first column of data from sheet2 and paste it into the second column of data in sheet3
'Cut 2
'Set0,6,12,18,24,30,36,42,48,54,60,66,72
Sheets("Sheet2").Select
Range(SourceRange2).Select
Selection.Cut
SourceRange2 = SourceRange2 + 2
'Paste 2
Sheets("Sheet7").Select
Range(DestinationRange).Select
ActiveSheet.Paste
DestinationRange = DestinationRange + 1
'Cut the second "used" column of data from sheet1 and paste it into the third column of data in sheet3
'Cut 1
'Set2,8,14,20,26,32,38,44,50,56,62,68,74
Sheets("Sheet1").Select
Range(SourceRange1).Select
Selection.Cut
SourceRange1 = SourceRange1 + 1
'Paste 1
Sheets("Sheet7").Select
Range(DestinationRange).Select
ActiveSheet.Paste
DestinationRange = DestinationRange + 1
'Cut the second "used" column of data from sheet2 and paste it into the fourth column of data in sheet3... This pattern continues...
'Cut 2
'Set2,8,14,20,26,32,38,44,50,56,62,68,74
Sheets("Sheet2").Select
Range(SourceRange2).Select
Selection.Cut
SourceRange2 = SourceRange2 + 1
'Paste 2
Sheets("Sheet7").Select
Range(DestinationRange).Select
ActiveSheet.Paste
DestinationRange = DestinationRange + 1
'Cut 1
'Set3,9,15,21,27,33,39,45,51,57,63,69,75
Sheets("Sheet1").Select
Range(SourceRange1).Select
Selection.Cut
SourceRange1 = SourceRange1 + 1
'Paste 1
Sheets("Sheet7").Select
Range(DestinationRange).Select
ActiveSheet.Paste
DestinationRange = DestinationRange + 1
'Cut 2
'Set3,9,15,21,27,33,39,45,51,57,63,69,75
Sheets("Sheet2").Select
Range(SourceRange2).Select
Selection.Cut
SourceRange2 = SourceRange2 + 1
'Paste 2
Sheets("Sheet7").Select
Range(DestinationRange).Select
ActiveSheet.Paste
DestinationRange = DestinationRange + 1
'Cut 1
'Set4,10,16,22,28,34,40,46,52,58,64,70,76
Sheets("Sheet1").Select
Range(SourceRange1).Select
Selection.Cut
SourceRange1 = SourceRange1 + 2
'Paste 1
Sheets("Sheet7").Select
Range(DestinationRange).Select
ActiveSheet.Paste
DestinationRange = DestinationRange + 1
'Cut 2
'Set4,10,16,22,28,34,40,46,52,58,64,70,76
Sheets("Sheet2").Select
Range(SourceRange2).Select
Selection.Cut
SourceRange2 = SourceRange2 + 2
'Paste 2
Sheets("Sheet7").Select
Range(DestinationRange).Select
ActiveSheet.Paste
DestinationRange = DestinationRange + 1
N = N + 1
Loop
I am trying to copy the data (C6:C78) from an excel spreadsheet Sheet1 and copy it to (C6:C78) in Sheet3. I then want to copy the data from (C6:C78) Sheet2 and copy it to the range (D6:D78) on Sheet3. I then want to copy (D6:D78) from Sheet1 to (E6:E78) on Sheet3. I would then like to copy (D6:D78) from Sheet2 to (F6:F78) on Sheet3 and the pattern continues. How would I increment only the column letter (sometimes the increment is by 1 and sometimes it is by 2) in SourceRange1, SourceRange2, & DestinationRange? The column letters would both need to be incremented such as (C6:C78) becomes (D6:D78) and (C6:C78) becomes (E6:E78). I hope my question makes sense...
Thanks in advance!! My code is listed below:
Dim N As Integer
Dim SourceRange1 As Range
Dim SourceRange2 As Range
Dim DestinationRange As Range
N = 0
'Set the starting copy ranges for sheet1 & sheet2
'Set the starting paste range for sheet3
SourceRange1 = "C6:C78"
SourceRange2 = "C6:C78"
DestinationRange = "C6:C78"
Do Until N = 13
'Cut the first column of data from sheet1 and paste it into the first column of data in sheet3
'Cut 1
'Set0,6,12,18,24,30,36,42,48,54,60,66,72
Sheets("Sheet1").Select
Range(SourceRange1).Select
Selection.Cut
SourceRange1 = SourceRange1 + 2
'Paste 1
Sheets("Sheet7").Select
Range(DestinationRange).Select
ActiveSheet.Paste
DestinationRange = DestinationRange + 1
'Cut the first column of data from sheet2 and paste it into the second column of data in sheet3
'Cut 2
'Set0,6,12,18,24,30,36,42,48,54,60,66,72
Sheets("Sheet2").Select
Range(SourceRange2).Select
Selection.Cut
SourceRange2 = SourceRange2 + 2
'Paste 2
Sheets("Sheet7").Select
Range(DestinationRange).Select
ActiveSheet.Paste
DestinationRange = DestinationRange + 1
'Cut the second "used" column of data from sheet1 and paste it into the third column of data in sheet3
'Cut 1
'Set2,8,14,20,26,32,38,44,50,56,62,68,74
Sheets("Sheet1").Select
Range(SourceRange1).Select
Selection.Cut
SourceRange1 = SourceRange1 + 1
'Paste 1
Sheets("Sheet7").Select
Range(DestinationRange).Select
ActiveSheet.Paste
DestinationRange = DestinationRange + 1
'Cut the second "used" column of data from sheet2 and paste it into the fourth column of data in sheet3... This pattern continues...
'Cut 2
'Set2,8,14,20,26,32,38,44,50,56,62,68,74
Sheets("Sheet2").Select
Range(SourceRange2).Select
Selection.Cut
SourceRange2 = SourceRange2 + 1
'Paste 2
Sheets("Sheet7").Select
Range(DestinationRange).Select
ActiveSheet.Paste
DestinationRange = DestinationRange + 1
'Cut 1
'Set3,9,15,21,27,33,39,45,51,57,63,69,75
Sheets("Sheet1").Select
Range(SourceRange1).Select
Selection.Cut
SourceRange1 = SourceRange1 + 1
'Paste 1
Sheets("Sheet7").Select
Range(DestinationRange).Select
ActiveSheet.Paste
DestinationRange = DestinationRange + 1
'Cut 2
'Set3,9,15,21,27,33,39,45,51,57,63,69,75
Sheets("Sheet2").Select
Range(SourceRange2).Select
Selection.Cut
SourceRange2 = SourceRange2 + 1
'Paste 2
Sheets("Sheet7").Select
Range(DestinationRange).Select
ActiveSheet.Paste
DestinationRange = DestinationRange + 1
'Cut 1
'Set4,10,16,22,28,34,40,46,52,58,64,70,76
Sheets("Sheet1").Select
Range(SourceRange1).Select
Selection.Cut
SourceRange1 = SourceRange1 + 2
'Paste 1
Sheets("Sheet7").Select
Range(DestinationRange).Select
ActiveSheet.Paste
DestinationRange = DestinationRange + 1
'Cut 2
'Set4,10,16,22,28,34,40,46,52,58,64,70,76
Sheets("Sheet2").Select
Range(SourceRange2).Select
Selection.Cut
SourceRange2 = SourceRange2 + 2
'Paste 2
Sheets("Sheet7").Select
Range(DestinationRange).Select
ActiveSheet.Paste
DestinationRange = DestinationRange + 1
N = N + 1
Loop