Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-04-2014, 12:56 PM
grexcelman grexcelman is offline Copying and pasting values with unequal data columns Windows 7 32bit Copying and pasting values with unequal data columns Office 2003
Novice
Copying and pasting values with unequal data columns
 
Join Date: Dec 2014
Posts: 21
grexcelman is on a distinguished road
Question Copying and pasting values with unequal data columns

I have data that has been filtered into a pivot table from multiple sources. This table consists of 3 columns, I will need to automate a process of copying this data from a range which can change dynamically in size depending on future entries, and I wish to paste into a separate consolidated two column range elsewhere in the worksheet. The issue I'm having is column A and Column B have labels which all correspond to figures in Column C. Column A starts in row 5 but will show a "(blank)" after several labels, this will change over time but always begin at 5, and once that "blank" label is found, the list will continue in column B in the following row. Again all data will remain constant in the third column, but I need to consolidated columns A and B correctly until column B data ends. Hopefully that's clear. Thanks in advance!
Reply With Quote
  #2  
Old 12-04-2014, 01:16 PM
charlesdh charlesdh is offline Copying and pasting values with unequal data columns Windows 7 32bit Copying and pasting values with unequal data columns Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

HI,

Can you attach a redacted copy so we can see what the data looks like.
Reply With Quote
  #3  
Old 12-04-2014, 02:29 PM
grexcelman grexcelman is offline Copying and pasting values with unequal data columns Windows 7 32bit Copying and pasting values with unequal data columns Office 2003
Novice
Copying and pasting values with unequal data columns
 
Join Date: Dec 2014
Posts: 21
grexcelman is on a distinguished road
Default

Here's a screen shot of its current form(Pivot table). and another shot of it after its been copy and pasted. I will need to sort it by value afterwards and this process will also get included in the macro.
Attached Images
File Type: jpg Pivot View.jpg (161.8 KB, 18 views)
File Type: jpg pasted view.jpg (145.0 KB, 17 views)
Reply With Quote
  #4  
Old 12-04-2014, 03:04 PM
charlesdh charlesdh is offline Copying and pasting values with unequal data columns Windows 7 32bit Copying and pasting values with unequal data columns Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

Hi,

Thanks for the images.
I'll make the assumption that the data that will be copied somewhere else in the worksheet will be deleted when you create the next Pivot Table. Is this correct?
Reply With Quote
  #5  
Old 12-04-2014, 03:09 PM
grexcelman grexcelman is offline Copying and pasting values with unequal data columns Windows 7 32bit Copying and pasting values with unequal data columns Office 2003
Novice
Copying and pasting values with unequal data columns
 
Join Date: Dec 2014
Posts: 21
grexcelman is on a distinguished road
Default

that's correct. Will be pasting values from the pivot table and whenever a new pivot table is created, I would rerun the macro and that would essentially paste over the last pasted data.
Reply With Quote
  #6  
Old 12-05-2014, 11:36 AM
charlesdh charlesdh is offline Copying and pasting values with unequal data columns Windows 7 32bit Copying and pasting values with unequal data columns Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

Hi,

Here's the code that I have. You can see if it works.
Copy to a module and test on sample data.
Code:
Sub Copy_PTData()
Application.ScreenUpdating = False
Dim i As Long
Dim LastRow As Long '' for column A lastrow
Dim Lrow As Long '' fo column E
LastRow = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Row
''' Clear the copy range for new input '''
ActiveSheet.Range("E2:F" & ActiveSheet.Range("E65536").End(xlUp).Row).ClearContents
For i = 5 To LastRow
    ''' get last last row of data in column E
    Lrow = ActiveSheet.Range("E65536").End(xlUp).Row + 1
    ''' now loop down column A and copy
    If Cells(i, 1).Text <> "" And Cells(i, 1).Text <> "(blank)" Then ''' not sure about this line
        Cells(Lrow, 5).Value = Cells(i, 1).Text
        Cells(Lrow, 6).Value = Cells(i, 3).Text
    Else '' column A is empty
        Cells(Lrow, 5).Value = Cells(i, 2).Text
        Cells(Lrow, 6).Value = Cells(i, 3).Text
    End If
    If i = LastRow Then
        Cells(Lrow, 6).Value = Cells(i, 3).Text
    End If
Next i
Application.ScreenUpdating = True
End Sub
Reply With Quote
Reply

Tags
copy and paste, loop, vba

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copying and pasting acount data into Excel as text will not reformat to a number or currency ncbergst Excel 3 09-04-2014 11:29 AM
Copying and pasting values with unequal data columns Prevent Copying and Pasting Jimmy Tsawo Excel 3 02-20-2014 03:54 AM
Copying and pasting values with unequal data columns Copying data from sheet with deleted columns creates blanks ZGreyArea Excel 1 11-20-2013 10:12 AM
Copying and pasting values with unequal data columns Copying and pasting from Excel Lorna B Word 1 03-20-2012 11:58 PM
macro for comparing data from 3 columns and pasting into another worksheet ashukla Excel 1 06-24-2009 05:01 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:43 AM.


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