Closed Thread
Results 1 to 2 of 2

Copy and paste macro

  1. #1
    Registered User
    Join Date
    08-15-2017
    Location
    Brisbane, Australia
    MS-Off Ver
    Office 365 ProPlus
    Posts
    15

    Copy and paste macro

    I have posted this also in the Macros forum but thought I might add it here as well just in case someone picks up on it

    I am relatively new to Excel but learning quickly thanks to this forum, and I am very new to macros. I Have a macro written (sourced as bits and pieces from other posts on here) which copies data from a calculation sheet and pastes it to the next blank line in a summary sheet. The summary sheet is set up as one week per row, for 52 weeks. Everything works except in 4 columns I have a running total/average at the bottom for the year. Obviously in those 4 columns the data gets pasted below the totals, but I want them on the next blank line above this.

    Sheets("Environmental calculation").Select
    Sheets("Environmental calculation").unprotect
    Range("B19:I19").Select
    Selection.Copy
    Sheets("Weekly observations").Select
    Sheets("Weekly observations").unprotect
    lMaxRows = Cells(Rows.Count, "G").End(xlUp).Row
    Range("G" & lMaxRows + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    This is the section from the macro that needs tweaking.. I need to copy B19-I19 from one sheet into next blank line in columns G-N on the weekly data sheet, but there is totals formulas in G55-J55 at the bottom of the years data.

    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

    I also have the protect line at the end of the whole macro to finish off, but is there any way to reprotect it with a password through the macro? I will need this as there will be other people entering this data occasionally and would not want to lose any previous data on the sheet.
    TIA

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Copy and paste macro

    Duplicate threads are not allowed (please read the forum rules). Continue in your original thread: https://www.excelforum.com/excel-pro...ine-macro.html
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 01-13-2016, 08:50 PM
  2. Copy paste - cute paste macro almost working
    By graiggoriz in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-27-2014, 12:33 PM
  3. Copy & paste in macro doesn't paste all cells
    By pltrapper in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-13-2013, 09:22 AM
  4. [SOLVED] Simple copy and paste macro- Paste special help needed.
    By hernancrespo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-20-2012, 07:02 AM
  5. [SOLVED] Macro to Find, copy, paste, find, copy, paste, find, copy, paste, u get the picture....
    By westsoldman in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-12-2012, 01:12 AM
  6. 2 Macro's: only vertical copy/paste action and copy-paste 14 columns to the right.
    By vdongen in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-08-2010, 10:34 AM
  7. Copy and Paste macro needs to paste to a changing cell reference
    By loulou in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-24-2005, 07:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1