VBA copy and paste to new sheet based on number of rows

wolthers

New Member
Joined
Sep 2, 2015
Messages
18
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hi, I am making a workbook to log all data based on coffee roast profiles sold.

Sales sheet has the following which I want to copy to log sheet.
B2 = sales ID
B4 = name (either company or person)

Then I can have data from D11 all the way to F20, and that's where I am finding difficulties, I can copy and paste D11:F11 but if I have more than one row then I will only have data on the first row on the log sheet.
D11E11F11
LightWhole1,250 g
MediumWhole2,500 g
DarkWhole2,500 g
MediumGround750 g


log sheet is as follows:
(A)Day(B)Order Nr(C)Client(D)Seller(E)Roast(F)Prep(G)Quantity
16-06-20SBC 00001BakerDanLightWhole1250
MediumWhole2500
DarkWhole2500
MediumGround750
16-06-20SBC 00002LawyerTomLightWhole1250
MediumWhole2500
DarkWhole2500
MediumGround750
16-06-20SBC 00003DentistJoeLightWhole1250
MediumWhole2500
DarkWhole2500
MediumGround750


should look as follows:
(A)Day(B)Order Nr(C)Client(D)Seller(E)Roast(F)Prep(G)Quantity
16-06-20SBC 00001BakerDanLightWhole1250
16-06-20SBC 00001BakerDanMediumWhole2500
16-06-20SBC 00001BakerDanDarkWhole2500
16-06-20SBC 00001BakerDanMediumGround750
16-06-20SBC 00001LawyerTomLightWhole1250
16-06-20SBC 00001LawyerTomMediumWhole2500
16-06-20SBC 00001LawyerTomDarkWhole2500
16-06-20SBC 00001LawyerTomMediumGround750
16-06-20SBC 00001DentistJoeLightWhole1250
16-06-20SBC 00001DentistJoeMediumWhole2500
16-06-20SBC 00001DentistJoeDarkWhole2500
16-06-20SBC 00001DentistJoeMediumGround750



my VBA macro:

VBA Code:
Sub insert()

Dim ms As Worksheet, NR
Set ms = Sheets("log")
   With Sheets("sales")

       NR = ms.Cells.Find("*", , , , xlByRows, xlPrevious).Row + 1
       

       ms.Range("A" & NR) = Date
       
           .Range("B2").Resize(1).Copy  'sale nr
       ms.Range("B" & NR).PasteSpecial xlValues
       
           .Range("B4").Resize(1).Copy  'client
       ms.Range("C" & NR).PasteSpecial xlValues
       
       myValue = InputBox("Quem vendeu") 'broker
       ms.Range("D" & NR).Value = myValue
       
'A B C and D should repeat the paste by the amount of rows between B11 and B20 with data

       .Range("d11:f11").Resize(10).Copy   'order
       ms.Range("E" & NR).PasteSpecial xlValues, Transpose:=False
    
    
   End With
  
End Sub
 
Just activate the sheet in your code like this:
VBA Code:
Sub MyFillMacro()

    Dim lr As Long
    Dim rng As Range
  
'   Specify the worksheet you want this to happen on
    Sheets("Sheet6").Activate
  
'   Find last row with data in column G
    lr = Cells(Rows.Count, "G").End(xlUp).Row

'   Set range
    Set rng = Range("A1:G" & lr)

'   Fill in blanks with formulas
    rng.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "=R[-1]C"
  
'   Turn formulas into values
    rng.Value = rng.Value

End Sub
If that doesn't work, then it must think those blank rows at the bottom of column G really have something in them.
Are there formulas or spaces in them?
 
Upvote 0

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
If that doesn't work, then it must think those blank rows at the bottom of column G really have something in them.
Are there formulas or spaces in them?

There are no formulas on the column G, is there a way for me to upload the spreadsheet?
 
Upvote 0
is there a way for me to upload the spreadsheet?
You can upload files to file sharing sites, like DropBox or a Google Drive account, if you have one, and then provide a link here to the files.
Note that I can only download files from my home computer, so I wouldn't be able to download it until later tonight when I am on that computer.

Try running this code, and tell me what it returns:
VBA Code:
Sub Test()

    Dim lr As Long
    Dim ln As Long

    Sheets("Sheet6").Activate

    lr = Cells(Rows.Count, "G").End(xlUp).Row
    ln = Len(Range("G" & lr))
    
    MsgBox "There is an entry of length " & ln & " in cell G" & lr
    
End Sub
 
Upvote 0
This is what happens, see from line G7 to G11 it should be empty, then I ran macro again including your line, and it filled up the blank between G7:G11, and added the rest there too, but there should be nothing from row 17 and down. Now if I run macro again, the same will repeat, filling up 1500 from G18-G21, and adding the new data after A22.
Annotation 2020-06-17 140619.png
 
Upvote 0
If you use the latest version of my code, it should be stopping at that row, row 16.
If it is not, check again to make sure that you have copied and pasted over my latest version. Note that there were updates in a few places.
 
Upvote 0
Thanks Joe, I really appreciate your help! Here's a link for the workbook in case you want to look, note I am keeping the codes together in one macro only.
This looks an "after" view of the the data after you ran the code that wasn't working correctly.
Do you have a "before" copy of the data, so I can try running the code exactly on that?
 
Upvote 0
Hi Joe, edited the file, I guess it should be updated there on the link now without your code and without data now.

Edit: the data being sent to the sheet called log is on the sheet called venda
 
Upvote 0

Forum statistics

Threads
1,223,898
Messages
6,175,274
Members
452,628
Latest member
dd2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top