Solved

Copy Row of Data

Posted on 2013-06-03
3
226 Views
Last Modified: 2013-06-03
I'm trying to figure out a way to copy my row of information to another worksheet in my workbook if there is text in Column A, and a date in Column B. Data will be in Column C to Q. I would like to have it LOOP so when a user clicks a button and the VBA code looks down Column A and Column B to see if there is any data it would then move the row of data in column A to column Q to the last available row of a worksheet called 'Detail'.
TransferData.xls
0
Comment
Question by:Southern_Gentleman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39215566
Pls try

Sub DoIt()

Dim shData As Worksheet
Dim shDetail As Worksheet

Set shData = Worksheets("Data")
Set shDetail = Worksheets("Detail")

On Error Resume Next
For IdxRow = shData.Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
    If WorksheetFunction.IsText(Cells(IdxRow, "A").Value) And IsDate(Cells(IdxRow, "B").Value) Then
        Application.CutCopyMode = False
        shData.Range(Range("A" & IdxRow), Range("Q" & IdxRow)).Cut _
            Destination:=shDetail.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        shData.Cells(IdxRow, 1).EntireRow.Delete Shift:=xlUp
    End If
Next
End Sub

Open in new window

EDIT Corrected Bug
Regards
0
 

Author Comment

by:Southern_Gentleman
ID: 39216750
Right on. Works great! Gonzo
0
 

Author Closing Comment

by:Southern_Gentleman
ID: 39216752
Awesom
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question