+ Reply to Thread
Results 1 to 4 of 4

Copy and Paste Values from Select Columns

  1. #1
    Registered User
    Join Date
    05-03-2022
    Location
    Vancouver, BC
    MS-Off Ver
    O365
    Posts
    2

    Copy and Paste Values from Select Columns

    Hi all,

    Apologies if this type of thing has been asked before but I've created the following macro in a timesheet I've created for my work:

    Sub CompletedTime()
    Sheets("Timesheet").Select
    Range("A4:M53").Select
    Selection.Copy
    Sheets("Completed Time").Select
    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Cells.Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Completed Time").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Completed Time").Sort.SortFields.Add2 Key:=Range( _
    "B2:B1048576"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
    xlSortNormal
    With ActiveWorkbook.Worksheets("Completed Time").Sort
    .SetRange Range("A1:M1048576")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    Range("A2").Select
    End Sub

    As you can tell, this is my attempt at cleaning up (albeit slightly) a recorded macro that, originally, was meant to copy all data from a range of cells (that have formulas in them) from one worksheet and paste those values to another worksheet and then sort it. It's meant to keep a record of all the time I submit to our timekeeping software and it works fine. The only issue, other than having messy code, is that it will always copy from the entire range whether there is a value there or not. Would someone be able to suggest a way of only copying columns A to M of rows when the formula in column A is equal to a value, not just a formula?
    Attached Files Attached Files
    Last edited by jampher; 05-04-2022 at 11:16 AM.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Copy and Paste Values from Select Columns

    Hi Jampher and welcome to the forum,

    Instead of always going to row 53 on your Timesheet you could find the last row with a date in it in ColumnB. Something like:

    Dim LastBRow as double
    LastBRow = Cells(rows.count,"B").end(xlUp).row

    Then when you do your first copy you would do something like:

    Range("A4:M" & LastBRow).Select

    This would be my first step in learning VBA by recording and changing/improving/fixing your macro.
    Last edited by MarvinP; 05-03-2022 at 09:41 PM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,496

    Re: Copy and Paste Values from Select Columns

    In addition to MarvinP's suggestion.
    My preference to copy/paste values is so (prevent selecting as much as possible)
    You can change the sh1, sh2, lr and sh1Val to names that are meaningful to you.
    Please Login or Register  to view this content.
    In this
    Please Login or Register  to view this content.
    change the 1,048,576 to another last row calculation, for instance
    Please Login or Register  to view this content.
    Above line should be after the data has been copied into sh2
    or so
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    05-03-2022
    Location
    Vancouver, BC
    MS-Off Ver
    O365
    Posts
    2

    Re: Copy and Paste Values from Select Columns

    Thank you both! This was extremely helpful!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Select, copy and paste columns with InputBox
    By Ianmacros in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-02-2019, 04:05 PM
  2. [SOLVED] Copy and paste only values without worksheet select
    By chrismeeky in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-11-2018, 05:14 AM
  3. [SOLVED] Copy and paste values all cells w/o select?
    By franklin_m in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-29-2017, 09:17 AM
  4. How to Select and Copy/Paste Columns Based on Checkbox
    By krayon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-03-2016, 05:48 PM
  5. [SOLVED] Copy values and paste as values WITHOUT SELECT
    By brainzlp in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 10-29-2015, 07:24 AM
  6. Replies: 1
    Last Post: 05-14-2014, 12:52 AM
  7. find, select, copy paste values
    By gsrai31 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-26-2011, 01:59 PM

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