View Full Version : Need Help with VBA code - Excel
ChaosAngelic
12-09-2020, 03:25 AM
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
12-09-2020, 04:55 AM
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
12-09-2020, 05:21 AM
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
12-09-2020, 07:42 AM
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
12-09-2020, 01:29 PM
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
12-09-2020, 02:59 PM
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
12-10-2020, 03:15 AM
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
12-10-2020, 10:46 AM
Re: I could post the files here, but I dont know how
Click on FAQ at the top. (Between "Private Messages" and "Calendar")
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.