Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-19-2018, 10:16 PM
Josh1012 Josh1012 is offline Copy Data from different areas in a worksheet into a Table Windows 10 Copy Data from different areas in a worksheet into a Table Office 2010 64bit
Novice
Copy Data from different areas in a worksheet into a Table
 
Join Date: Sep 2018
Posts: 12
Josh1012 is on a distinguished road
Default Copy Data from different areas in a worksheet into a Table

Hi, I am currently trying to write a vba macro and assign it to a button so that it copies a few specific cells to a table. The table has 10 columns (this won't change) and 10 Rows. If I keep pressing the button I want it to copy the data to a different column that has not already been used. When the columns are full (All 10 columns have been used) and I press the button again, I want it to delete all the values in all columns and rows and then copy my data range to the first column again.


These are the the references to where I'm copying my data from:
Code:
Range ("C2:C4")
Range("E2:E4")
Range("E13:E16")

So I want the data to be copied in the first column which fills the first 10 rows in the first column. Then when I press the button again it copies the same data to the next column that hasn't already been used. I've set my table as:
Code:
Dim table As ListObject
Set table = ThisWorksheet.ListObjects("Table4")

The Table is located in:
Code:
Range("B25:K34")




I appreciate any help I can get
Reply With Quote
  #2  
Old 09-19-2018, 11:40 PM
Josh1012 Josh1012 is offline Copy Data from different areas in a worksheet into a Table Windows 10 Copy Data from different areas in a worksheet into a Table Office 2010 64bit
Novice
Copy Data from different areas in a worksheet into a Table
 
Join Date: Sep 2018
Posts: 12
Josh1012 is on a distinguished road
Default This is what I have come up with

So I haven't quite found out how to do this yet, but I've got something to get started with. I'm thinking of a long winded method of using 10 "IF" statements with an else statement which deletes all rows and columns if the very last column is filled. Here's an example of what I've got so far, but it doesn't include all 10 IF statements as they just repeat themselves.


Code:
Sub CopyInputData()
Dim table As ListObject, text As String


text = "Data 1"
Set table = ThisWorksheet.ListObjects("Table9")


'So this will copy all my data to the second column of my table as long as it's empty

With table.DataBodyRange
If table.DataBodyRange(, 2) = " " Then
text.Copy Destination:=ThisWorksheet.Cells(25,2)
Sheets("GerotorSelection").Range("C2:C4").Copy
table.DataBodyRange(1, 2).PasteSpecial Paste:=xlPasteValues
Sheets("GerotorSelection").Range("E2:E4").Copy
table.DataBodyRange(4, 2).PasteSpecial Paste:=xlPasteValues
Sheets("GerotorSelection").Range("E13:E16").Copy
table.DataBodyRange(7, 2).PasteSpecial Paste:=xlPasteValues
Reply With Quote
  #3  
Old 09-20-2018, 01:51 AM
NoSparks NoSparks is offline Copy Data from different areas in a worksheet into a Table Windows 7 64bit Copy Data from different areas in a worksheet into a Table Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 779
NoSparks has a spectacular aura aboutNoSparks has a spectacular aura aboutNoSparks has a spectacular aura about
Default

Try this
Code:
Sub TransferData()
Dim i As Long, oLo As ListObject

Set oLo = ActiveSheet.ListObjects("Table9")

For i = 1 To 10
    If WorksheetFunction.CountA(oLo.ListColumns(i).DataBodyRange) = 0 Then
        'listcolumn(i) is empty so use it
        Exit For
    End If
Next i
    
If i = 11 Then
    oLo.DataBodyRange.ClearContents
    i = 1
End If

oLo.DataBodyRange.Cells(1, i).Resize(3).Value = Sheets("GerotorSelection").Range("C2:C4").Value
oLo.DataBodyRange.Cells(4, i).Resize(3).Value = Sheets("GerotorSelection").Range("E2:E4").Value
oLo.DataBodyRange.Cells(7, i).Resize(4).Value = Sheets("GerotorSelection").Range("E13:E16").Value
    
End Sub
Reply With Quote
Reply

Tags
columns, copy cells, count

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
copy data table from pdf to excel DIMI Excel 1 07-10-2018 11:40 PM
Looking to copy select cells in table using dropdown list to paste to new table in another worksheet CaptainRetired Excel Programming 18 01-04-2018 07:22 PM
Table - Allow Spellcheck and tick boxes in certain areas only BarbW Word 10 03-09-2016 01:59 PM
Copy Data from different areas in a worksheet into a Table Appending unique data from one worksheet to existing data on another worksheet EdStockton Excel 1 08-06-2014 11:00 PM
Copy Data from different areas in a worksheet into a Table Cannot copy ->paste table structure but data pastes ok kangz Word 1 04-24-2012 04:05 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 09:21 AM.


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