Previous Topic Next Topic

Macro to Copy and Paste

Solved/Closed
sholly64
Posts
4
Registration date
Tuesday August 16, 2011
Status
Member
Last seen
August 16, 2011
 - Aug 16, 2011 at 08:27 AM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 - Aug 16, 2011 at 12:39 PM
Hello,

I would greatly appreciate any assistance with creating an Excel macro that will copy information from cells A1, B11:D11, A13, B23:D23, etc... and paste that information to cells J1, K1:N1. Example:

A1 would be the team name
A2 thru A10 would be the list of players
B1 would be January
C1 would be February
D1 would be March
B2 thru D10 would be scores
A11 would be the word totals
B11:D11 would be the monthly totals

A13 would be the team name
A14 thru A22 would be the list of players
B14 thru D22 would be scores
A23 would be the word totals
B23:D23 would be monthly totals

The macro that I have come up with is:

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+q
'
Range("A1").Select
Selection.Copy
Range("J1").Select
ActiveSheet.Paste Link:=True
Application.CutCopyMode = False
Range("B11:D11").Select
Selection.Copy
Range("K1:N1").Select
ActiveSheet.Paste Link:=True
Application.CutCopyMode = False
Range("A13").Select
Selection.Copy
Range("J2").Select
ActiveSheet.Paste Link:=True
Application.CutCopyMode = False
Range("B23:D23").Select
Selection.Copy
Range("K2:N2").Select
ActiveSheet.Paste Link:=True
End Sub

This works okay but I have over 8,000 lines of text that need to be copied and pasted. Thanks in advance for your time and patience. :)

3 replies

Answer 1 / 3
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 769
Aug 16, 2011 at 10:35 AM
So why there is a jump between A1 and A13. Are you only copying every 12th row ?
0
Answer 2 / 3
sholly64
Posts
4
Registration date
Tuesday August 16, 2011
Status
Member
Last seen
August 16, 2011
Aug 16, 2011 at 10:50 AM
Thank you for the reply. Yes, that is what I would like to do.
0
Answer 3 / 3
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 769
Aug 16, 2011 at 11:05 AM
Try this
Assumption is that you want to
process rows in a chunk of 12
you want to quit if the cell in column A of the first cell of chunk (a1, a13, ...) is blank 

Sub doCopyRange() 

   Dim lRow             As Long 
   lRow = 1 
   Do While (Cells(lRow, 1) <> vbNullString) 
      Application.CutCopyMode = False 
      Cells(lRow, "A").Copy 
      Cells(lRow, "J").Paste Link:=True 
      Application.CutCopyMode = False 
      Range(Cells(lRow + 10, "B"), Cells(lRow, "D")).Copy 
      Range(Cells(lRow, "K"), Cells(lRow, "N")).Paste Link:=True 
      Application.CutCopyMode = False 
      lRow = lRow + 12 
   Loop 
End Sub



Note:
I am not sure if this will work
Range(Cells(lRow, "K"), Cells(lRow, "N")).Paste Link:=True
If it does not, try with
Range(Cells(lRow, "K"), Cells(lRow, "M")).Paste Link:=True

If it still does not work, please upload a sample file with sample data at some file sharing site and post back the link to the file back here
0
sholly64
Posts
4
Registration date
Tuesday August 16, 2011
Status
Member
Last seen
August 16, 2011
Aug 16, 2011 at 11:54 AM
Just tried it and got the following error: Run-time error '438': Object doesn't support this property or method. I clicked Debugg and the following line was highlighted: Cells(1Row, "J").Paste Link:=True
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 769
Aug 16, 2011 at 12:39 PM
please upload a sample file with sample data at some file sharing site and post back the link to the file back here
0