PDA

View Full Version : Solved: How to copy/paste below existing data



YellowLabPro
08-24-2006, 09:03 AM
I am copying into column B from two sheets placing the data from sheet 2 below the data from sheet 1. Currently, I am overwriting the first data w/ the second data.

Here are my lines of code:
ws1.Range("u5:u" & LastRowSrc).Copy
ws3.Range("b2").PasteSpecial Paste:=xlPasteValues

ws2.Range("a4:a" & LastRowSrc).Copy
ws3.Range("b2").PasteSpecial Paste:=xlPasteValues

How can I alter this to paste below w/ the second copy rather than replacing?

Thanks,

YLP

lucas
08-24-2006, 09:18 AM
Wonder why you don't attach an example so we don't have to recreate your workbook from scratch....?

mdmackillop
08-24-2006, 10:07 AM
You need to find the last cell, then offset by one row

ws3.Cells(Rows.Count, "B").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

YellowLabPro
08-24-2006, 10:14 AM
Hi Md,
Thanks. That does part of it. It actually performs the function, but only copies beginning at row 4 to row 52. There are actually 7,185 rows of data it should copy. Why is it only copying 48 rows?

YellowLabPro
08-24-2006, 10:19 AM
Lucas,
I will gladly post one if you like. MD has the right idea for me. I am just experiencing some difficulty copying all the data over in column A of the second worksheet.

YellowLabPro
08-24-2006, 10:29 AM
I found the problem.
There was a line of code that was referencing ws1
LastRowSrc = ws1.Cells(Rows.Count, 1).End(xlUp).Row

Thanks guys,

YLP

lucas
08-24-2006, 10:42 AM
glad you got it worked out. Malcolms good isn't he? I have to tinker with things to get them to work.

YellowLabPro
08-24-2006, 10:45 AM
Yes, very good. You might have to tinker, but I feel like everything is a full blown labotomy when I trying to get things to work.....:bug: :rotlaugh:

I will sure be glad when this stuff makes more sense to me...

Thanks...for keeping an eye for me

YLP

YellowLabPro
08-24-2006, 11:04 AM
Related Issue:
Attempting to fill in the term "Existing" Down. I get it to fill the first cell w/ the term, but not all the way down.
Column A gets filled in first w/ term "New Record"
ws3.Range(Cells(2, 1), Cells(ws3.Cells(Rows.Count, 2).End(xlUp).Row, 1)) = "New Record"

Then I want to fill in below w/ term "Existing"
ws3.Range(Cells(2, 1), Cells(ws3.Cells(Rows.Count, 2).End(xlUp).Row, 1)) = "Existing"

But only fills in the inital cell.

thanks,

YLP

mdmackillop
08-24-2006, 11:20 AM
Something to try:
I'm getting fed up with all this LastRow stuff, so I've put together the following function to assist. Option 5 needs watching the the rest work fine. Your own copy line would be written as the first sub.

Sub CopyData()
Rng(ws2.[A4], 1).Copy
End Sub

'Demo sub
Sub ShowData()
Dim i As Long
For i = 1 To 5
Rng(Sheets(1).[A4], i).Select
MsgBox Selection.Address
Next
End Sub

Function Rng(Cel As Range, Typ As Long) As Range
Select Case Typ
Case 1 'Contiguous data - Down
Set Rng = Range(Cel, Cel.End(xlDown))
Case 2 'Broken data - Down
Set Rng = Range(Cel, Cells(Rows.Count, Cel.Column).End(xlUp))
Case 3 'Contiguous data - Right
Set Rng = Range(Cel, Cel.End(xlToRight))
Case 4 'Broken data - Right
Set Rng = Range(Cel, Cells(Cel.Row, Columns.Count).End(xlToLeft))
Case 5 'To end of Current Region
Set Rng = Range(Cel, Cel.CurrentRegion.SpecialCells(11))
End Select
End Function

mdmackillop
08-24-2006, 11:31 AM
Without seeing where your data is placed, I don't know what your range should be returning.

jungix
08-24-2006, 11:35 AM
Related Issue:
Attempting to fill in the term "Existing" Down. I get it to fill the first cell w/ the term, but not all the way down.
Column A gets filled in first w/ term "New Record"
ws3.Range(Cells(2, 1), Cells(ws3.Cells(Rows.Count, 2).End(xlUp).Row, 1)) = "New Record"

Then I want to fill in below w/ term "Existing"
ws3.Range(Cells(2, 1), Cells(ws3.Cells(Rows.Count, 2).End(xlUp).Row, 1)) = "Existing"

But only fills in the inital cell.

thanks,

YLP

You're overwriting the last cell each time. You must add an offset to write below:

ws3.Range(Cells(2, 1), Cells(ws3.Cells(Rows.Count, 2).End(xlUp).Row+1, 1)) = "New Record"

YellowLabPro
08-24-2006, 11:52 AM
Sure MD,
I was trying to understand your code. Sorry MD, it is out of my comprehension level right now.
I dont mind the "LastRow" stuff. Just because right now for me, I at least comprehend what is going on. :help Your new code is probably very cool and much faster..... I am still walking around lost with this stuff for the present time.

Let me know if this is what you needed...

Here is the code and worksheet.... The line I am working on is Line 87 col. 1


Option Explicit
Sub Importer()
Dim LastRow As Long, LastRowSrc As Long, LastRowDst As Long, i As Long
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, C As Range
Dim rng1 As Range, rng2 As Range
Set ws1 = Workbooks("TGS Item Record Creator.xls").Sheets("Record Creator")
Set ws2 = Workbooks("MasterImportSheetWebstore.xls").Sheets("TGFF")
Set ws3 = Workbooks("TGSImporter.xls").Sheets("Update")
LastRowSrc = ws1.Cells(Rows.Count, 1).End(xlUp).Row
LastRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row
ws3.Range("A1:H1") = Array("Origin", "Item#", "Record Description", "Dept", "Cat", "Qty", "Cost", "Price")
Rows("1:1").HorizontalAlignment = xlCenter
Rows("1:1").Font.Bold = True
Cells.Columns.AutoFit
Rows("1:1").HorizontalAlignment = xlLeft
Rows("1:1").Font.Bold = True
ws3.UsedRange.Offset(1, 0).ClearContents
ws1.Range("u5:u" & LastRowSrc).Copy
ws3.Range("b2").PasteSpecial Paste:=xlPasteValues

ws1.Range("w5:w" & LastRowSrc).Copy
ws3.Range("c2").PasteSpecial Paste:=xlPasteValues

ws1.Range("ab5:ab" & LastRowSrc).Copy
ws3.Range("d2").PasteSpecial Paste:=xlPasteValues

ws1.Range("ac5:ac" & LastRowSrc).Copy
ws3.Range("e2").PasteSpecial Paste:=xlPasteValues

ws1.Range("Aa5:Aa" & LastRowSrc).Copy
ws3.Range("f2").PasteSpecial Paste:=xlPasteValues

ws1.Range("x5:x" & LastRowSrc).Copy
ws3.Range("g2").PasteSpecial Paste:=xlPasteValues

ws1.Range("z5:z" & LastRowSrc).Copy
ws3.Range("h2").PasteSpecial Paste:=xlPasteValues

ws3.Range(Cells(2, 1), Cells(ws3.Cells(Rows.Count, 2).End(xlUp).Row, 1)) = "New Record"
'--------------------------------------------------------------------------------------------
'Begin Import of Existing Records from MasterImportSheetWebstore.xls
LastRowSrc = ws2.Cells(Rows.Count, 1).End(xlUp).Row
ws2.Range("a4:a" & LastRowSrc).Copy
ws3.Cells(Rows.Count, "B").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

ws2.Range("d4:d" & LastRowSrc).Copy
ws3.Cells(Rows.Count, "c").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

ws2.Range("b4:b" & LastRowSrc).Copy
ws3.Cells(Rows.Count, "d").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

ws2.Range("c4:c" & LastRowSrc).Copy
ws3.Cells(Rows.Count, "e").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

ws2.Range("j4:j" & LastRowSrc).Copy
ws3.Cells(Rows.Count, "f").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

ws2.Range("f4:f" & LastRowSrc).Copy
ws3.Cells(Rows.Count, "g").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

ws2.Range("g4:g" & LastRowSrc).Copy
ws3.Cells(Rows.Count, "h").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

ws3.Cells(Rows.Count, "A").End(xlUp).Row , 1 = "Existing"
ws3.Range(Cells(2, 1), Cells(ws3.Cells(Rows.Count, 2).End(xlUp).Row, 1)) = "Existing"
ws3.Rows("1:1").Font.Bold = True
ws3.Columns("C:D").HorizontalAlignment = xlLeft
ws3.Cells.Columns.AutoFit
ws3.Rows("1:1").HorizontalAlignment = xlCenter
[A1].Activate
End Sub

mdmackillop
08-24-2006, 12:32 PM
I see how you got confused. Very convoluted with all these Cells within Cells

ws3.Range(Cells(Rows.Count, "A").End(xlUp).Offset(1), _
Cells(Cells(Rows.Count, 2).End(xlUp).Row, 1)) = "Existing"

mdmackillop
08-24-2006, 12:46 PM
An interesting little problem How to fill down as far as the end of the adjoining row. Here's a Sub to which you pass the first cell abd the Fill string. You can replace A6 with the usual xlUp type reference


Sub DoFill()
FillNext Range("A6"), "Existing"
End Sub

Sub FillNext(Cel As Range, Data As String)
Range(Cel, Cel.Offset(, 1).End(xlDown).Offset(, -1)) = Data
End Sub

YellowLabPro
08-24-2006, 12:48 PM
Daggonit you are good. I really appreciate it MD.

Thank you....
If you ever decide to teach this stuff, sign me up. I think that is the biggest problem, someone like me w/o any programming experience has no clue. I read about this stuff all the time, and it is a little better than when I started, but still no where near what I would say having any real level of skill or use. I learn by seeing how things work rather than the other way around.

Thanks again,

YLP

YellowLabPro
08-24-2006, 12:50 PM
This worked great, are you saying that my code might have an issue and to use the last Sub?

mdmackillop
08-24-2006, 02:40 PM
No problem with that part of your code.
The advantage of learning how to create and use small subs and functions is that they greatly simplify your code if you are repeating similar (if not identical) actions.
With my function in Post 10, it turns the selection of a populated part of a column into a few characters "Rng(ws2.[A4], 1).Select" , and I hate to think how often I've typed the xlDown/xlUp routines. If you can get the hang of passing values and returning function values, it's well worth the effort.