Previous Topic Next Topic

Copy and convert data

Solved/Closed
smcgrath3475 Posts 17 Registration date Wednesday December 13, 2017 Status Member Last seen January 23, 2018 - Updated on Jan 10, 2018 at 02:21 PM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Jan 23, 2018 at 08:26 PM
Hi, in advance I would like to thank everyone for reading my post and providing any assistance. I have an exported worksheet with 50 rows of data (30 columns). The results are Yes, No, NA, Blanks and dates; I want to copy all the data to a new worksheet and convert Yes to 1, No to 0 and NA to 9. I could use find/replace, but I need to repeat the process several times a day for different data sets. I didn't know if VBA or macro could copy and convert the data to a new worksheet with a single action (button). I appreciate your thoughts and time!

Thanks,
smcgrath3475

13 responses

Answer 1 / 13
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Dec 13, 2017 at 09:56 PM
Hello SMcgrath3475,

I'm not sure if the results that need to be replaced with new values are in the one column or spread out over a data set, but following is a code which may help:- 

Sub FRTest()

With Sheet1.UsedRange.Offset(1)
    
    .Replace What:="Yes", Replacement:=1, LookAt:=xlPart, MatchCase:=False
    .Replace What:="No", Replacement:=0, LookAt:=xlPart, MatchCase:=False
    .Replace What:="NA", Replacement:=9, LookAt:=xlPart, MatchCase:=False
    
End With

End Sub


This code finds the values"Yes", "No" and "NA" and replaces them with the values 1, 0 and 9.

At this point it doesn't copy the data set to a new sheet but it is just a starting point to see if this is the result that you are after. Following is the link to a small sample that I have prepared for you:-

http://ge.tt/3Vf9vin2

Click on the "RUN" button to see it work and let us know your thoughts.

I hope that this helps.

Cheerio,
vcoolio.
0
smcgrath3475 Posts 17 Registration date Wednesday December 13, 2017 Status Member Last seen January 23, 2018
Dec 14, 2017 at 01:09 PM
Thanks it works perfectly; I was able to code so it copies to a new worksheet and then uses your code to convert the data (leaving the original data intact). Can you please help with an issue I found. In column "AL" it copies over erroneous data (-42,000) that was exported from InfoPath to the original worksheet. I would like for it to only copy to the new worksheet if the number is >-100 as in some cases there might be a negative number. Here is the code I used to copy column "AL" over to the second worksheet.

lastRow = Sheets("Exported Data").Range("AL" & Rows.Count).End(xlUp).Row
Sheets("Conversion").Range("AE4:AE" & lastRow).Value = Sheets("Exported Data").Range("AL2:AL" & lastRow).Value

Any further assistance would be greatly appreciated!!

Thanks Again;
0
Answer 2 / 13
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Updated on Dec 14, 2017 at 06:55 PM
Good day Mr. McGrath,

With the following code:- 
Sub CopyStuff()

Application.ScreenUpdating = False

With Sheet1.[A1].CurrentRegion
           .AutoFilter 38, ">" & -100
           .Offset(1).Resize(, 38).Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
           .AutoFilter
End With

FRTest

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


all the relevant rows of data that you wish to be transferred over to the "Conversion" sheet based on the criteria ">-100" in Column AL will be transferred across leaving the remainder behind. The code then calls the FRTest sub to convert the data.

Both codes can be placed in the same module and assigned to a button. You only need to assign the "CopyStuff" code to the button in this situation.

You'll note that I've used the sheet codes (Sheet1 and Sheet2) to refer to the worksheets in the code above. This is the safest method to use in VBA coding so if, for some reason, the sheet names are changed in the future, the code will still work. Its a good habit to get in to.
To find the sheet codes of your worksheets, go to the VB Editor and over to the left in the Project Explorer you will see your sheets listed by name with the actual sheet code to the left of the name. Use those sheet codes in the macro above (should you need to change them to suit). You may also have to change the sheet code in the FRTest code to suit.

Test the code in a copy of your workbook first.

Should it all still need some tweaking, then upload a sample of your workbook (both sheets) to a free file sharing site such as GE.TT or Drop Box and then post the link to your file back here. If you have sensitive data in your file then please use dummy data for the sample.

I hope that this helps.

Cheerio,
vcoolio.
0
smcgrath3475 Posts 17 Registration date Wednesday December 13, 2017 Status Member Last seen January 23, 2018
Dec 20, 2017 at 09:09 AM
Thanks again for your hard work; I inserted the code and though it ran it still copied the -42,000 numbers. As I played with it I realized the columns I am having issues with are all based on another adjacent column that has a date. Thus, no date creates the -42,000 number (as this is an auto-calculate function in the original InfoPath form). So, is it possible to say only copy data from ws1, column "AL" to ws2, Column "AE" if ws1, Column "AK" isn't blank. I have three columns in total with this issue... Thanks for taking the time to help me as it's really appreciated!!
0
Answer 3 / 13
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Dec 20, 2017 at 03:54 PM
Hello Mr. McGrath,

We could try a couple of things.

Firstly, try the above code slightly modified as follows:- 

Sub CopyStuff()

Application.ScreenUpdating = False

With Sheet1.[A1].CurrentRegion
           .AutoFilter 38, ">" & -100
           .Offset(1).Resize(, 38).Copy
           Sheet2.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
           .AutoFilter
End With

FRTest

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


Secondly, based on your last post, try:- 

Sub CopyStuff()

Application.ScreenUpdating = False

With Sheet1.[A1].CurrentRegion
           .AutoFilter 37, "<>" & ""
           .Offset(1).Columns(38).Copy
           Sheet2.Range("AE" & Rows.Count).End(3)(2).PasteSpecial xlValues
           .AutoFilter
End With

FRTest

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


I hope that this helps.

Cheerio,
vcoolio.
0
smcgrath3475 Posts 17 Registration date Wednesday December 13, 2017 Status Member Last seen January 23, 2018
Dec 21, 2017 at 09:24 AM
Wow you are amazing!! I inserted your code and added my two other columns needing the same function. Two things: It copies the data to the new ws, but doesn't skip rows where it should be blank. Also one of the columns (40) is all blanks, but it still copies all the (-42,000) over. I played with it and if I insert a single date (test purposes) in column 40 it works fine...

With Sheet1.[A1].CurrentRegion
.AutoFilter 37, "<>" & ""
.Offset(1).Columns(38).Copy
Sheet2.Range("AE4").End(3)(2).PasteSpecial xlValues
.AutoFilter
.AutoFilter 40, "<>" & ""
.Offset(1).Columns(41).Copy
Sheet2.Range("AH4").End(3)(2).PasteSpecial xlValues
.AutoFilter
.AutoFilter 44, "<>" & ""
.Offset(1).Columns(45).Copy
Sheet2.Range("AL4").End(3)(2).PasteSpecial xlValues
.AutoFilter
End With

FRTest

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Sorry, didn't know how to format like your code above; Just to let you know I do try to resolve the issue by myself before I ask you; I really appreciate all you have done!!
0
Answer 4 / 13
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Dec 21, 2017 at 08:43 PM
Hello Mr. McGrath,

Good to know that you are making the effort to understand and learn.

I reckon it would be best if you could upload a sample of your workbook for me to have a look at and test with. I think that something else is afoot here!
Upload a sample of your workbook to a free file sharing site such as ge.tt or Drop Box and then post the link to your file back here. If you have sensitive data in your workbook then please use dummy data. Make sure that the sample is exactly the same as your actual workbook and please give a full explanation of inputs and expected results.

I'm sure, thereafter, we can sort this out.

Cheerio,
vcoolio.
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 13
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Updated on Dec 22, 2017 at 06:00 AM
Hello again Mr. McGrath,

Whilst you are preparing the sample workbook, here's another method that may work for you:- 

Sub CopyStuff2()

        Dim sht As Worksheet, lr As Long, i As Long, x As String, y As String
        Set sht = Sheet1
        x = sht.[AT1].Value
        y = sht.[AU1].Value
        If x = vbNullString Then Exit Sub
        If y = vbNullString Then Exit Sub
        
Application.ScreenUpdating = False
     
lr = sht.Range("AK" & Rows.Count).End(xlUp).Row
    
sht.Range(x & 1 & ":" & x & lr).AutoFilter 1, "<>" & ""
        sht.Range(x & 2 & ":" & x & lr).Offset(, 1).Copy
        Sheet2.Range(y & 2 & ":" & y & lr).End(3)(2).PasteSpecial xlValues
        sht.[A1].AutoFilter

sht.Select
sht.[AT1:AU1] = ""

Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "All done!", vbExclamation

End Sub


This code copy/pastes from the three source columns to the three destination columns. By selecting a source column from the drop downs in cell AT1 and a destination column in cell AU1and then clicking on a button, the code will copy/paste from and to the desired columns. The only downside I can see with this method is User error; i.e. selecting a source column and then selecting the wrong destination column or vice versa.

Following is the link to a small sample that I have prepared to demonstrate how the above code works:-

http://ge.tt/4hNARpn2

There are some notes for you in the sample. After selecting from the drop downs, click on the "RUN" button. Note that I have left some blank cells in the source columns.

Just a little more to confuse you!!

Cheerio,
vcoolio.
0
smcgrath3475 Posts 17 Registration date Wednesday December 13, 2017 Status Member Last seen January 23, 2018
Jan 8, 2018 at 09:49 AM
Happy post holidays and New Year; sorry for the late response as I was off on vacation. I have uploaded a test worksheet with test data. Please let me know if you need me to explain the issues I am still seeing when comparing the previous cell (date) before copying the adjacent cell to the second worksheet (need to include blanks, so data lines up like on sheet 1). Here is the link; I appreciate all your help...
https://www.dropbox.com/s/748ppx6iw7zdbgx/Test1%20conversion%20master.xlsm?dl=0
0
Answer 6 / 13
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Jan 8, 2018 at 10:19 PM
Hello Mr. McGrath,

I'm starting all over again from your opening post.

- All the data that you have in the sheet "Exported Data" you wish to copy over to sheet "Conversion".
- The data from "Exported Data" won't necessarily go to the exact same column in the "Conversion" sheet. For example, the data in Column D in "Exported Data" will go to Column B in the "Conversion" sheet or the data in Column E (Exported Data) will go to Column C (Conversion) etc. etc..
- The transferred data in the "Conversion" sheet needs to be aligned as per the "Exported Data" sheet. Hence any blanks need to be transferred over also.
- The find/replace then needs to be applied to the "Conversion" sheet.

Task complete.

I have noticed that, in the sample you have supplied, you have placed the codes in the Worksheet module. The codes are not Worksheet_Change events and, for them to work correctly, need to be placed in a standard module and assigned to a button (you have created a button in the "Conversion" sheet).

To test, I removed all the codes in the worksheet module and placed only the longer code (FRTest) in a standard module and assigned it to your button. It appeared to work as you would like, complete with the find/replace conversion. All blanks are transferred also with no sign of the -40,000 inputs.

The large code that you have appears to work well but can be trimmed for more efficiency.

Following is the link to your sample file:-

http://ge.tt/5EgVU1o2

I have placed the code in a standard module, cleared out the Worksheet module and assigned the code to the button in the "Conversion" sheet.
Click on the button to run the code and see if the result is as you wish.

Please also test this in a copy of your actual workbook to see if the end result is correct. If it is, then we can look at tidying up the code a little.

I hope that this helps.

Cheerio,
vcoolio.
0
smcgrath3475 Posts 17 Registration date Wednesday December 13, 2017 Status Member Last seen January 23, 2018
Jan 9, 2018 at 07:30 AM
Thanks for the hard work and explaining what you have done! The copy over feature is working as before the issue continues to be columns "Exported Data AL, AO, AS" is not copying over to "Conversion AE, AL, AO" based on the previous column (Exported Data AK, AN, AR) if a date is present then copy over the data; if blank copy over a blank and not the erroneous data (0 or -42,000). In the code you applied to the module you will see there is no code for the columns I mentioned as I was using separate sub (which copied over, but removed blank rows, so they didn't copy over to the correct row). thanks again
0
Answer 7 / 13
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Jan 10, 2018 at 07:11 AM
Hello Mr. McGrath,

I'm a little short on time this evening but try the following method (untested):- 


Sub CopyStuff()

Application.ScreenUpdating = False
           
With Sheet1.[A1].CurrentRegion
           .AutoFilter 37, "<>" & "", xlAnd, ""
           .Offset(1).Columns(38).Copy
           Sheet2.[AE4].PasteSpecial xlValues
           .AutoFilter
End With

With Sheet2.Range("AE3", Sheet2.Range("AE" & Sheet2.Rows.Count).End(xlUp))
           .AutoFilter 1, "<" & -100
           .Offset(1).ClearContents
           .AutoFilter
End With

FRTest

End Sub

Sub FRTest()
  
    Dim lastrow As Long

    lastrow = Sheets("Exported Data").Range("D" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("B4:B" & lastrow).Value = Sheets("Exported Data").Range("D2:D" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("E" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("C4:C" & lastrow).Value = Sheets("Exported Data").Range("E2:E" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("I" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("D4:D" & lastrow).Value = Sheets("Exported Data").Range("I2:I" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("J" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("E4:E" & lastrow).Value = Sheets("Exported Data").Range("J2:J" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("K" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("F4:F" & lastrow).Value = Sheets("Exported Data").Range("K2:K" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("L" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("G4:G" & lastrow).Value = Sheets("Exported Data").Range("L2:L" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("M" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("H4:H" & lastrow).Value = Sheets("Exported Data").Range("M2:M" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("N" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("I4:I" & lastrow).Value = Sheets("Exported Data").Range("N2:N" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("P" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("J4:J" & lastrow).Value = Sheets("Exported Data").Range("P2:P" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("Q" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("K4:K" & lastrow).Value = Sheets("Exported Data").Range("Q2:Q" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("R" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("L4:L" & lastrow).Value = Sheets("Exported Data").Range("R2:R" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("S" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("M4:M" & lastrow).Value = Sheets("Exported Data").Range("S2:S" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("T" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("N4:N" & lastrow).Value = Sheets("Exported Data").Range("T2:T" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("U" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("O4:O" & lastrow).Value = Sheets("Exported Data").Range("U2:U" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("V" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("P4:P" & lastrow).Value = Sheets("Exported Data").Range("V2:V" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("W" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("Q4:Q" & lastrow).Value = Sheets("Exported Data").Range("W2:W" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("X" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("R4:R" & lastrow).Value = Sheets("Exported Data").Range("X2:X" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("Y" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("S4:S" & lastrow).Value = Sheets("Exported Data").Range("Y2:Y" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("Z" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("T4:T" & lastrow).Value = Sheets("Exported Data").Range("Z2:Z" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AA" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("U4:U" & lastrow).Value = Sheets("Exported Data").Range("AA2:AA" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AC" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("V4:V" & lastrow).Value = Sheets("Exported Data").Range("AC2:AC" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AD" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("W4:W" & lastrow).Value = Sheets("Exported Data").Range("AD2:AD" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AE" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("X4:X" & lastrow).Value = Sheets("Exported Data").Range("AE2:AE" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AF" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("Y4:Y" & lastrow).Value = Sheets("Exported Data").Range("AF2:AF" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AG" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("Z4:Z" & lastrow).Value = Sheets("Exported Data").Range("AG2:AG" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AH" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AA4:AA" & lastrow).Value = Sheets("Exported Data").Range("AH2:AH" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AI" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AB4:AB" & lastrow).Value = Sheets("Exported Data").Range("AI2:AI51" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AJ" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AC4:AC" & lastrow).Value = Sheets("Exported Data").Range("AJ2:AJ" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AK" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AD4:AD" & lastrow).Value = Sheets("Exported Data").Range("AK2:AK" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AM" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AF4:AF" & lastrow).Value = Sheets("Exported Data").Range("AM2:AM" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AN" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AG4:AG" & lastrow).Value = Sheets("Exported Data").Range("AN2:AN" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AP" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AI4:AI" & lastrow).Value = Sheets("Exported Data").Range("AP2:AP" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AQ" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AJ4:AJ" & lastrow).Value = Sheets("Exported Data").Range("AQ2:AQ" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AR" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AK4:AK" & lastrow).Value = Sheets("Exported Data").Range("AR2:AR" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AT" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AM4:AM" & lastrow).Value = Sheets("Exported Data").Range("AT2:AT" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AU" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AN4:AN" & lastrow).Value = Sheets("Exported Data").Range("AU2:AU" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AV" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AO4:AO" & lastrow).Value = Sheets("Exported Data").Range("AV2:AV" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AW" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AP4:AP" & lastrow).Value = Sheets("Exported Data").Range("AW2:AW" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AX" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AQ4:AQ" & lastrow).Value = Sheets("Exported Data").Range("AX2:AX" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AY" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AR4:AR" & lastrow).Value = Sheets("Exported Data").Range("AY2:AY" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AZ" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AS4:AS" & lastrow).Value = Sheets("Exported Data").Range("AZ2:AZ" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("BA" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AT4:AT" & lastrow).Value = Sheets("Exported Data").Range("BA2:BA" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("BB" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AU4:AU" & lastrow).Value = Sheets("Exported Data").Range("BB2:BB" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("BC" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AV4:AV" & lastrow).Value = Sheets("Exported Data").Range("BC2:BC" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("BD" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AW4:AW" & lastrow).Value = Sheets("Exported Data").Range("BD2:BD" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("BE" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AX4:AX" & lastrow).Value = Sheets("Exported Data").Range("BE2:BE" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("BF" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AY4:AY" & lastrow).Value = Sheets("Exported Data").Range("BF2:BF" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("BG" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AZ4:AZ" & lastrow).Value = Sheets("Exported Data").Range("BG2:BG" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("BH" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("BA4:BA" & lastrow).Value = Sheets("Exported Data").Range("BH2:BH" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("BI" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("BB4:BB" & lastrow).Value = Sheets("Exported Data").Range("BI2:BI" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("BJ" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("BC4:BC" & lastrow).Value = Sheets("Exported Data").Range("BJ2:BJ" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("BK" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("BD4:BD" & lastrow).Value = Sheets("Exported Data").Range("BK2:BK" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("BL" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("BE4:BE" & lastrow).Value = Sheets("Exported Data").Range("BL2:BL" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("BM" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("BF4:BF" & lastrow).Value = Sheets("Exported Data").Range("BM2:BM" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("BN" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("BG4:BG" & lastrow).Value = Sheets("Exported Data").Range("BN2:BN" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("BO" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("BH4:BH" & lastrow).Value = Sheets("Exported Data").Range("BO2:BO" & lastrow).Value
     
    
    ActiveSheet.Range("B2:BH53").Replace What:="Yes", Replacement:=1, LookAt:=xlPart, MatchCase:=False
    ActiveSheet.Range("B2:BH53").Replace What:="No", Replacement:=0, LookAt:=xlPart, MatchCase:=False
    ActiveSheet.Range("B2:BH53").Replace What:="NA", Replacement:=9, LookAt:=xlPart, MatchCase:=False
     

End Sub


I've added to the CopyStuff code just to test on Column AE in the "Conversion" sheet. It should work as you wish but test it in a copy of your actual workbook.

You can see that I've placed it above the larger code that you already have (FRTest). The CopyStuff code then calls the FRTest code.

If this works for you then you should have the idea of how to add some more code to the CopyStuff macro to take care of the other two columns. Basically, just copy/paste the two With statements in the macro directly below the very last End With and change the column references.

I hope that this helps.

Cheerio,
vcoolio.
0
smcgrath3475 Posts 17 Registration date Wednesday December 13, 2017 Status Member Last seen January 23, 2018
Jan 10, 2018 at 08:53 AM
You are incredible, thanks so much for all your hard work!! I copied and added the other columns to the code... the only issue I found was that along with erroneous -42, 000 numbers their are erroneous zero's that the InfoPath database added for auto-calculations (when there was nothing to calculate)... so if I understand the code we are copying everything over to the sheet2 then filtering and removing anything less than -100. Example from sheet1 I have column AN with no dates, but AO has zeros in every cell. When I run the macro it still copies all the zeros over to Sheet2... but there will be times zero is a valid answer. Again, I can't express how I appreciate your help!!

Scott
0
Answer 8 / 13
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Updated on Jan 11, 2018 at 05:55 AM
Hello Scott,

Your interpretation is correct.

To take care of those annoying discrepancies, it may be a case of just re-positioning where the FRTest code is called from. So, try it as follows:- 


Sub CopyStuff()

FRTest

Application.ScreenUpdating = False
           
With Sheet1.[A1].CurrentRegion
           .AutoFilter 37, "<>" & "", xlAnd, ""
           .Offset(1).Columns(38).Copy
           Sheet2.[AE4].PasteSpecial xlValues
           .AutoFilter
End With

With Sheet2.Range("AE3", Sheet2.Range("AE" & Sheet2.Rows.Count).End(xlUp))
           .AutoFilter 1, "<" & -100
           .Offset(1).ClearContents
           .AutoFilter
End With

With Sheet1.[A1].CurrentRegion
           .AutoFilter 40, "<>" & "", xlAnd, ""
           .Offset(1).Columns(41).Copy
           Sheet2.[AL4].PasteSpecial xlValues
           .AutoFilter
End With

With Sheet2.Range("AL3", Sheet2.Range("AL" & Sheet2.Rows.Count).End(xlUp))
           .AutoFilter 1, "<" & -100
           .Offset(1).ClearContents
           .AutoFilter
End With


With Sheet1.[A1].CurrentRegion
           .AutoFilter 44, "<>" & "", xlAnd, ""
           .Offset(1).Columns(45).Copy
           Sheet2.[AO4].PasteSpecial xlValues
           .AutoFilter
End With

With Sheet2.Range("AO3", Sheet2.Range("AO" & Sheet2.Rows.Count).End(xlUp))
           .AutoFilter 1, "<" & -100
           .Offset(1).ClearContents
           .AutoFilter
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Sub FRTest()
  
    Dim lastrow As Long

    lastrow = Sheets("Exported Data").Range("D" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("B4:B" & lastrow).Value = Sheets("Exported Data").Range("D2:D" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("E" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("C4:C" & lastrow).Value = Sheets("Exported Data").Range("E2:E" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("I" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("D4:D" & lastrow).Value = Sheets("Exported Data").Range("I2:I" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("J" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("E4:E" & lastrow).Value = Sheets("Exported Data").Range("J2:J" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("K" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("F4:F" & lastrow).Value = Sheets("Exported Data").Range("K2:K" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("L" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("G4:G" & lastrow).Value = Sheets("Exported Data").Range("L2:L" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("M" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("H4:H" & lastrow).Value = Sheets("Exported Data").Range("M2:M" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("N" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("I4:I" & lastrow).Value = Sheets("Exported Data").Range("N2:N" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("P" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("J4:J" & lastrow).Value = Sheets("Exported Data").Range("P2:P" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("Q" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("K4:K" & lastrow).Value = Sheets("Exported Data").Range("Q2:Q" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("R" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("L4:L" & lastrow).Value = Sheets("Exported Data").Range("R2:R" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("S" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("M4:M" & lastrow).Value = Sheets("Exported Data").Range("S2:S" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("T" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("N4:N" & lastrow).Value = Sheets("Exported Data").Range("T2:T" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("U" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("O4:O" & lastrow).Value = Sheets("Exported Data").Range("U2:U" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("V" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("P4:P" & lastrow).Value = Sheets("Exported Data").Range("V2:V" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("W" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("Q4:Q" & lastrow).Value = Sheets("Exported Data").Range("W2:W" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("X" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("R4:R" & lastrow).Value = Sheets("Exported Data").Range("X2:X" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("Y" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("S4:S" & lastrow).Value = Sheets("Exported Data").Range("Y2:Y" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("Z" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("T4:T" & lastrow).Value = Sheets("Exported Data").Range("Z2:Z" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AA" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("U4:U" & lastrow).Value = Sheets("Exported Data").Range("AA2:AA" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AC" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("V4:V" & lastrow).Value = Sheets("Exported Data").Range("AC2:AC" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AD" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("W4:W" & lastrow).Value = Sheets("Exported Data").Range("AD2:AD" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AE" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("X4:X" & lastrow).Value = Sheets("Exported Data").Range("AE2:AE" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AF" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("Y4:Y" & lastrow).Value = Sheets("Exported Data").Range("AF2:AF" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AG" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("Z4:Z" & lastrow).Value = Sheets("Exported Data").Range("AG2:AG" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AH" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AA4:AA" & lastrow).Value = Sheets("Exported Data").Range("AH2:AH" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AI" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AB4:AB" & lastrow).Value = Sheets("Exported Data").Range("AI2:AI51" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AJ" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AC4:AC" & lastrow).Value = Sheets("Exported Data").Range("AJ2:AJ" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AK" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AD4:AD" & lastrow).Value = Sheets("Exported Data").Range("AK2:AK" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AM" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AF4:AF" & lastrow).Value = Sheets("Exported Data").Range("AM2:AM" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AN" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AG4:AG" & lastrow).Value = Sheets("Exported Data").Range("AN2:AN" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AP" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AI4:AI" & lastrow).Value = Sheets("Exported Data").Range("AP2:AP" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AQ" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AJ4:AJ" & lastrow).Value = Sheets("Exported Data").Range("AQ2:AQ" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AR" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AK4:AK" & lastrow).Value = Sheets("Exported Data").Range("AR2:AR" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AT" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AM4:AM" & lastrow).Value = Sheets("Exported Data").Range("AT2:AT" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AU" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AN4:AN" & lastrow).Value = Sheets("Exported Data").Range("AU2:AU" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AV" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AO4:AO" & lastrow).Value = Sheets("Exported Data").Range("AV2:AV" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AW" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AP4:AP" & lastrow).Value = Sheets("Exported Data").Range("AW2:AW" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AX" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AQ4:AQ" & lastrow).Value = Sheets("Exported Data").Range("AX2:AX" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AY" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AR4:AR" & lastrow).Value = Sheets("Exported Data").Range("AY2:AY" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("AZ" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AS4:AS" & lastrow).Value = Sheets("Exported Data").Range("AZ2:AZ" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("BA" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AT4:AT" & lastrow).Value = Sheets("Exported Data").Range("BA2:BA" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("BB" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AU4:AU" & lastrow).Value = Sheets("Exported Data").Range("BB2:BB" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("BC" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AV4:AV" & lastrow).Value = Sheets("Exported Data").Range("BC2:BC" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("BD" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AW4:AW" & lastrow).Value = Sheets("Exported Data").Range("BD2:BD" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("BE" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AX4:AX" & lastrow).Value = Sheets("Exported Data").Range("BE2:BE" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("BF" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AY4:AY" & lastrow).Value = Sheets("Exported Data").Range("BF2:BF" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("BG" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AZ4:AZ" & lastrow).Value = Sheets("Exported Data").Range("BG2:BG" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("BH" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("BA4:BA" & lastrow).Value = Sheets("Exported Data").Range("BH2:BH" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("BI" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("BB4:BB" & lastrow).Value = Sheets("Exported Data").Range("BI2:BI" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("BJ" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("BC4:BC" & lastrow).Value = Sheets("Exported Data").Range("BJ2:BJ" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("BK" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("BD4:BD" & lastrow).Value = Sheets("Exported Data").Range("BK2:BK" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("BL" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("BE4:BE" & lastrow).Value = Sheets("Exported Data").Range("BL2:BL" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("BM" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("BF4:BF" & lastrow).Value = Sheets("Exported Data").Range("BM2:BM" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("BN" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("BG4:BG" & lastrow).Value = Sheets("Exported Data").Range("BN2:BN" & lastrow).Value
    lastrow = Sheets("Exported Data").Range("BO" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("BH4:BH" & lastrow).Value = Sheets("Exported Data").Range("BO2:BO" & lastrow).Value
     
    
    ActiveSheet.Range("B2:BH53").Replace What:="Yes", Replacement:=1, LookAt:=xlPart, MatchCase:=False
    ActiveSheet.Range("B2:BH53").Replace What:="No", Replacement:=0, LookAt:=xlPart, MatchCase:=False
    ActiveSheet.Range("B2:BH53").Replace What:="NA", Replacement:=9, LookAt:=xlPart, MatchCase:=False
     

End Sub


As you can see, the FRTest code is now called immediately upon execution of the CopyStuff code instead of from the end of the CopyStuff code. Hence, you will need to only assign the CopyStuff code to the button.

I've attached your sample workbook with the full code implemented at the following link:-

http://ge.tt/5JPdQ3o2

Click on the button to see if it does the deed.

I was also wondering, is it necessary to have the source data transferred into the differing columns in the destination sheet?
I was thinking that if the "Conversion" sheet was set up exactly like the "Exported Data" sheet, the whole process could be done with many fewer lines of code than is currently being used. The entire data set could be transferred over as is (going to the exact same columns in the "Conversion" sheet) with a smaller, more efficient code doing the whole task that is currently being done with the large code(s).
Just wondering............

Anyway, I hope that the above helps.

Cheerio,
vcoolio.

P.S. On perusing your large macro (FRTest), I have found a discrepancy which may have been the cause of the zero (0) issue.

This line of code in your macro:- 
 Sheets("Conversion").Range("AO4:AO" & lastrow).Value = Sheets("Exported Data").Range("AV2:AV" & lastrow).Value


is taking data from Column AV (Exported Data) to Column AO (Conversion). Hence all the zeros(0) in Column AO (Conversion).

Now, based on your post #11:- 

 The copy over feature is working as before the issue continues to be columns "Exported Data AL, AO, AS" is not copying over to "Conversion AE, AL, AO" based on the previous column (Exported Data AK, AN, AR) if a date is present then copy over the data; if blank copy over a blank and not the erroneous data (0 or -42,000).


you are saying that Column AO (Conversion) is being populated from Column AS (Exported Data) based on the values in Column AR (Exported Data). Herein lies the conflict.

Another discrepancy:-
In your post #6, you attempted the following:- 
.AutoFilter 44, "<>" & ""
.Offset(1).Columns(45).Copy
Sheet2.Range("AL4").End(3)(2).PasteSpecial xlValues
.AutoFilter
End With

which shows the data being populated in Column AL in the Conversion sheet but, again based on your post #11, Column AL (Conversion) is being populated from Column AO (Exported Data) based on values in Column AN (Exported Data).

This part of the code that I supplied:- 
With Sheet1.[A1].CurrentRegion
           .AutoFilter 44, "<>" & "", xlAnd, ""
           .Offset(1).Columns(45).Copy
           Sheet2.[AO4].PasteSpecial xlValues
           .AutoFilter
End With


is basically re-instating the zeros and the -42000 values but this part:- 

With Sheet2.Range("AO3", Sheet2.Range("AO" & Sheet2.Rows.Count).End(xlUp))
           .AutoFilter 1, "<" & -100
           .Offset(1).ClearContents
           .AutoFilter
End With


rectifies the situation.

Thus it may be better to remove these lines of code:- 

lastrow = Sheets("Exported Data").Range("AV" & Rows.Count).End(xlUp).Row
    Sheets("Conversion").Range("AO4:AO" & lastrow).Value = Sheets("Exported Data").Range("AV2:AV" & lastrow).Value

and it would be best if you could check that all the cell/column references are correct and as you want them.
0
smcgrath3475 Posts 17 Registration date Wednesday December 13, 2017 Status Member Last seen January 23, 2018
Jan 15, 2018 at 01:43 PM
I can't stop thanking you for all the work you have done! Yes, it would be easier if all the data copied over in order, but that is not possible as questions are eliminated from the statistical analysis, but remain on the form (it's an internal process between departments). That being said, I color coded the columns from the exported data to the conversion sheet. I believe my comments back in earlier posts are now longer valid. Presently (Exported data AL, AO, AS, AV) copies to (Conversion AE, AH, AL, AO)... the first two columns seem to work, the third has a few ghostly zeros and the last copies all zeros though no dates in the previous column. I appreciate everything you have done!! Thanks Again..

https://www.dropbox.com/s/9q3zcbq6fj71tj0/Test2%20conversion%20master.xlsm?dl=0
0
Answer 9 / 13
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Jan 16, 2018 at 01:21 AM
Hello Scott,

So you've changed the parameters?

The "ghostly" zeros appear because you originally specified that values <-100 were not required. Hence, anything greater than -100 will appear.

If you don't want the zeros to appear, then change the following line:- 

.AutoFilter 1, "<" & -100


to 
.AutoFilter 1, "<=" & 0


in each of the "With Sheet2................." parts of the code.

This will prevent zeros from appearing.

Cheerio,
vcoolio.
0
smcgrath3475 Posts 17 Registration date Wednesday December 13, 2017 Status Member Last seen January 23, 2018
Jan 16, 2018 at 07:47 AM
Thanks for the quick response and all your hard work. I updated the code and I'm still getting zeros to copy over when there is no date in the previous column. The columns /cells I'm trying to copy over are auto calculations, so the number is only valid if there is a date in the previous column (result could be a negative number, 0, or a positive number) . Is there a way (if statement) to only copy the cell if the previous cell has text (date); else leave blank and repeat? Again I can't thank you enough for all your help.
Scott
0
smcgrath3475 Posts 17 Registration date Wednesday December 13, 2017 Status Member Last seen January 23, 2018
Jan 16, 2018 at 08:09 AM
Disregard my comment on the zeros still copying over; I saved and reopened and now no zeros copy over in the defined columns. In my previous response I state that there are valid zeros that should copy over... thanks for your patience!

Scott
0
smcgrath3475 Posts 17 Registration date Wednesday December 13, 2017 Status Member Last seen January 23, 2018
Jan 16, 2018 at 09:33 AM
Hello, I tried the following code and it only copies when there is a date in the previous column/cell like I want, but I can't get it to leave spaces for the blanks or copy to Sheet2 "Conversion".

Sub CopyData()
Application.ScreenUpdating = False
Dim c As Range
For Each c In Range("AV2:AV51")
If c.Offset(0, -1) <> "" Then
c.Copy
Cells(Rows.Count, "CC").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
End If
Next c
Application.ScreenUpdating = False
End Sub

Thanks for everything!!
0
Answer 10 / 13
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Jan 16, 2018 at 10:55 PM
Hello Scott,

Where/why does Column CC come into the equation?

Based on your last three posts, the following should work:- 

Sub Copydata()

    Dim lr As Long
    Dim c As Range
            
FRTest

Application.ScreenUpdating = False
           
lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
    
With Sheet1.Range("AL2", Sheet1.Range("AL" & Sheet1.Rows.Count).End(xlUp))
        Sheet2.Cells(4, 31).Resize(.Rows.Count, .Columns.Count).Value = .Value
End With
            
For Each c In Sheet2.Range("AE4", Sheet2.Range("AE" & Sheet2.Rows.Count).End(xlUp))
     If Not IsDate(c.Offset(, -1)) Then
            c.ClearContents
      End If
Next c

With Sheet1.Range("AO2", Sheet1.Range("AO" & Sheet1.Rows.Count).End(xlUp))
        Sheet2.Cells(4, 34).Resize(.Rows.Count, .Columns.Count).Value = .Value
End With

For Each c In Sheet2.Range("AH4", Sheet2.Range("AH" & Sheet2.Rows.Count).End(xlUp))
     If Not IsDate(c.Offset(, -1)) Then
            c.ClearContents
      End If
Next c

With Sheet1.Range("AS2", Sheet1.Range("AS" & Sheet1.Rows.Count).End(xlUp))
        Sheet2.Cells(4, 38).Resize(.Rows.Count, .Columns.Count).Value = .Value
End With

For Each c In Sheet2.Range("AL4", Sheet2.Range("AL" & Sheet2.Rows.Count).End(xlUp))
     If Not IsDate(c.Offset(, -1)) Then
            c.ClearContents
      End If
Next c

With Sheet1.Range("AV2", Sheet1.Range("AV" & Sheet1.Rows.Count).End(xlUp))
        Sheet2.Cells(4, 41).Resize(.Rows.Count, .Columns.Count) = .Value
End With

For Each c In Sheet2.Range("AO4", Sheet2.Range("AO" & Sheet2.Rows.Count).End(xlUp))
     If Not IsDate(c.Offset(, -1)) Then
            c.ClearContents
      End If
Next c
      
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


Following is the link to your sample workbook with the updated code implemented:-

http://ge.tt/17zCx8o2

I hope that this helps.

Cheerio,
vcoolio.
0
smcgrath3475 Posts 17 Registration date Wednesday December 13, 2017 Status Member Last seen January 23, 2018
Jan 17, 2018 at 06:36 AM
Thanks you are awesome! Everything works... the CC was just a blank column I was able to use to text as I couldn't get to sheet2... Thanks again I appreciate all your help!

Scott
0
smcgrath3475 Posts 17 Registration date Wednesday December 13, 2017 Status Member Last seen January 23, 2018
Jan 17, 2018 at 07:55 AM
You are amazing! As I play with it I noticed on sheet 2 I have text in G56:BH56 and below that are part of the form the data is being copied to and now they are gone after running the macro... so basically the data should only be pasting to rows 4:53 on sheet 2, I don't know if it's the copy/pasting command or the clear contents command? Any idea.. thanks
0
smcgrath3475 Posts 17 Registration date Wednesday December 13, 2017 Status Member Last seen January 23, 2018
Jan 17, 2018 at 02:29 PM
Hello; don't know what I did, but it's no longer clearing the text below my desired paste range... hopefully I won't have to ask you for more help;-). You have been great and appreciate all you have done while teaching my along the way. Thanks
0
Answer 11 / 13
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Jan 17, 2018 at 11:55 PM
You're welcome Scott. I'm glad that I was able to help. 

I don't know if it's the copy/pasting command or the clear contents command?

It could be the ClearcContents part so just keep an eye out for the quirk. However, if the paste range will always be rows 4:53, then you could change this line of code:- 

For Each c In Sheet2.Range("AE4", Sheet2.Range("AE" & Sheet2.Rows.Count).End(xlUp))


to 
For Each c In Sheet2.Range("AE4:AE53")

which will hard code the range to those rows only.
Change it, if necessary, in lines 16, 26, 36 & 46 in the code in post #21.

Cheerio,
vcoolio.
0
smcgrath3475 Posts 17 Registration date Wednesday December 13, 2017 Status Member Last seen January 23, 2018
Updated on Jan 22, 2018 at 03:48 PM
Hello; I have taken all your hard work and modified for another worksheet with the same functions, but for some reason the last two rows (50:51) from the WS1 "Exported Data"are not copying over to WS2 "Sheet 1"(52:53). It's the same code that works on the other workbook, but data copying to and from different columns and only one column requires a check against another (Date) to copy... I would appreciate if you had any thoughts? Your the best!!

Sub CopyData()

Dim lr As Long
Dim c As Range

FRTest

Application.ScreenUpdating = False

lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

With Sheet1.Range("AG2", Sheet1.Range("AG" & Sheet1.Rows.Count).End(xlUp))
Sheet2.Cells(4, 35).Resize(.Rows.Count, .Columns.Count).Value = .Value
End With

For Each c In Sheet2.Range("AI4", Sheet2.Range("AI" & Sheet2.Rows.Count).End(xlUp))
If Not IsDate(c.Offset(, -2)) Then
c.ClearContents
End If
Next c

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub
Sub FRTest()
Sheets("Sheet 1").Range("B4:AK53").ClearContents


Dim lastRow As Long
lastRow = Sheets("Exported Data").Range("D" & Rows.Count).End(xlUp).Row
Sheets("Sheet 1").Range("B4:B" & lastRow).Value = Sheets("Exported Data").Range("D2:D" & lastRow).Value
lastRow = Sheets("Exported Data").Range("E" & Rows.Count).End(xlUp).Row
Sheets("Sheet 1").Range("C4:C" & lastRow).Value = Sheets("Exported Data").Range("E2:E" & lastRow).Value
lastRow = Sheets("Exported Data").Range("I" & Rows.Count).End(xlUp).Row
Sheets("Sheet 1").Range("D4:D" & lastRow).Value = Sheets("Exported Data").Range("I2:I" & lastRow).Value
lastRow = Sheets("Exported Data").Range("J" & Rows.Count).End(xlUp).Row
Sheets("Sheet 1").Range("E4:E" & lastRow).Value = Sheets("Exported Data").Range("J2:J" & lastRow).Value
lastRow = Sheets("Exported Data").Range("K" & Rows.Count).End(xlUp).Row
Sheets("Sheet 1").Range("O4:O" & lastRow).Value = Sheets("Exported Data").Range("K2:K" & lastRow).Value
lastRow = Sheets("Exported Data").Range("L" & Rows.Count).End(xlUp).Row
Sheets("Sheet 1").Range("F4:F" & lastRow).Value = Sheets("Exported Data").Range("L2:L" & lastRow).Value
lastRow = Sheets("Exported Data").Range("M" & Rows.Count).End(xlUp).Row
Sheets("Sheet 1").Range("G4:G" & lastRow).Value = Sheets("Exported Data").Range("M2:M" & lastRow).Value
lastRow = Sheets("Exported Data").Range("N" & Rows.Count).End(xlUp).Row
Sheets("Sheet 1").Range("P4:P" & lastRow).Value = Sheets("Exported Data").Range("N2:N" & lastRow).Value
lastRow = Sheets("Exported Data").Range("O" & Rows.Count).End(xlUp).Row
Sheets("Sheet 1").Range("Q4:Q" & lastRow).Value = Sheets("Exported Data").Range("O2:O" & lastRow).Value
lastRow = Sheets("Exported Data").Range("P" & Rows.Count).End(xlUp).Row
Sheets("Sheet 1").Range("R4:R" & lastRow).Value = Sheets("Exported Data").Range("P2:P" & lastRow).Value
lastRow = Sheets("Exported Data").Range("Q" & Rows.Count).End(xlUp).Row
Sheets("Sheet 1").Range("S4:S" & lastRow).Value = Sheets("Exported Data").Range("Q2:Q" & lastRow).Value
lastRow = Sheets("Exported Data").Range("R" & Rows.Count).End(xlUp).Row
Sheets("Sheet 1").Range("T4:T" & lastRow).Value = Sheets("Exported Data").Range("R2:R" & lastRow).Value
lastRow = Sheets("Exported Data").Range("S" & Rows.Count).End(xlUp).Row
Sheets("Sheet 1").Range("U4:U" & lastRow).Value = Sheets("Exported Data").Range("S2:S" & lastRow).Value
lastRow = Sheets("Exported Data").Range("T" & Rows.Count).End(xlUp).Row
Sheets("Sheet 1").Range("V4:V" & lastRow).Value = Sheets("Exported Data").Range("T2:T" & lastRow).Value
lastRow = Sheets("Exported Data").Range("U" & Rows.Count).End(xlUp).Row
Sheets("Sheet 1").Range("W4:W" & lastRow).Value = Sheets("Exported Data").Range("U2:U" & lastRow).Value
lastRow = Sheets("Exported Data").Range("V" & Rows.Count).End(xlUp).Row
Sheets("Sheet 1").Range("X4:X" & lastRow).Value = Sheets("Exported Data").Range("V2:V" & lastRow).Value
lastRow = Sheets("Exported Data").Range("W" & Rows.Count).End(xlUp).Row
Sheets("Sheet 1").Range("Y4:Y" & lastRow).Value = Sheets("Exported Data").Range("W2:W" & lastRow).Value
lastRow = Sheets("Exported Data").Range("X" & Rows.Count).End(xlUp).Row
Sheets("Sheet 1").Range("Z4:Z" & lastRow).Value = Sheets("Exported Data").Range("X2:X" & lastRow).Value
lastRow = Sheets("Exported Data").Range("Y" & Rows.Count).End(xlUp).Row
Sheets("Sheet 1").Range("AA4:AA" & lastRow).Value = Sheets("Exported Data").Range("Y2:Y" & lastRow).Value
lastRow = Sheets("Exported Data").Range("Z" & Rows.Count).End(xlUp).Row
Sheets("Sheet 1").Range("AB4:AB" & lastRow).Value = Sheets("Exported Data").Range("Z2:Z" & lastRow).Value
lastRow = Sheets("Exported Data").Range("AA" & Rows.Count).End(xlUp).Row
Sheets("Sheet 1").Range("AC4:AC" & lastRow).Value = Sheets("Exported Data").Range("AA2:AA" & lastRow).Value
lastRow = Sheets("Exported Data").Range("AB" & Rows.Count).End(xlUp).Row
Sheets("Sheet 1").Range("AD4:AD" & lastRow).Value = Sheets("Exported Data").Range("AB2:AB" & lastRow).Value
lastRow = Sheets("Exported Data").Range("AC" & Rows.Count).End(xlUp).Row
Sheets("Sheet 1").Range("AE4:AE" & lastRow).Value = Sheets("Exported Data").Range("AC2:AC" & lastRow).Value
lastRow = Sheets("Exported Data").Range("AD" & Rows.Count).End(xlUp).Row
Sheets("Sheet 1").Range("AF4:AF" & lastRow).Value = Sheets("Exported Data").Range("AD2:AD" & lastRow).Value
lastRow = Sheets("Exported Data").Range("AE" & Rows.Count).End(xlUp).Row
Sheets("Sheet 1").Range("AG4:AG" & lastRow).Value = Sheets("Exported Data").Range("AE2:AE" & lastRow).Value
lastRow = Sheets("Exported Data").Range("AF" & Rows.Count).End(xlUp).Row
Sheets("Sheet 1").Range("AH4:AH" & lastRow).Value = Sheets("Exported Data").Range("AF2:AF" & lastRow).Value
lastRow = Sheets("Exported Data").Range("AH" & Rows.Count).End(xlUp).Row
Sheets("Sheet 1").Range("AJ4:AJ" & lastRow).Value = Sheets("Exported Data").Range("AH2:AH" & lastRow).Value
lastRow = Sheets("Exported Data").Range("AI" & Rows.Count).End(xlUp).Row
Sheets("Sheet 1").Range("AK4:AK" & lastRow).Value = Sheets("Exported Data").Range("AI2:AI" & lastRow).Value


ActiveSheet.Range("B4:AK53").Replace What:="YES", Replacement:=1, LookAt:=xlPart, MatchCase:=True
ActiveSheet.Range("B4:AK53").Replace What:="NO", Replacement:=0, LookAt:=xlPart, MatchCase:=True
ActiveSheet.Range("H4:AK53").Replace What:="NA", Replacement:=9, LookAt:=xlPart, MatchCase:=True


End Sub

Thanks Again; Scott
0
Answer 12 / 13
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Jan 22, 2018 at 08:14 PM
Hello Scott,

Sitting here slurping on my espresso reading your post, I can't see anything wrong with the code. It may be a set out problem with the data set so if you could upload a sample of the new workbook, with the code implemented, I'll check it out for you.

Cheerio,
vcoolio.
0
smcgrath3475 Posts 17 Registration date Wednesday December 13, 2017 Status Member Last seen January 23, 2018
Jan 23, 2018 at 07:12 AM
Good morning from Boston; Thanks for taking time to review the code;-) I also noticed columns T:Y if blank on sh1 copies over #N/A and changes the header in row 2 and 3 as well in those columns? The other item is that the last two rows of data don't seem to copy over. Here is the link to the uploaded test data https://www.dropbox.com/s/tzkjy06ddnj88qh/Book1.xlsm?dl=0 Thanks again!
0
smcgrath3475 Posts 17 Registration date Wednesday December 13, 2017 Status Member Last seen January 23, 2018
Jan 23, 2018 at 09:49 AM
I rewrote the copy portion of the code and now works? Thanks for be willing to help again!!

Sub FRTest()
Sheets("Sheet 1").Range("B4:AK53").ClearContents

Sheets("Sheet 1").Range("B4:B53").Value = Sheets("Exported Data").Range("D2:D51").Value
Sheets("Sheet 1").Range("C4:C53").Value = Sheets("Exported Data").Range("E2:E51").Value
Sheets("Sheet 1").Range("D4:D53").Value = Sheets("Exported Data").Range("I2:I51").Value
Sheets("Sheet 1").Range("E4:E53").Value = Sheets("Exported Data").Range("J2:J51").Value
Sheets("Sheet 1").Range("F4:F53").Value = Sheets("Exported Data").Range("L2:L51").Value
Sheets("Sheet 1").Range("G4:G53").Value = Sheets("Exported Data").Range("M2:M51").Value
Sheets("Sheet 1").Range("O4:O53").Value = Sheets("Exported Data").Range("K2:K51").Value
Sheets("Sheet 1").Range("P4:P53").Value = Sheets("Exported Data").Range("N2:N51").Value
Sheets("Sheet 1").Range("Q4:Q53").Value = Sheets("Exported Data").Range("O2:O51").Value
Sheets("Sheet 1").Range("S4:S53").Value = Sheets("Exported Data").Range("Q2:Q51").Value
Sheets("Sheet 1").Range("T4:T53").Value = Sheets("Exported Data").Range("R2:R51").Value
Sheets("Sheet 1").Range("U4:U53").Value = Sheets("Exported Data").Range("S2:S51").Value
Sheets("Sheet 1").Range("V4:V53").Value = Sheets("Exported Data").Range("T2:T51").Value
Sheets("Sheet 1").Range("W4:W53").Value = Sheets("Exported Data").Range("U2:U51").Value
Sheets("Sheet 1").Range("X4:X53").Value = Sheets("Exported Data").Range("V2:V51").Value
Sheets("Sheet 1").Range("Y4:Y53").Value = Sheets("Exported Data").Range("W2:W51").Value
Sheets("Sheet 1").Range("Z4:Z53").Value = Sheets("Exported Data").Range("X2:X51").Value
Sheets("Sheet 1").Range("AA4:AA53").Value = Sheets("Exported Data").Range("Y2:Y51").Value
Sheets("Sheet 1").Range("AB4:AB53").Value = Sheets("Exported Data").Range("Z2:Z51").Value
Sheets("Sheet 1").Range("AC4:AC53").Value = Sheets("Exported Data").Range("AA2:AA51").Value
Sheets("Sheet 1").Range("AD4:AD53").Value = Sheets("Exported Data").Range("AB2:AB51").Value
Sheets("Sheet 1").Range("AE4:AE53").Value = Sheets("Exported Data").Range("AC2:AC51").Value
Sheets("Sheet 1").Range("AF4:AF53").Value = Sheets("Exported Data").Range("AD2:AD51").Value
Sheets("Sheet 1").Range("AG4:AG53").Value = Sheets("Exported Data").Range("AE2:AE51").Value
Sheets("Sheet 1").Range("AH4:AH53").Value = Sheets("Exported Data").Range("AF2:AF51").Value
Sheets("Sheet 1").Range("AJ4:AJ53").Value = Sheets("Exported Data").Range("AH2:AH51").Value
Sheets("Sheet 1").Range("AK4:AK53").Value = Sheets("Exported Data").Range("AI2:AI51").Value

ActiveSheet.Range("B4:AK53").Replace What:="YES", Replacement:=1, LookAt:=xlPart, MatchCase:=True
ActiveSheet.Range("B4:AK53").Replace What:="NO", Replacement:=0, LookAt:=xlPart, MatchCase:=True

End Sub
0
Answer 13 / 13
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Jan 23, 2018 at 08:26 PM
Hello Scott,

Very good. As long as the ranges are fixed, all should work fine with your last code amendment.

One thing that I did overlook when reading through your code in post #26:
When using that particular method, its a good idea to declare a last row variable for both sheets. Hence, something like:- 


 Dim lastRow As Long
    Dim lr As Long
    lastRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
    lr = Sheet2.Range("A" & Rows.Count).End(xlUp).Row


Thus the FRTest code could be trimmed as follows:- 

Sub FRTest()
Sheets("Sheet 1").Range("B4:AK53").ClearContents

  
    Dim lastRow As Long
    Dim lr As Long
    lastRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
    lr = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
    
    Sheets("Sheet 1").Range("B4:B" & lr).Value = Sheets("Exported Data").Range("D2:D" & lastRow).Value
    Sheets("Sheet 1").Range("C4:C" & lr).Value = Sheets("Exported Data").Range("E2:E" & lastRow).Value
    Sheets("Sheet 1").Range("D4:D" & lr).Value = Sheets("Exported Data").Range("I2:I" & lastRow).Value
    Sheets("Sheet 1").Range("E4:E" & lr).Value = Sheets("Exported Data").Range("J2:J" & lastRow).Value
    Sheets("Sheet 1").Range("O4:O" & lr).Value = Sheets("Exported Data").Range("K2:K" & lastRow).Value
    Sheets("Sheet 1").Range("F4:F" & lr).Value = Sheets("Exported Data").Range("L2:L" & lastRow).Value
    Sheets("Sheet 1").Range("G4:G" & lr).Value = Sheets("Exported Data").Range("M2:M" & lastRow).Value
    Sheets("Sheet 1").Range("P4:P" & lr).Value = Sheets("Exported Data").Range("N2:N" & lastRow).Value
    Sheets("Sheet 1").Range("Q4:Q" & lr).Value = Sheets("Exported Data").Range("O2:O" & lastRow).Value
    Sheets("Sheet 1").Range("R4:R" & lr).Value = Sheets("Exported Data").Range("P2:P" & lastRow).Value
    Sheets("Sheet 1").Range("S4:S" & lr).Value = Sheets("Exported Data").Range("Q2:Q" & lastRow).Value
    Sheets("Sheet 1").Range("T4:T" & lr).Value = Sheets("Exported Data").Range("R2:R" & lastRow).Value
    Sheets("Sheet 1").Range("U4:U" & lr).Value = Sheets("Exported Data").Range("S2:S" & lastRow).Value
    Sheets("Sheet 1").Range("V4:V" & lr).Value = Sheets("Exported Data").Range("T2:T" & lastRow).Value
    Sheets("Sheet 1").Range("W4:W" & lr).Value = Sheets("Exported Data").Range("U2:U" & lastRow).Value
    Sheets("Sheet 1").Range("X4:X" & lr).Value = Sheets("Exported Data").Range("V2:V" & lastRow).Value
    Sheets("Sheet 1").Range("Y4:Y" & lr).Value = Sheets("Exported Data").Range("W2:W" & lastRow).Value
    Sheets("Sheet 1").Range("Z4:Z" & lr).Value = Sheets("Exported Data").Range("X2:X" & lastRow).Value
    Sheets("Sheet 1").Range("AA4:AA" & lr).Value = Sheets("Exported Data").Range("Y2:Y" & lastRow).Value
    Sheets("Sheet 1").Range("AB4:AB" & lr).Value = Sheets("Exported Data").Range("Z2:Z" & lastRow).Value
    Sheets("Sheet 1").Range("AC4:AC" & lr).Value = Sheets("Exported Data").Range("AA2:AA" & lastRow).Value
    Sheets("Sheet 1").Range("AD4:AD" & lr).Value = Sheets("Exported Data").Range("AB2:AB" & lastRow).Value
    Sheets("Sheet 1").Range("AE4:AE" & lr).Value = Sheets("Exported Data").Range("AC2:AC" & lastRow).Value
    Sheets("Sheet 1").Range("AF4:AF" & lr).Value = Sheets("Exported Data").Range("AD2:AD" & lastRow).Value
    Sheets("Sheet 1").Range("AG4:AG" & lr).Value = Sheets("Exported Data").Range("AE2:AE" & lastRow).Value
    Sheets("Sheet 1").Range("AH4:AH" & lr).Value = Sheets("Exported Data").Range("AF2:AF" & lastRow).Value
    Sheets("Sheet 1").Range("AJ4:AJ" & lr).Value = Sheets("Exported Data").Range("AH2:AH" & lastRow).Value
    Sheets("Sheet 1").Range("AK4:AK" & lr).Value = Sheets("Exported Data").Range("AI2:AI" & lastRow).Value
    
    
    Sheet2.Range("B4:AK53").Replace What:="YES", Replacement:=1, LookAt:=xlPart, MatchCase:=True
    Sheet2.Range("B4:AK53").Replace What:="NO", Replacement:=0, LookAt:=xlPart, MatchCase:=True
    Sheet2.Range("H4:AK53").Replace What:="NA", Replacement:=9, LookAt:=xlPart, MatchCase:=True
     

End Sub


This should work just as well as the fixed range method.

You could trim the code further by using the sheet codes (Sheet1, Sheet2) rather than the sheet names.

BTW, in the the "Copydata" code, these two lines aren't necessary:- 

Dim lr As Long
lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row


I inadvertently left them there (post #21) after deciding to go down another road.

I hope that this all helps.

Cheerio,
vcoolio.
0

Similar discussions

Unlock safe folder without losing any data
Mintu -
HelpiOS -

1 response
How To Fix Mint Mobile Data Not Working?
sachin327 -
joshitha -

2 responses