Solved

Macro needs help with Copy

Posted on 2014-07-17
19
114 Views
Last Modified: 2014-07-18
Folks,
This is the code I'm using
Sub InsertNewYearConsolidated()
    Dim ws As Worksheet, rw As Long
    Set ws = Worksheets("Consolidated")
    ws.Select
    
    'Insert 12 rows above Totals and the spacer row
    rw = ws.Range("A:A").Find("Totals").Row
    ws.Rows(((rw - 1) & ":" & (rw + 10))).Insert Shift:=xlDown
    
    'copy the format from the first 12 rows (6 to 17)
    ws.Range("A6:U17").Copy
    ws.Range("A" & (rw - 1)).Select
    Selection.PasteSpecial Paste:=xlPasteFormats
   Ws.Range(“A6:A17”).Copy
   Selection.PasteSpecial Paste:=xlPasteValues    
   Application.CutCopyMode = False

    Range("A1").Select
End Sub

Open in new window

In adding lines 14 and 15 I'm getting this:
Wrong fillNot exactly what I am needing which is this:
correct fill
0
Comment
Question by:Frank Freese
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
19 Comments
 
LVL 13

Expert Comment

by:duncanb7
ID: 40202942
Could you send us your Excel workbook with that macro so that we can run it
at our side that assist to solve the issue quickly

Duncan
0
 

Author Comment

by:Frank Freese
ID: 40202972
Sure no problem. It is attached; On the Cost Allocation Main worksheet you will find a command button with the macro
named InsertNew YearConsolidated. I added to the original code
    ws.Range("A6:A17").Copy
    Selection.PasteSpecial Paste:=xlPasteValues

Open in new window

The rest of the code is correct.
Project-Cost-Allocation-Revised1.xlsm
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40202984
Sorry, ignore the previous writing, I could open it now and run it

Duncan
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:duncanb7
ID: 40203019
What is the result you want on Consolidated sheet after macro running. Could you put the data into sheet and send us in other file ?

Duncan
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40203095
Please try this code

Duncan
Sub InsertNewYearConsolidated()
    Dim ws As Worksheet, rw As Long
    Set ws = Worksheets("Consolidated")
    ws.Select
    
    'Insert 12 rows above Totals and the spacer row
    rw = ws.Range("A:A").Find("Totals").Row
    ws.Rows(((rw - 1) & ":" & (rw + 10))).Insert Shift:=xlDown
   ' ws.Range("B" & (rw - 1) & ":" & "B" & (rw + 10)).NumberFormat = "dd/mm/yyyy"
    'copy the format from the first 12 rows (6 to 17)
  '  ws.Range("A6:U17").Copy
    ws.Range("A" & (rw - 1) & ":" & "A" & (rw + 10)).Select
    ws.Range("A6:A17").Copy
   Selection.PasteSpecial Paste:=xlPasteValues
 
   
    Application.CutCopyMode = False

    Range("A1").Select
End Sub

Open in new window

0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40203137
Please see the attached file, and you delete the rows below row17, and then re-run marco

Duncan
junk2.xlsm
0
 

Author Comment

by:Frank Freese
ID: 40203202
Duncan,
This code maintains the row and column formatting needed.
Sub InsertNewYearConsolidated()
    Dim ws As Worksheet, rw As Long
    Set ws = Worksheets("Consolidated")
    ws.Select
    
    'Insert 12 rows above Totals and the spacer row
    rw = ws.Range("A:A").Find("Totals").Row
    ws.Rows(((rw - 1) & ":" & (rw + 10))).Insert Shift:=xlDown
    
    'copy the format from the first 12 rows (6 to 17)
    ws.Range("A6:U17").Copy
    ws.Range("A" & (rw - 1)).Select
    Selection.PasteSpecial Paste:=xlPasteFormats
   Application.CutCopyMode = False

    Range("A1").Select
End Sub

Open in new window

From that I get this
 correct fillThe above is correct.
What I am getting from junk2.xlsm looks like this:
incorrect fill
You give me the months Jan - Dec but not the formatting. When I'm adding to the worksheet I am also adding the months Jan - Dec in A18:A29. That's what's missing from the code snippet provided above --- adding Jan - Dec is my objective. I'm having some issues adding the year but I may post another question if I need to..
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40203206
based on my attached example to solve the rest of your issue

have a nice day

Duncan
0
 

Author Comment

by:Frank Freese
ID: 40203216
I'm not sure I understand your response. Are you saying that you have provided a solution?
thanks
Frank
0
 

Author Comment

by:Frank Freese
ID: 40203317
Actually, I was able to resolve this myself today. It truly was not as difficult of a fix that I though.
Thanks for your support
0
 
LVL 13

Accepted Solution

by:
duncanb7 earned 500 total points
ID: 40203324
I have already done the code for the image at  http://filedb.experts-exchange.com/incoming/2014/07_w29/861307/correctfill.jpg

Is it right ?

Duncan
0
 

Author Comment

by:Frank Freese
ID: 40205386
I've requested that this question be closed as follows:

Accepted answer: 0 points for fh_freese's comment #a40203317

for the following reason:

Duncan left me with the impression he had offered a solution, which unfortunately, was not the case. So I spent more time with the problem and saw how to do it myself.
I simply added this snippet:
    Range("A6:A17").Select
    Selection.Copy
    Range("A18:A29").Select
problem solved
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40203337
I'd also spent  a lot time to read your question in detail  and made the code even that is not matched to your need finally.

Anyway, glad to see you have solved your problem

Have a nice day

Duncan
0
 

Author Comment

by:Frank Freese
ID: 40203343
Duncan,
I tried everything you posted and displayed the results. Yes, you did add the months but the rest of the formatting was something I could not use. I closed this question when I discovered that all the new code that was needed was:
    Range("A6:A17").Select
    Selection.Copy
    Range("A18:A29").Select

Right before:
Range("A1").Select
this last line of code I had.
I personally don't collect points and if there was some way to give you partial I truly would
0
 

Author Comment

by:Frank Freese
ID: 40203352
Duncan,
You are good man and dedicated to helping us. I'll have more posts of this problem for sure - my client is taking me to on a Star Trek adventure - To boldly go....
Again,
Accept my grateful thanks and appreciation
Frank
0
 

Author Comment

by:Frank Freese
ID: 40205378
Duncan work on this and there is a value. I'm going to give him the points
0
 

Author Closing Comment

by:Frank Freese
ID: 40205387
Thanks Duncan!
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40205506
Thanks for your points

Have a nice day

Duncan
0
 

Author Comment

by:Frank Freese
ID: 40205516
My pleasure. You have a great day also.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question