Copy & Paste based on Variable (Excel 2002)

Topic

New Reply

Questions and a problem: The questions are, do you want to copy all of the rows from the “data to copy & paste” to the appropriate areas of the “New Worksheet,” or do you want to copy just one particular company. If just one company, how will the code know which one? what do you want the code to do if there is not a matching company number on the “New Worksheet?” The problem is that the code in the workbook you uploaded is password protected, so we can’t test the code to do what you want.

Reply | Quote

Replies

Hello!
I need help as usual… my search in this forum found a few posts that may be similar, but because I’m not up on the code (yet!) I can’t tell if it will do what I need. I’ve attached a worksheet as an example… what I’m in need of doing is to transfer a large amount of data from one worksheet to another based on a variable. For example, there may be 10 lines out of 150 lines that need to be copied over to the next worksheet, however they need to go in a certain place in the new worksheet. The variable is the company number which is built into our department number… for example the company number is 75 and the data that needs to be copied has a department number of 75R&D. I’d love a macro that could search the data and find all lines that have a “75” in the left 2 characters, and then copy and paste those lines into the next worksheet… BUT it has to paste it in a spot where company 75’s lines need to be. Basically I have about 10 company numbers, which means I’ll have 10 areas on the new worksheet where the 10 separate sets of data need to be pasted… man, I hope my spreadsheet makes more sense?!?!?! Any help on this would be greatly appreciated!!
THANKS!
Lana

Reply | Quote

Hi Legare,
Sorry about that… for example purposes I had generalized a file that had a macro in it that someone else wrote, therefore the password. It wasn’t anything I wrote, so it had nothing to do with my copy & paste dilemma. I copied my example to a “clean” workbook and re-attached to the original question.
To answer the question in your reply, I would like to copy ALL of the rows from the “data to copy & paste” to the appropriate areas of the “New Worksheet”. One thing to keep in mind is that the data in the “data to copy & paste” worksheet may be 150 lines one month and 250 lines long the next, so it varies. I planned on setting up the 10 journal entry templates in the “New Worksheet” with plenty of space between each to accomodate the copying & pasting of the varying lengths of data from the “data to copy & paste” worksheet.
To answer your next question, there will always be a journal entry template for EVERY company, so we shouldn’t have a problem there. Also keep in mind that the first two digits (Left,2) in the department code (in the example 75R&D) is the company number, so I thought we could read the left two digits to determine which got moved and to where?!?! Does that make sense?
Thanks!
Lana

Reply | Quote

Does this do what you want?


Public Sub CopyData()
Dim oSrc As Worksheet, oDst As Worksheet
Dim I As Long, J As Long, K As Long, lDMax As Long, lSMax As Long
Dim strCpy As String
Set oSrc = Worksheets("data to copy & paste")
Set oDst = Worksheets("New Worksheet")
lDMax = oDst.Range("A65536").End(xlUp).Row - 1
lSMax = oSrc.Range("A65536").End(xlUp).Row - 1
For I = 0 To lDMax
If oDst.Range("B1").Offset(I, 0).Value = "Financial Division" Then
strCpy = oDst.Range("B1").Offset(I + 1, 0).Value
K = 0
For J = 0 To lSMax
If oSrc.Range("A1").Offset(J, 0).Value "" Then
If Left(oSrc.Range("A1").Offset(J, 0).Value, 2) = strCpy Then
oDst.Range("A1").Offset(I + 4 + K).Value = oSrc.Range("A1").Offset(J, 0).Value
oDst.Range("B1").Offset(I + 4 + K).Value = oSrc.Range("A1").Offset(J, 1).Value
oDst.Range("K1").Offset(I + 4 + K).Value = oSrc.Range("A1").Offset(J, 2).Value
K = K + 1
End If
End If
Next J
End If
Next I
End Sub


Reply | Quote

There is a reason you are a MVP… this is fantastic!
Thank you Legare!
Lana

Reply | Quote

Hi Legare… you were so kind to help me out with a great macro, and now I’m changing my mind on what I want it to do… I’m hoping to tweak it a bit further to automate it even more. Rest assured, I’ve learned so much from you and Hans in regards to Macros, however I’m unable (YET) to actually write the code, just a bit of tweaking at this point. Anyway, below is what I’d like to change around on the original macro that you had written for me… I’m hoping it is doable (if that is a word)… these days, I’m pretty confident that almost anything is possible as you guys & gals on Woody’s Lounge are brilliant!! Anyway, here is my wish list…

Please refer to the attached document…

*I’d like to copy & paste A7 and B7 to the New Worksheet A15 and B15
*The narrative in D7 to go over to G15 in the New Worksheet
*The dollar amount in cell C7 to go over to the New Worksheet K15

Then it would scan down and recognize A10 is also company 75 (as the first two digits of the Unit are the company number) and need to go over to the new worksheet on the line below the one mentioned above.

Then it would re-scan the data looking for the next incremental number (83 in this case), and copy all of the data in this worksheet over to a NEW journal entry template in the “New Worksheet” following the same concept in paragraph one above.

Legare has already created a macro to do this, however I’d like to ADD to it if possible…

Instead of having a journal entry template for each company already created in the “New Worksheet”, once the next company’s data is ready to be transferred (in this case company 83), I’d like to have the macro copy the “New Worksheet” range A11 thru I15 (as row 15 will have formula’s in it) and place these copied cells 3 rows AFTER the last row of the previous company’s data (in this case after company 75’s data).

Of Course then since row 15 was copied (due to the need of formulas in a few of those cells) and now contain some unit, nature, amount, & narrative data that pertain to the previous company number (A24,B24,&G24 in this case) need to be “cleared out” so that company 83’s data can be copied over to these cells.

To complicate it, I do not want any of the zero dollar amount lines to be copied over to the “New Worksheet”… they should just be ignored.

As always… thanks for the help!!
Lana

Reply | Quote

I read your post a little earlier this morning and I was hoping to have time to respond. Unfortunately, I am preparing to leave town for two weeks of vacation and, after taking a look, your request is going to take a little more time than I am going to have available before I leave. Hopefully, someone else will jump in and give you some help. If you don’t get what you need, give me another post after July 9th and I will see what I can do.

Reply | Quote

Try the attached version.

Reply | Quote

Thanks Hans… this works great! You’ll be proud to know that I am learning something from you!! I changed the code line below to be “not equal” to zero as opposed to “greater than” zero as some of the amounts will be a credit value, thus be less than zero… I neglected to include that tidbit in my wish list… anyway, I was totally impressed with myself that my change actually worked… soon I hope to have just a sliver of the knowledge you possess in regards to VBA and Excel. You are truly talented. Thanks again Hans!
Lana
If wshSource.Range(“C” & lngSourceRow) 0 Then

Reply | Quote

Hi Hans,
I’d like to change the code to copy and paste “values” as opposed to just paste… the data I’m copying has formula’s as it’s read from a pivot table for summarization purposes, so the current macro is sorting the columns and then the formula’s get copied to the target worksheet. In looking at the code I see a “copy destination” which I’m assuming is the “paste” part, but as you know I’m not trained on all the commands yet so I’m unsure how to get it to “paste values” instead. Of course, I see in the code that there are two copy destination functions, and I only want to change the movement of the Source data to the Target worksheet. The copying of A11:I14 in the target worksheet works fine, so no need to change that part of the code to “paste values”. If you could explain breifly in words what it is doing that would be great as well… I’ve studied it numerous times and I’ve caught on to part of it, but the paste part has eluded me.
Thanks as always!
Lana

Reply | Quote

One way would be to use Paste Special with the option to paste only values, but it is even simpler to set (the value of) the target cell to (the value of) the source cell: change the lines

wshSource.Range(“A” & lngSourceRow).Copy Destination:=wshTarget.Range(“A” & lngTargetRow)
wshSource.Range(“B” & lngSourceRow).Copy Destination:=wshTarget.Range(“B” & lngTargetRow)
wshSource.Range(“C” & lngSourceRow).Copy Destination:=wshTarget.Range(“K” & lngTargetRow)
wshSource.Range(“D” & lngSourceRow).Copy Destination:=wshTarget.Range(“G” & lngTargetRow)

to

wshTarget.Range(“A” & lngTargetRow) = wshSource.Range(“A” & lngSourceRow)
wshTarget.Range(“B” & lngTargetRow) = wshSource.Range(“B” & lngSourceRow)
wshTarget.Range(“K” & lngTargetRow) = wshSource.Range(“C” & lngSourceRow)
wshTarget.Range(“G” & lngTargetRow) = wshSource.Range(“D” & lngSourceRow)

Just for educational purposes: the code to Paste Special is a bit longer – you have to use separate instructions to copy and to paste:

wshSource.Range(“A” & lngSourceRow).Copy
wshTarget.Range(“A” & lngTargetRow).PasteSpecial Paste:=xlPasteValues
wshSource.Range(“B” & lngSourceRow).Copy
wshTarget.Range(“B” & lngTargetRow).PasteSpecial Paste:=xlPasteValues
wshSource.Range(“C” & lngSourceRow).Copy
wshTarget.Range(“K” & lngTargetRow).PasteSpecial Paste:=xlPasteValues
wshSource.Range(“D” & lngSourceRow).Copy
wshTarget.Range(“G” & lngTargetRow).PasteSpecial Paste:=xlPasteValues

Reply | Quote

Hi Hans… of course the code works, but my concept doesn’t, as the sort causes a problem with the data due to the formulas I have, therefore causing the data to be copied to the target worksheet incorrectly… anyway, I’ve resorted to recording the following macro

Columns(“F:I”).Select
Selection.Copy
Range(“A1”).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

to copy and paste values of the “source” data, then the sort and subsequent copying & pasting into the “target” worksheet won’t be a problem… I’m getting frustrated because I tried to copy the recorded macro and input it into your code and obviously putting it in the wrong place as it keeps giving me the “debug” error. Where / how can I place this recorded code into your code… it needs to be the first thing that happens… prior to the sort.
As another option, I also tried to “call” the recorded macro AND “call” your code via ONE command button, but apparently I’m only allowed ONE call per button??? Is that right?
Thanks for the help!
Lana

Reply | Quote

Below the instruction

Set wshSource = …

insert the lines

wshSource.Columns(“F:I”).Copy
wshSource.Range(“A1”).PasteSpecial Paste:=xlPasteValues

A button can execute only one macro, but that macro can call other macros if necessary:

Sub ButtonClick()
Call Macro1
Call Macro2
Call Macro3
End Sub

Reply | Quote

Hi Hans,
That worked… now the movement of the data to the target worksheet isn’t going over because “blank” rows are being sorted and are ending up at the top of the sort. Can the sort eliminate blank rows (reverse the sort order???, ascending vs. descending??)
Thanks,
Lana

Reply | Quote

Are you sure they are real blanks? Excel always sorts blank values last, whether you sort ascending or descending. Spaces, however, come after numbers but before all text when sorting ascending, and after text but before numbers when sorting descending.

Reply | Quote

Well, I do have the following formula
=IF(O3=0,””,IF(L3=””,F2,L3))
I’m now guessing the “” doesn’t qualify as blank??

Reply | Quote

> I’m now guessing the “” doesn’t qualify as blank?

That is correct. Excel only considers a cell to be blank if it contains neither a value nor a formula. Even if the formula results in an empty string, the cell is not really blank.
What happens if you sort the other way round?

Reply | Quote

It works if I sort it the other way around… can I change which way it sorts in the code?

Reply | Quote

Yep, by adding an argument to the Sort method: change

wshSource.Range(“A” & lngSourceRow).CurrentRegion.Sort _
Key1:=wshSource.Range(“A” & (lngSourceRow – 1)), Header:=xlYes

to

wshSource.Range(“A” & lngSourceRow).CurrentRegion.Sort _
Key1:=wshSource.Range(“A” & (lngSourceRow – 1)), Order1:=xlDescending, Header:=xlYes

Reply | Quote

Ahh… perfect!! And to prove that I am learning from you… I added more code to the end of the macro by copying your columns code to copy some blank columns over columns A thru D so that it gets “blanked out” after the data has been move over to the target worksheet… this way the next person doesn’t have to remember to “clear” the contents. It actually worked! Yahoo!
I’m learn’in!!
Thanks Hans!!
Lana

Reply | Quote

Great!

BTW, you don’t have to copy blank columns over A through D. Instead, you can use

wshSource.Range(“A:D”).Clear

or

wshSource.Range(“A:D”).ClearContents

The former will remove both cell contents and cell formatting, the latter will only remove cell contents.

Reply | Quote