Macro limits the copying

**carapins** · 10-17-2013, 10:56 AM

Hi again,

I need help here too, I have this table: examp.xlsx and I'm using a macro that copy and paste data to another spreadsheet with this code:

Sub Save()

Dim i&
With Sheets("Plan1").Range("B6").CurrentRegion
i = .Rows.Count - 1
With .Offset(1).Resize(i)
Union(.Columns(1).Resize(, 10), .Columns(12), .Columns(14).Resize(, 3)).Copy
Sheets("Plan2").Cells(Rows.Count, 3).End(xlUp)(2).PasteSpecial xlPasteAllExceptBorders
End With
End With
With Sheets("Plan2").Cells(Rows.Count, 2).End(xlUp)(2).Resize(i)
.FormulaR1C1 = "=ROW(RC[-2])-6"
.Value = .Value
End With
Application.CutCopyMode = False

End Sub

However I want to limit the functionality of this macro because I dont want this macro copy rows that have no data in columns C6, D6, E6, F6, G6, H6, J6, K6, M6, O6, in between rows 7 to 20. But can copy if there is no data in B6, I6, L6, N6, P6, Q6 in between rows 7 to 20.
So if there is no data in these columns, a warning appears saying "Unable to copy. Complete the columns". Does anyone know how to do this?

Are trying to help me on this forum as well: http://www.vbaexpress.com/forum/show...ng-of-the-data

Cheers!

**AB33** · 10-17-2013, 11:00 AM

I do not see any line of the code which deals with saving, but this line errors.

Please Login or Register  to view this content.

As you have a single row, you need to remove the minus from 1

i = .Rows.Count[/CODE]

**carapins** · 10-17-2013, 11:14 AM

The code is right. If you create a macro with that code and use for example this data on table: try.xlsx the data is save correctly as I want

**AB33** · 10-17-2013, 03:06 PM

Yes, the code works on the latest attached as you have increased the no of rows, but still there is no save line, so I do not know what the issue is.

**carapins** · 10-17-2013, 04:00 PM

The term "save" may not be correct ... what I want is to copy and paste data but if there is no data in the columns that I mentioned then is not able to copy and paste

**AB33** · 10-17-2013, 04:14 PM

Carapins,
First of all, you need to wrap your code with tags as per forum's rule.
Second, if you have a code and want to amend it, you need to attach it with the sample, so the person who looks on the attached could easily find it.
I have not got error when I run it. You need to show me what error you are getting or, which part of the code do you want to change.

**carapins** · 10-17-2013, 04:26 PM

I know that there is no error in the code. But I want the code doesnt work if the columns that I mentioned didn't have data. I want to limit the functionality of this macro because I dont want this macro copy rows that have no data in columns I mentioned. What you dont understand here?

**carapins** · 10-17-2013, 07:04 PM

I am trying to do in this way to not copy the column C (C7:C20) if there is no data:

Please Login or Register  to view this content.

But doesn't work...

**carapins** · 10-17-2013, 07:51 PM

Something better...

Please Login or Register  to view this content.

But it was necessary to write a code to determine first if there is data in rows 7,8,9,10,11,12,13...20 and if there is data then just check in these rows if there are cells without data. Anyone knows?

Cheers!

**carapins** · 10-23-2013, 01:03 PM

Up...

Some help please, I'll need this done...

Macro limits the copying

LinkBack

Thread Tools

Rate This Thread

Display

Macro limits the copying

Re: Macro limits saving of the data

Re: Macro limits saving of the data

Re: Macro limits saving of the data

Re: Macro limits saving of the data

Re: Macro limits saving of the data

Re: Macro limits saving of the data

Re: Macro limits the copying

Re: Macro limits saving of the data

Re: Macro limits the copying

Thread Information

Users Browsing this Thread

Similar Threads

Macro to find value between two limits for a given condition

[SOLVED] Beginner Here: Saving data using macro in new rows.

Macro for saving cell data to another sheet

saving, deleting data, macro

[SOLVED] Saving Data In a Macro

Bookmarks

Bookmarks

Posting Permissions