Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-24-2021, 07:15 AM
Logit Logit is offline How To Copy/Paste Partial Row Windows 10 How To Copy/Paste Partial Row Office 2007
Expert
How To Copy/Paste Partial Row
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default How To Copy/Paste Partial Row

The following macro does everything it is designed for, EXCEPT the copy/paste portion. I am at a loss what correction/s to make.



The macro searches each sheet, specific column (either F or G), seeking any value greater than ZERO. If found, it should copy Cols B:F or B:G (depending on which column was searched) and paste those values to the appropriate worksheet.

Thank you for your assistance !

Code:
Option Explicit

Sub SampleCopy()
Dim ws As Worksheet
Dim c As Range
    
'On Error Resume Next

Application.ScreenUpdating = False

For Each ws In Worksheets
           
    Select Case ws.Name
        
        Case "In Stock", "To Order", "Sheet1"
            'If it's one of these sheets, do nothing
           
        Case Else
            
               For Each c In Range("F15:F" & Cells(Rows.Count, 6).End(xlUp).Row)
                  If c.Value >= 1 Then
                       Range("B:G").Copy Sheets("In Stock").Cells(Rows.Count, 2).End(xlUp)(1)  'Edit sheet name
                  End If
               Next c
            
               For Each c In Range("G15:G50" & Cells(Rows.Count, 7).End(xlUp).Row)
                   If c.Value >= 1 Then
                       Range("B:G").Copy Sheets("To Order").Cells(Rows.Count, 2).End(xlUp)(1)  'Edit sheet name
                   End If
               Next c
          
        End Select
    Next ws
End Sub
Download workbook :
https://www.amazon.com/clouddrive/sh...NbAeIVtC1Vuvld
Reply With Quote
  #2  
Old 04-24-2021, 10:03 AM
gibra's Avatar
gibra gibra is offline How To Copy/Paste Partial Row Windows 10 How To Copy/Paste Partial Row Office 2019
Novice
 
Join Date: Mar 2021
Posts: 8
gibra is on a distinguished road
Default

You must always indicate the sheet you intend to work on.
This instruction, does NOT select the range as you think, because it reads the active sheet which is not TCU but is Sheet1.
Code:
For Each c In Range("F15:F" & Cells(Rows.Count, 6).End(xlUp).Row)
so you should be write:
Code:
For Each c In ws.Range("F15:F" & Cells(ws.Rows.Count, 6).End(xlUp).Row)
and so on...
Reply With Quote
  #3  
Old 04-24-2021, 11:49 AM
Logit Logit is offline How To Copy/Paste Partial Row Windows 10 How To Copy/Paste Partial Row Office 2007
Expert
How To Copy/Paste Partial Row
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

No success. I believe my entire approach to this goal is flawed ???

I receive this error : attach
Attached Images
File Type: jpg Err.jpg (90.0 KB, 12 views)
Reply With Quote
  #4  
Old 04-24-2021, 10:04 PM
gibra's Avatar
gibra gibra is offline How To Copy/Paste Partial Row Windows 10 How To Copy/Paste Partial Row Office 2019
Novice
 
Join Date: Mar 2021
Posts: 8
gibra is on a distinguished road
Default

I don't know how you modified the code.
Attach the new modified file.
Reply With Quote
  #5  
Old 04-24-2021, 10:28 PM
Logit Logit is offline How To Copy/Paste Partial Row Windows 10 How To Copy/Paste Partial Row Office 2007
Expert
How To Copy/Paste Partial Row
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

As you can see I've tried another macro as well. The new macro actually appears to be copying and pasting something but I haven't been able to confirm exactly what because no data is appearing ?

I know, that sounds really stupid but it is going through the steps.
Attached Files
File Type: xlsm Inventory.xlsm (32.0 KB, 5 views)
Reply With Quote
  #6  
Old 04-25-2021, 02:43 AM
gibra's Avatar
gibra gibra is offline How To Copy/Paste Partial Row Windows 10 How To Copy/Paste Partial Row Office 2019
Novice
 
Join Date: Mar 2021
Posts: 8
gibra is on a distinguished road
Default

I honestly don't understand what you are trying to do, and why you want to paste the data into the Sheet1 sheet.

Your error is that you paste an entire row into a single cell.

It would seem that it is enough to remove EntireRow from the instruction, that is:
Code:
c.Copy Destination: = ws1.Cells (x, "B")
but the result is so strange that I doubt it is the desired one.
Reply With Quote
  #7  
Old 04-25-2021, 08:22 AM
Logit Logit is offline How To Copy/Paste Partial Row Windows 10 How To Copy/Paste Partial Row Office 2007
Expert
How To Copy/Paste Partial Row
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

Thank you so much for assisting. Here is the answer I've been seeking, provided for others:

Code:
Option Explicit

Sub SampleCopy()
Dim ws As Worksheet
Dim c As Range, rngToCopy As Range
    
'On Error Resume Next

Application.ScreenUpdating = False

For Each ws In Worksheets
           
    Select Case ws.Name
        
        Case "In Stock", "To Order", "Sheet1"
            'If it's one of these sheets, do nothing
           
        Case Else
                
               For Each c In ws.Range("F15:F" & ws.Cells(Rows.Count, 6).End(xlUp).Row)
                  If c.Value > 0 Then
                       Set rngToCopy = Intersect(ws.Columns("B:G"), c.EntireRow)
                       If Not rngToCopy Is Nothing Then
                            rngToCopy.Copy Sheets("In Stock").Cells(Rows.Count, 2).End(xlUp)(2).Resize(, rngToCopy.Columns.Count) 'Edit sheet name
                       End If
                  End If
               Next c
            
               For Each c In ws.Range("G15:G" & ws.Cells(Rows.Count, 7).End(xlUp).Row)
                   If c.Value > 0 Then
                       Set rngToCopy = Intersect(ws.Columns("B:G"), c.EntireRow)
                       If Not rngToCopy Is Nothing Then
                            rngToCopy.Copy Sheets("To Order").Cells(Rows.Count, 2).End(xlUp)(2).Resize(, rngToCopy.Columns.Count)  'Edit sheet name
                       End If
                   End If
               Next c
          
        End Select
    Next ws

    Application.ScreenUpdating = True
End Sub
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
cut/copy and paste won't paste DennisWG Word 1 03-13-2020 12:36 PM
VBA copy/paste Urraco Excel Programming 3 05-14-2018 03:59 AM
How To Copy/Paste Partial Row When I copy&paste a second copy appears that can't be edited makeo22 Word 3 04-26-2017 07:09 PM
How To Copy/Paste Partial Row Copy n Paste seimeinokigen Excel 2 04-16-2016 09:31 AM
How To Copy/Paste Partial Row Paste Special: Copy and Paste Formatting Only? tinfanide Word 6 03-06-2013 12:21 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:18 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft