Using a Macro to copy & Paste from one sheet to another. [Solved/Closed]

Report
Posts
3
Registration date
Monday December 15, 2014
Status
Member
Last seen
December 17, 2014
-
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
-
Hello,

Hi Guy's I hope you can help, I am using two sheets. one is spread across different rows
so when I click create list it copies everything to my list on my new worksheet,

Now what I want it to do is copy and paste to the next row, and so on because my first sheet is the initial one to complete and then it is cleared.

I'm New to these forums so please be patient with me. see the below

Sub Create_List()
'
' Create_List Macro
'

'
Sheets("Single SDR").Select
Range("C2").Select
Selection.Copy
Sheets("SDR list").Select
Range("F4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
Range("C3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("C4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
Range("C4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("D4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
Range("C5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("E4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
Range("C7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("B4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
Range("C12:F12").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("G4:J4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
Range("K8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("L4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
Range("M8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("M4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
Range("I13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("N4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
Range("K13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
Range("O4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
Range("L13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("P4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
Range("D19:H19").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("R4:V4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
Range("O18:O19").Select
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.UnMerge
Range("O18").Select
With Selection.Font
.Name = "Calibri"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("O19").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlHairline
End With
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("O18").Select
Selection.Copy
Sheets("SDR list").Select
Range("W4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
Range("I27").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("X4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
Range("I26").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("Y4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
Range("I24").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("AA4").Select
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
Sheets("Single SDR").Select
Range("I25").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("AB4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 41
Range("D58:E59").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("AC4").Select
Range("AB7").Select
Sheets("Single SDR").Select
Range("D64:E65").Select
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.UnMerge
Range("D58:E59").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.UnMerge
Range("D64").Select
Selection.Copy
Sheets("SDR list").Select
Range("AD4").Select
Sheets("Single SDR").Select
Range("J73").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("AE4").Select
Sheets("Single SDR").Select
ActiveWindow.SmallScroll Down:=-45
Range("I29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("AF4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
ActiveWindow.SmallScroll Down:=30
Range("D64").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("AD4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
ActiveWindow.SmallScroll Down:=15
Range("J73").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("AE4").Select
ActiveSheet.Paste
Range("B05").Select
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Sheets("Single SDR").Select
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1



End

7 replies

Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Marksman,

Looks like you've busy trying to work this one out!

To simplify and better understand exactly what you would like, please upload a sample work book. Please desensitise any confidential information.

Use a file sharing site such as DropBox to do this.

Regards,
vcoolio.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2885 users have said thank you to us this month

Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Mark,

Just letting you know that I haven't forgotten you. I'll be tackling your case again tonight when I get home from work. I went through it last night with a fine-tooth comb (well, fairly fine anyway!).

Just wondering, did you record the current macro with the macro recorder?

Regards,
vcoolio.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2885 users have said thank you to us this month

Posts
3
Registration date
Monday December 15, 2014
Status
Member
Last seen
December 17, 2014
1
Hi Coolio,

Yes mate I used the Macro recorder, it is a little messy, I will tidy it up a little : )
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2885 users have said thank you to us this month

Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Mark,

That's ok. Leave it as it is. Sorting through it for you. Will be in touch.

Cheers,
vcoolio.
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello again Mark,

Click on the following link to retrieve your file:-

https://www.dropbox.com/s/gpezz3vvxwvji8u/Marksman.xlsm?dl=0

I've re-arranged your code so now it transfers data as you wanted - row by row - to the SDR List.

I have made your existing button "Create List" the active cell also so, when you click on it, all the data will be transferred. Its in cell R2. If you ever move the button, you will need to change the cell reference in the code.

Also (and I hope that you don't mind), I had to re-arrange your form ("Single SDR") a little as there were many, many merged cells that created havoc with the code. There are no merged cells now. To trim the code down, I have removed the formatting code from the macro but both sheets are still formatted, and saved, as you had them originally. You can change and save the formatting any time you wish.

I have placed the code in Module 1 as it was not being used.

I hope its now all working as you wanted.

Kind regards,
vcoolio.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2885 users have said thank you to us this month

Hi Coolio,

Thank you very much, I have tidied it up now similar to the original, you were right
that merged cells were causing a problem and found that if you un-merged the titles
the macro would work.

Please see the link

https://www.dropbox.com/s/bs1oo8oom7dsgvt/Trim%20SDR-List%20Creator.xlsm?dl=0

Once again thanks very much, I have also learned a lot from your good work and can use it on other tasks.

Kindest Regards


Mark
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2885 users have said thank you to us this month

Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Mark,

Great tidy up work on the form! I knew you'd make that your first task. It looks great now. I was more interested in unmerging everything.

I'm actually still playing around with the code to try and make it more efficient. I'll let you know if it turns out any better.

Glad that I could help.

Cheers,
vcoolio.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2885 users have said thank you to us this month

Posts
3
Registration date
Monday December 15, 2014
Status
Member
Last seen
December 17, 2014
1
Hi Coolio,

Thanks for the quick reply buddy, please see the link to the file below

https://www.dropbox.com/s/zkn5n4v1g9wu7kr/Copy%20of%20SDR%20list%20test.xlsm?dl=0

Basically the first tab (SDR) is filled in, I click the Create file list and the second worksheet tab 2 is completed. then what I want to be able to do is everytime I create a new SDR and create the list it moves down to row 2 and so on.

I hope the link works ok, or do I need to do it a different way?


Many thanks


Mark