Need Help with VBA code - Excel

[ChaosAngelic]

Hello,

First of all, I'm new to this VBA world, its the first time I'm codding, and new to this forum, I hope I can get some help with this.

Problem:

I'm trying to copy information from one workbook "Copy" to another "Paste".
My condition are:
In the "Copy" workbook, I check if cells in B column are empty or not
In the "Paste" workbook i paste that information every 23 rows in column A starting at row 2702

Thats the code I managed to make, and its not working yet:


Sub testeCOPY()


Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim test As Range
Dim i As Range
Set wsCopy = Workbooks("02_Compta RCConcept.xlsx").Worksheets("Contas correntes CLIENTES")
Set wsDest = Workbooks("Obras RC Concept.xlsm").Worksheets("MAPA OBRAS")


test = Range("B8:B999").Value
i = Range("A2702")


If Not IsEmpty(wsCopy.Range("test")) Then
wsDest.Range("i") = wsCopy.Range("test")
i = i + 23
End If


End Sub

The error i get is:

27563

There might be more errors

Thanks you for your time and help :D

[anish.ms]

I'm not sure what you are trying to loop here
In the "Paste" workbook i paste that information every 23 rows in column A starting at row 2702



Sub testCOPY()


Dim wsCopy As Worksheet
Dim wsDest As Worksheet


Set wsCopy = Workbooks("Book4.xlsx").Worksheets("Sheet1")
Set wsDest = Workbooks("Book5.xlsb").Worksheets("Sheet1")


wsCopy.Range("B2:B20").Copy
wsDest.Range("A2").PasteSpecial (xlPasteAll)


End Sub


Please make it very clear and attach sample files so that the expert members here will be able to help you

[ChaosAngelic]

There is a small error on what I said, it should start at row 2006.
The idea is to copy information from "copy" excel workbook, to the "paste" workbook, and in that (paste) workbook i want that information to be in row 2006, 2029, 2052, 2075 etc
I attached 2 images to help understand.

Copy workbook
27564

Paste workbook
27565

[ChaosAngelic]

There is a small error on what I said, it should start at row 2006.
The idea is to copy information from "copy" excel workbook, to the "paste" workbook, and in that (paste) workbook i want that information to be in row 2006, 2029, 2052, 2075 etc
I attached 2 images to help understand.

Copy workbook
27564

Paste workbook
27565

So, what I mean is: In the "copy workbook" check 1 by 1 each line in the B column to see if there is any number on it
If yes, then copy that number, and paste it to the "destination workbook" in A column, row 2006.
Do it again, but next time it founds a value in the "copy workbook", it pastes it to the to the "destination workbook", but this time in row 2029.

[anish.ms]

Hi
I'm also a beginner to VBA. Hope the below code works


Option Explicit

Sub testCOPY()
Dim wsCopy As Worksheet, wsDest As Worksheet
Dim CopyRange As Range, DestRange As Range, myCell As Range
Dim i As Integer


Set wsCopy = Workbooks("02_Compta RCConcept.xlsx").Worksheets("Contas correntes CLIENTES")
Set wsDest = Workbooks("Obras RC Concept.xlsm").Worksheets("MAPA OBRAS")


Set CopyRange = wsCopy.Range("B1:B" & wsCopy.Cells(Rows.Count, 2).End(xlUp).Row)
i = 2006


For Each myCell In CopyRange
If Not IsEmpty(myCell) Then
myCell.Copy
Set DestRange = wsDest.Cells(i, 1)
DestRange.PasteSpecial (xlPasteAll)
i = i + 23
End If
Next myCell


End Sub

[jolivanes]

Looping is not the best way to go.
If you have a large range in Column B it might be better to use AutoFilter

Sub Maybe()
Dim wsCopy As Worksheet, wsPaste As Worksheet, c As Range, x As Long
Set wsCopy = Worksheets("Sheet1") '<----- Change Sheet name as required
Set wsPaste = Worksheets("Sheet2") '<----- Change Sheet name as required
x = 1983
For Each c In wsCopy.Range("B1:B" & wsCopy.Cells(Rows.Count, 2).End(xlUp).Row)
If Len(c) <> 0 Then
x = x + 23
c.Copy wsPaste.Cells(x, 1)
End If
Next c
End Sub

Next time, use a more meaningful header. Need help etc is pretty sure to be the problem for 100 percent of the posters.

[ChaosAngelic]

Fisrt of all thanks you all for the help provided and quick answers.

Its 2 diferents workbooks, not one workbook with 2 sheets.

I still didnt managed to get what I'm looking for, looks like the problem migh be related with the path to the workbooks. I still dont know if I need to create a path C:username/desktop etc, or just write the workbook name. I get this error:

Run-Time error '9':
Subscript out of range

This is the code I'm trying now:

Sub Maybe()
Dim wsCopy As Worksheet, wsPaste As Worksheet, c As Range, x As Long
Set wsCopy = Workbooks("C:\Users\PV\OneDrive\Ambiente de Trabalho\Nova pasta\Copy.xlsx").Worksheets("Contas correntes CLIENTES") '<----- Change Sheet name as required
Set wsDest = Workbooks("C:\Users\PV\OneDrive\Ambiente de Trabalho\Nova pasta\Paste.xlsm").Worksheets("MAPA OBRAS") '<----- Change Sheet name as required
x = 2006
For Each c In wsCopy.Range("B16:B" & wsCopy.Cells(Rows.Count, 2).End(xlUp).Row)
If Len(c) <> 0 Then
x = x + 23
c.Copy wsPaste.Cells(x, 1)
End If
Next c
End Sub

From Jolivanes

I'm not sure what this does: wsCopy.Cells(Rows.Count, 2).End(xlUp).Row

The problem I have to solve, is a bit more complex that what I'm asking now, because I think if I can manage to copy the numbers from the B column of rthe copy workbook to the A column of the paste workbook at row X I migh be able to solve the entire problem alone, but I will post in 2 images all the data I need to copy from one workbook to the other:

Copy workbook:

27568

Paste workbook:

27569

What I have been asking is how to copy the numbers in dark green from one workbook to the other.

I could post the files here, but I dont know how... can't see a option to load excel files.

If you could help with the first question or the entire problem, it would be great. Thanks :)

[jolivanes]

Re: I could post the files here, but I dont know how
Click on FAQ at the top. (Between "Private Messages" and "Calendar")