VBA to Copy Value of Cell in a Do Until Loop

[JarrydBarnard]

Hi there,

I've got a range of data, that I'm trying to copy from Sheet 1, paste into specific cells in a new duplicated sheet, but then loop until the last row. I can't seem to get it to copy from the first sheet to the new sheet. Please help. Unsure where I'm going wrong. For example, I want it to copy A2 on Sheet 1, and paste into A9 on the new sheet, so on for all the values, then it loops to create another new sheet, but copies A3 into A9 on the new new sheet. Here is my VBA code:

Sub Do_Loop

'Setting Data Type

Dim Counter As String
Dim Last_Row As Long
Dim val As Integer
Dim CompanyName As String
Dim CompanyAddress As String
Dim City As String
Dim Province As String
Dim PostalCode As String
Dim Country As String
Dim VATNumber As Double
Dim InvoiceDate As Variant
Dim InvoiceNumber As Variant
Dim ProductCode As String
Dim Description As String
Dim Rate As Single
Dim Quantity As Integer
Dim DeliveryDistance As Single
Dim ProductManager As String
Dim ContactNumber As String
Dim sh1 As Worksheet
Dim sh2 As Worksheet

Set sh1 = ActiveWorkbook.Sheets("Recorded_Sales")
Set sh2 = ActiveWorkbook.Sheets("Invoice_Template")

'Setting Variable values

Counter = 2
Last_Row = sh1.Cells(Rows.Count, 1).End(xlUp).Row

'Creating Do Until Loop

Do Until Counter = Last_Row + 1
    Worksheets("Invoice_Template").Copy After:=Worksheets("Invoice_Template")
    CompanyName = sh1.Cells("Counter, 1").Text
    CompanyAddress = sh1.Cells("Counter, 2").Text
    City = sh1.Cells("Counter, 3").Text
    Province = sh1.Cells("Counter, 4").Text
    PostalCode = sh1.Cells("Counter, 5").Text
    Country = sh1.Cells("Counter, 6").Text
    VATNumber = sh1.Cells("Counter, 7").Text
    InvoiceDate = sh1.Cells("Counter, 8").Text
    InvoiceNumber = sh1.Cells("Counter, 9").Text
    ProductCode = sh1.Cells("Counter, 10").Text
    Description = sh1.Cells("Counter, 11").Text
    Rate = sh1.Cells("Counter, 12").Value
    Quantity = sh1.Cells("Counter, 13").Value
    DeliveryDistance = sh1.Cells("Counter, 14").Value
    ProductManager = sh1.Cells("Counter, 15").Text
    ContactNumber = sh1.Cells("Counter, 16").Text
   
    ActiveSheet.Range("A9").Value = CompanyName
    ActiveSheet.Range("A10").Value = CompanyAddress
    ActiveSheet.Range("A11").Value = City
    ActiveSheet.Range("A12").Value = Province
    ActiveSheet.Range("A13").Value = PostalCode
    ActiveSheet.Range("A14").Value = Country
    ActiveSheet.Range("A15").Value = VATNumber
    ActiveSheet.Range("C9").Value = InvoiceDate
    ActiveSheet.Range("E9").Value = InvoiceNumber
    ActiveSheet.Range("A19").Value = ProductCode
    ActiveSheet.Range("B19").Value = Description
    ActiveSheet.Range("I19").Value = Rate
    ActiveSheet.Range("J19").Value = Quantity
    ActiveSheet.Range("C26").Value = ProductManager
    ActiveSheet.Range("C27").Value = ContactNumber
    ActiveSheet.Range("J20").Value = DeliveryDistance
    ActiveSheet.Range("A20").Value = "Delivery"
    ActiveSheet.Range("B20").Value = "Kilometers"
    ActiveSheet.Name = ActiveSheet.Range("E9").Value
    Counter = Counter + 1
Loop

End Sub

Please help

 

[davesexcel]

Quotes do not belong in these lines.

sh1.Cells(Counter, 1).Text

 

[JarrydBarnard]

@davesexcel ahhh so just (Counter, 5) for example?